DQRSpK4u

1sql script, if and go statements
2IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'MY_TABLE') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
3  ALTER TABLE MY_TABLE ADD TMP_FIELD datetime
4GO
5IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'MY_TABLE') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
6  UPDATE MY_TABLE SET TMP_FIELD = modifiedDate
7GO
8IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'MY_TABLE') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
9  ALTER TABLE MY_TABLE DROP COLUMN modifiedDate
10GO
11IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'MY_TABLE') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
12  ALTER TABLE MY_TABLE ADD modifiedDate datetime
13GO
14IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'MY_TABLE') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
15  UPDATE MY_TABLE SET modifiedDate = TMP_FIELD
16GO
17IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'MY_TABLE') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
18  ALTER TABLE MY_TABLE DROP COLUMN TMP_FIELD
19GO
20	
21create table MY_TABLE (
22    modifiedDate varchar(20)
23)
24go
25
26insert MY_TABLE (modifiedDate) values ('2012-10-20 17:50:41')
27go
28
29select * from MY_TABLE
30go
31
32alter table MY_TABLE alter column modifiedDate datetime
33go
34
35select * from MY_TABLE
36go
37
38drop table MY_TABLE
39go
40	
41if exists (select table_name from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'MY_TABLE')
42begin
43  alter table MY_TABLE alter column modifiedDate datetime
44end
45	
46create table dbo.t1 (id int primary key, cola varchar(20))
47go
48
49insert dbo.t1 values (1, 'one')
50insert dbo.t1 values (2, 'two')   
51go
52
53if not exists(select  * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 't1' and COLUMN_NAME = 'colb')
54BEGIN
55    -- add new column 'colb', and set its value initially to existing values in 'cola' 
56    ALTER TABLE dbo.t1 ADD colb varchar(20)
57
58    DECLARE @v NVARCHAR(500)
59    SET @v = N'UPDATE dbo.t1 SET colb = cola'
60    EXEC (@v) -- use dynamic SQL, otherwise will get Invalid column name colb error.    
61END
62GO