· 7 years ago · Sep 12, 2018, 01:04 AM
1Alter Column datatype with primary key
2DECLARE @TableName AS VARCHAR(200)
3DECLARE TableCursor CURSOR LOCAL READ_ONLY FOR
4SELECT t.name AS TableName
5 FROM sys.columns c
6 JOIN sys.tables t ON c.object_id = t.object_id
7 WHERE c.name = 'ReferenceID'
8
9OPEN TableCursor
10 FETCH NEXT FROM TableCursor
11 INTO @TableName
12
13ALTER TABLE @TableName ALTER COLUMN ReferenceID VARCHAR(8)
14
15CREATE TABLE p
16(
17ReferenceID VARCHAR(6) NOT NULL PRIMARY KEY
18)
19
20INSERT INTO p VALUES ('AAAAAA')
21
22ALTER TABLE p ALTER COLUMN ReferenceID VARCHAR(8) NOT NULL
23
24Msg 5074, Level 16, State 1, Line 1
25The object 'PK__p__E1A99A792180FB33' is dependent on column 'ReferenceID'.
26Msg 4922, Level 16, State 9, Line 1
27ALTER TABLE ALTER COLUMN ReferenceID failed because one or more objects access this column.
28
29SET NOCOUNT ON
30
31/* Handle exceptional tables here
32 * Remove indexes and foreign keys
33 * --Lots of "IF EXISTS ... ALTER TABLE <name> DROP CONSTRAINT <constraint name>, etc.
34 */
35
36--Declare variables
37DECLARE @SQL VARCHAR(8000)
38DECLARE @TableName VARCHAR(512)
39DECLARE @ConstraintName VARCHAR(512)
40DECLARE @tColumn VARCHAR(512)
41DECLARE @Columns VARCHAR(8000)
42DECLARE @IsDescending BIT
43
44--Set up temporary table
45SELECT
46 tbl.[schema_id],
47 tbl.name AS TableName,
48 i.NAME AS IndexName,
49 i.type_desc,
50 c.[column],
51 c.key_ordinal,
52 c.is_desc,
53 i.[object_id],
54 s.no_recompute,
55 i.[ignore_dup_key],
56 i.[allow_row_locks],
57 i.[allow_page_locks],
58 i.[fill_factor],
59 dsi.type,
60 dsi.name AS DataSpaceName
61INTO #PKBackup
62FROM
63 sys.tables AS tbl
64 INNER JOIN sys.indexes AS i
65 ON (
66 i.index_id > 0
67 AND i.is_hypothetical = 0
68 )
69 AND ( i.[object_id] = tbl.[object_id] )
70 INNER JOIN (
71 SELECT
72 ic.[object_id] ,
73 c.[name] [column] ,
74 ic.is_descending_key [is_desc],
75 ic.key_ordinal
76 FROM
77 sys.index_columns ic
78 INNER JOIN
79 sys.indexes i
80 ON
81 i.[object_id] = ic.[object_id]
82 AND
83 i.index_id = 1
84 AND
85 ic.index_id = 1
86 INNER JOIN
87 sys.tables t
88 ON
89 t.[object_id] = ic.[object_id]
90 INNER JOIN
91 sys.columns c
92 ON
93 c.[object_id] = t.[object_id]
94 AND
95 c.column_id = ic.column_id
96 ) AS c
97 ON c.[object_id] = i.[object_id]
98 LEFT OUTER JOIN
99 sys.key_constraints AS k
100 ON
101 k.parent_object_id = i.[object_id]
102 AND
103 k.unique_index_id = i.index_id
104 LEFT OUTER JOIN
105 sys.data_spaces AS dsi
106 ON
107 dsi.data_space_id = i.data_space_id
108 LEFT OUTER JOIN
109 sys.xml_indexes AS xi
110 ON
111 xi.[object_id] = i.[object_id]
112 AND
113 xi.index_id = i.index_id
114 LEFT OUTER JOIN
115 sys.stats AS s
116 ON
117 s.stats_id = i.index_id
118 AND
119 s.[object_id] = i.[object_id]
120WHERE
121 k.TYPE = 'PK'
122
123DECLARE TableCursor CURSOR LOCAL READ_ONLY FOR
124 SELECT t.name AS TableName
125 FROM sys.columns c
126 JOIN sys.tables t ON c.object_id = t.object_id
127 WHERE
128 c.name = 'ReferenceID'
129
130OPEN TableCursor
131 FETCH NEXT FROM TableCursor
132 INTO @TableName
133
134WHILE @@FETCH_STATUS = 0
135BEGIN
136 PRINT('--Updating ' + @TableName + '...')
137
138 SELECT @ConstraintName = PK.CONSTRAINT_NAME
139 FROM
140 INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
141 WHERE
142 PK.TABLE_NAME = @TableName
143 AND
144 PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
145
146--drop the constraint
147 --Some tables don't have a PK defined, only do the next bit if they do
148 IF (SELECT COUNT(*) FROM #PKBackup PK WHERE PK.TableName = @TableName) > 0
149 BEGIN
150 SET @SQL = 'ALTER TABLE @TableName DROP CONSTRAINT @ConstraintName'
151 SET @SQL = REPLACE(@SQL, '@TableName', @TableName)
152 SET @SQL = REPLACE(@SQL, '@ConstraintName', @ConstraintName)
153 PRINT @SQL
154 EXEC (@SQL)
155 END
156--This is where we actually change the datatype of the column
157 SET @SQL = 'ALTER TABLE @TableName ALTER COLUMN ReferenceID VARCHAR(8)' + (SELECT CASE WHEN C.Is_Nullable = 'NO' THEN ' NOT NULL' ELSE '' END
158 FROM INFORMATION_SCHEMA.COLUMNS C
159 WHERE C.TABLE_NAME = @TableName AND C.COLUMN_NAME = 'ReferenceID')
160 SET @SQL = REPLACE(@SQL, '@TableName', @TableName)
161
162 PRINT(@SQL)
163 EXEC(@SQL)
164
165--Recreate the constraint
166 --Some tables don't have a PK defined, only do the next bit if they do
167 IF (SELECT COUNT(*) FROM #PKBackup PK WHERE PK.TableName = @TableName) > 0
168 BEGIN
169 --First set up @SQL template
170 SELECT @SQL = 'ALTER TABLE [' + SCHEMA_NAME(PK.schema_id) + '].[' + PK.TableName
171 + '] ADD CONSTRAINT [' + PK.IndexName
172 + '] PRIMARY KEY ' + Type_desc + ' ( @Columns ) WITH '
173 + '( PAD_INDEX = ' + CASE WHEN CAST(INDEXPROPERTY(pk.[object_id], PK.IndexName, N'IsPadIndex') AS BIT) = 0 THEN 'OFF'
174 ELSE 'ON'
175 END + ', '
176 + 'STATISTICS_NORECOMPUTE = ' + CASE WHEN pk.no_recompute = 0 THEN 'OFF'
177 ELSE 'ON'
178 END
179 + ', SORT_IN_TEMPDB = OFF, '
180 + 'IGNORE_DUP_KEY = ' + CASE WHEN pk.[ignore_dup_key] = 0 THEN 'OFF'
181 ELSE 'ON'
182 END + ', '
183 + 'ONLINE = OFF, '
184 + 'ALLOW_ROW_LOCKS = ' + CASE WHEN pk.allow_row_locks = 0 THEN 'OFF'
185 ELSE 'ON'
186 END + ', '
187 + 'ALLOW_PAGE_LOCKS = ' + CASE WHEN pk.allow_page_locks = 0 THEN 'OFF'
188 ELSE 'ON'
189 END + ', '
190 + 'FILLFACTOR = ' + CASE WHEN pk.[fill_factor] = 0 THEN '100'
191 ELSE CONVERT(NVARCHAR, pk.[fill_factor])
192 END + ' '
193 + ') ON [' + CASE WHEN 'FG' = pk.[type] THEN pk.DataSpaceName
194 ELSE N''
195 END + ']'
196 FROM
197 #PKBackup PK WHERE PK.TableName = @TableName
198
199 SET @SQL = REPLACE(@SQL, '@TableName', @TableName)
200 SET @SQL = REPLACE(@SQL, '@ConstraintName', @ConstraintName)
201
202 --Second, build up @Columns
203 SET @Columns = ' '
204 DECLARE ColumnCursor CURSOR LOCAL READ_ONLY FOR
205 SELECT pk.[column], PK.is_desc
206 FROM #PKBackup PK
207 WHERE PK.TableName = @TableName
208 ORDER BY PK.key_ordinal ASC
209
210 OPEN ColumnCursor
211 FETCH NEXT FROM ColumnCursor
212 INTO @tColumn, @IsDescending
213
214 WHILE @@FETCH_STATUS = 0
215 BEGIN
216 SET @Columns = @Columns + @tColumn + CASE WHEN @IsDescending = 1 THEN ' DESC, ' ELSE ' ASC, ' END
217
218 --Get the next TableName
219 FETCH NEXT FROM ColumnCursor
220 INTO @tColumn, @IsDescending
221 END
222
223 --Tidy up
224 CLOSE ColumnCursor
225 DEALLOCATE ColumnCursor
226
227 --Delete the last comma
228 SET @Columns = LEFT(@Columns, LEN(@Columns) - 1)
229 END
230--Recreate the constraint
231 SET @SQL = REPLACE(@SQL, '@Columns', @Columns)
232 PRINT @SQL
233 EXEC (@SQL)
234
235 PRINT('--Done
236 ')
237
238 SET @SQL = ''
239
240--Get the next TableName
241 FETCH NEXT FROM TableCursor
242 INTO @TableName
243END
244
245--Tidy up
246CLOSE TableCursor
247DEALLOCATE TableCursor
248
249DROP TABLE #PKBackup
250
251/* Handle exceptional tables here
252 * Replace indexes and foreign keys that were removed at the start
253 */
254
255SET NOCOUNT OFF