· 8 years ago · Jul 15, 2018, 07:16 PM
1--REMOVE THE DEFAULT CONSTRAINT FROM COLUMN__NAME COLUMN IN TABLE__NAME
2DECLARE @TableName varchar(512),@ColumnName varchar(512),@DefaultName varchar(512)
3SET @TableName='TABLE__NAME'
4SET @ColumnName='COLUMN__NAME'
5SELECT @DefaultName=df.[name] from syscolumns as sc
6 INNER JOIN sysobjects as so on so.ID = sc.ID
7 INNER JOIN sysobjects as df on so.ID = df.parent_obj
8 AND sc.cdefault = df.ID
9 WHERE ( (so.[name]=@TableName) AND
10 (sc.[name]=@ColumnName) AND
11 (df.xtype='D') )
12IF (@DefaultName is not NULL)
13 BEGIN
14 EXECUTE('ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @DefaultName)
15 print '--> table: ' + @TableName + ' constraint dropped'
16 END
17ELSE
18 print '--> table: ' + @TableName + ' is up to date'
19GO
20
21
22-- DROP FOREIGN KEY CONSTRAINT__NAME FROM TABLE__NAME
23IF EXISTS (
24 SELECT * FROM sys.foreign_keys
25 WHERE object_id = OBJECT_ID(N'[dbo].[CONSTRAINT__NAME]')
26 AND parent_object_id = OBJECT_ID(N'[dbo].[TABLE__NAME]') )
27 BEGIN
28 ALTER TABLE [TABLE__NAME]
29 DROP CONSTRAINT CONSTRAINT__NAME
30 print '--> table: [TABLE__NAME] CONSTRAINT__NAME constraint dropped'
31 END
32ELSE
33 print '--> table: [TABLE__NAME] CONSTRAINT__NAME is up to date'
34GO
35
36-- DROP CONSTRAINT__NAME FROM TABLE__NAME
37IF EXISTS
38 (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
39 WHERE CONSTRAINT_SCHEMA='dbo'
40 AND CONSTRAINT_NAME='CONSTRAINT__NAME'
41 AND TABLE_NAME='TABLE__NAME')
42 BEGIN
43 ALTER TABLE TABLE__NAME
44 DROP CONSTRAINT CONSTRAINT__NAME
45 print '--> table: TABLE__NAME CONSTRAINT__NAME constraint dropped'
46 END
47ELSE
48 print '--> table: TABLE__NAME CONSTRAINT__NAME is up to date'
49GO
50
51--DROP COLUMN__NAME COLUMN FROM TABLE__NAME
52IF EXISTS (
53 SELECT sc.[name] FROM syscolumns AS sc
54 INNER JOIN sysobjects AS so ON so.ID = sc.ID
55 WHERE so.[name] = 'TABLE__NAME'
56 AND sc.[name] = 'COLUMN__NAME' )
57 BEGIN
58 ALTER TABLE TABLE__NAME
59 DROP COLUMN COLUMN__NAME
60 print '--> table TABLE__NAME updated'
61 END
62ELSE
63 print '--> table TABLE__NAME up to date'
64GO
65
66-- ADD FOREIGN KEY CONSTRAINT__NAME CONSTRAINT
67IF NOT EXISTS
68 (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
69 WHERE CONSTRAINT_SCHEMA='dbo'
70 AND CONSTRAINT_NAME='CONSTRAINT__NAME'
71 AND TABLE_NAME='TABLE__NAME')
72 BEGIN
73 ALTER TABLE
74 [TABLE__NAME]
75 ADD CONSTRAINT
76 [CONSTRAINT__NAME]
77 FOREIGN KEY
78 ([COLUMN__NAME__ID])
79 REFERENCES
80 [SLAVE__TABLE] ([Id])
81 ON UPDATE NO ACTION
82 ON DELETE NO ACTION
83 END
84GO
85print '--> table: TABLE__NAME'
86GO
87
88-- ADD CHECK CONSTRAINT CONSTRAINT__NAME TO TABLE__NAME
89IF NOT EXISTS
90 (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
91 WHERE CONSTRAINT_SCHEMA='dbo'
92 AND CONSTRAINT_NAME='CONSTRAINT__NAME'
93 AND TABLE_NAME='TABLE__NAME')
94 BEGIN
95 ALTER TABLE dbo.TABLE__NAME
96 ADD CONSTRAINT
97 CONSTRAINT__NAME
98 CHECK
99 ([COLUMN__NAME] is NULL OR [COLUMN__NAME] >= 0)
100 print '--> table: TABLE__NAME CONSTRAINT__NAME constraint added'
101 END
102ELSE
103 print '--> table: TABLE__NAME CONSTRAINT__NAME is up to date'
104GO
105
106
107-- ALTER DF CONSTRAINT FOR COL__NAME IN TABLE__NAME
108IF EXISTS
109 (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
110 WHERE CONSTRAINT_SCHEMA='dbo'
111 AND CONSTRAINT_NAME='CONSTRAINT__NAME'
112 AND TABLE_NAME='TABLE__NAME')
113 BEGIN
114 ALTER TABLE TABLE__NAME
115 DROP CONSTRAINT CONSTRAINT__NAME
116 print '--> table: TABLE__NAME CONSTRAINT__NAME constraint dropped'
117 END
118ELSE
119 print '--> table: TABLE__NAME CONSTRAINT__NAME is up to date'
120GO
121IF NOT EXISTS
122 (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
123 WHERE CONSTRAINT_SCHEMA='dbo'
124 AND CONSTRAINT_NAME='CONSTRAINT__NAME'
125 AND TABLE_NAME='TABLE__NAME')
126 BEGIN
127 ALTER TABLE dbo.TABLE__NAME
128 ADD CONSTRAINT DF_TABLE__NAME_COL__NAME DEFAULT 1 FOR COL__NAME
129 print '--> table: TABLE__NAME_COL__NAME constraint added'
130 END
131ELSE
132 print '--> table: TABLE__NAME is up to date'
133GO
134
135
136--ALTER COLUMN__NAME COLUMN IN TABLE__NAME
137IF EXISTS (
138 SELECT sc.[name] FROM syscolumns AS sc
139 INNER JOIN sysobjects AS so ON so.ID = sc.ID
140 WHERE so.[name] = 'TABLE__NAME'
141 AND sc.[name] = 'COLUMN__NAME' )
142 BEGIN
143 ALTER TABLE TABLE__NAME
144 ALTER COLUMN COLUMN__NAME int null
145 print '--> table: TABLE__NAME altered'
146 END
147ELSE
148 print '--> table: TABLE__NAME is up to date'
149GO
150
151--ADD COLUMN__NAME COLUMN TO TABLE__NAME
152IF NOT EXISTS (
153 SELECT sc.[name] FROM syscolumns AS sc
154 INNER JOIN sysobjects AS so ON so.ID = sc.ID
155 WHERE so.[name] = 'TABLE__NAME'
156 AND sc.[name] = 'COLUMN__NAME' )
157 BEGIN
158 ALTER TABLE TABLE__NAME
159 ADD COLUMN__NAME varchar(255) NOT null DF_TABLE__NAME_COLUMN__NAME DEFAULT('')
160 print '--> table: TABLE__NAME updated'
161 END
162ELSE
163 print '--> table: TABLE__NAME up to date'
164GO
165
166--UPDATE COLUMN__NAME VALUE IN TABLE__NAME
167IF NOT EXISTS (
168 SELECT sc.[name] FROM syscolumns AS sc
169 INNER JOIN sysobjects AS so ON so.ID = sc.ID
170 WHERE so.[name] = 'TABLE__NAME'
171 AND sc.[name] = 'COLUMN__NAME' )
172 BEGIN
173 UPDATE TABLE__NAME
174 SET COLUMN__NAME = NEW__VALUE
175 WHERE THROW__ERROR__MUSTBEADDEDTO
176 print '--> column COLUMN__NAME values updated'
177 END
178GO
179
180-- CREATE TABLE
181IF EXISTS (
182 SELECT * FROM sysobjects
183 WHERE id = OBJECT_ID(N'dbo.[TABLE__NAME]')
184 AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
185 DROP TABLE dbo.[TABLE__NAME]
186 print '--> table: TABLE__NAME dropped'
187GO
188CREATE TABLE dbo.[TABLE__NAME] (
189 [Id] int identity(1,1) NOT NULL,
190 [GUID] uniqueidentifier NOT NULL CONSTRAINT DF_TABLE__NAME_GUID DEFAULT(NEWID()),
191 [ModifierId] int NULL,
192 [DateCreated] datetime NOT NULL CONSTRAINT DF_TABLE__NAME_DateCreated DEFAULT(GETDATE()),
193 [DateModified] datetime NOT NULL CONSTRAINT DF_TABLE__NAME_DateModified DEFAULT(GETDATE())
194 CONSTRAINT PK_TABLE__NAME PRIMARY KEY CLUSTERED (
195 [Id]
196 )
197)
198GO
199print '--> table: TABLE__NAME created'
200GO
201
202exec wctConfig_HelpCreateWithValue 'MODULE__NAME','PAGE__IDENTIFIER','SECTION__HEADER','DESCRIPTION',99,'TITLE'
203GO
204
205--ADD COLUMN__NAME COLUMN TO TABLE__NAME
206IF NOT EXISTS (
207 SELECT sc.[name] FROM syscolumns AS sc
208 INNER JOIN sysobjects AS so ON so.ID = sc.ID
209 WHERE so.[name] = 'TABLE__NAME'
210 AND sc.[name] = 'COLUMN__NAME' )
211 BEGIN
212 ALTER TABLE TABLE__NAME
213 ADD COLUMN__NAME varchar(255) NOT null DF_TABLE__NAME_COLUMN__NAME DEFAULT('')
214 print '--> table: TABLE__NAME updated'
215 END
216ELSE
217 print '--> table: TABLE__NAME up to date'
218GO
219
220
221-- UPDATE/MOD PROC__NAME STORED PROC
222IF EXISTS (
223 SELECT * FROM sysobjects
224 WHERE id = OBJECT_ID(N'[PROC__NAME]')
225 AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
226 BEGIN
227 DROP PROCEDURE [dbo].PROC__NAME
228 print '--> proc: PROC__NAME dropped'
229 END
230GO
231CREATE PROCEDURE [dbo].[PROC__NAME]
232 @GUID uniqueidentifier
233AS
234 BEGIN
235 declare @err int
236 set @err = 0
237
238 SELECT top 1 *
239 FROM SKY
240 WHERE ABOVE
241
242 set @err = @@ERROR
243 return @err
244 END
245GO
246print '--> proc: PROC__NAME created'
247GO
248
249--Cursor for TABLE__NAME @COLUMN__NAME
250DECLARE @COLUMN__NAME int
251DECLARE @message varchar(25)
252DECLARE TABLE__NAME_cursor CURSOR FOR
253 SELECT CT.ID
254 FROM TABLE__NAME AS CT
255
256OPEN TABLE__NAME_cursor;
257
258FETCH NEXT FROM TABLE__NAME_cursor INTO @COLUMN__NAME;
259
260 WHILE @@FETCH_STATUS = 0
261 BEGIN
262 --SELECT * FROM TABLE__NAME WHERE id = @COLUMN__NAME
263 SELECT @message = ' ' + @COLUMN__NAME
264 PRINT 'COLUMN__NAME: ' + @message
265
266 FETCH NEXT FROM TABLE__NAME_cursor INTO @COLUMN__NAME;
267 END;
268
269CLOSE TABLE__NAME_cursor;
270DEALLOCATE TABLE__NAME_cursor;