-
Notifications
You must be signed in to change notification settings - Fork 2
/
generate_drop_and_add_object_tsql_in_dependency_order.sql
655 lines (618 loc) · 31.5 KB
/
generate_drop_and_add_object_tsql_in_dependency_order.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
/*
The SQL functions (below) and command (at the very bottom)
generates DROP and CREATE commands for all PKs, FKs, and
indexes in dependency order below a user-specified TABLE_NAME.
It does not execute the DROP or CREATE commands; it only generates the TSQL code.
I used it to change about 300 datetime2 columns to datetime on a database with 11 levels of dependencies.
Set your SSMS output to "results to text," with up to 8000 characters per column.
Function deps_it_depends:
Given a schema, table name (as a starting point), and hard-coded 1,
return a list of all database objects and dependencies.
Slightly expanded from Phil Factor's code. (His method of gathering the dependencies
is pretty ingenious!)
The hard-coded 1 is to retrieve dependencies "upward," and 0 is "downward." I only used 1
for my use case.
Function deps_generate_create_and_drop_index:
Given a schema and index name or PK name, return the code to drop and create the object.
Function deps_generate_create_and_drop_fk:
Given a schema and FK name, return the code to drop and create the object.
*/
IF OBJECT_ID (N'dbo.deps_it_depends') IS NOT NULL
DROP FUNCTION dbo.deps_it_depends
GO
CREATE FUNCTION dbo.deps_it_depends (@p_schema varchar(255), @ObjectName varchar(200), @ObjectsOnWhichItDepends bit)
RETURNS @References TABLE (
ObjectPath VARCHAR(MAX), --the ancestor objects delimited by a '/'
ObjectSchema VARCHAR(200),
ObjectName VARCHAR(200),
ObjectType VARCHAR(20),
iteration INT
)
/**
summary: >
This Table function returns a a table giving the dependencies of the object whose name
is supplied as a parameter.
At the moment, only objects are allowed as a parameter, You can specify whether you
want those objects that rely on the object, or those on whom the object relies.
compatibility: SQL Server 2005 - SQL Server 2012
Revisions:
- Author: Phil Factor
Version: 1.1
Modified by William Meitzen
*/
AS
BEGIN
DECLARE @DatabaseDependencies TABLE (
ObjectSchemaName varchar(255),
ObjectName VARCHAR(200),
ObjectType CHAR(5),
DependencyType CHAR(4), -- hard or soft
ReferredObjectSchema VARCHAR(200),
ReferredObjectName VARCHAR(200),
ReferredType CHAR(5)
)
INSERT INTO @DatabaseDependencies ( ObjectSchemaName, ObjectName, ObjectType, DependencyType, ReferredObjectSchema, ReferredObjectName, ReferredType )
-- tables that reference UDTs
SELECT object_schema_name(o.object_id), o.name, o.type, 'hard', object_schema_name(c.object_id), ty.name, 'UDT'
FROM sys.objects o
INNER JOIN sys.columns AS c ON c.object_id = o.object_id
INNER JOIN sys.types ty ON ty.user_type_id = c.user_type_id
WHERE is_user_defined = 1
UNION ALL
-- udtts that reference UDTTs
SELECT object_schema_name(tt.type_table_object_id), tt.name, 'UDTT', 'hard', object_schema_name(c.object_id), ty.name, 'UDT'
FROM sys.table_types tt
INNER JOIN sys.columns AS c ON c.object_id = tt.type_table_object_id
INNER JOIN sys.types ty ON ty.user_type_id = c.user_type_id
WHERE ty.is_user_defined = 1
UNION ALL
--tables/views that reference triggers
SELECT object_schema_name(o.object_id), o.name, o.type, 'hard', object_schema_name(t.object_id), t.name, t.type
FROM sys.objects t
INNER JOIN sys.objects AS o ON o.parent_object_id = t.object_id
WHERE o.type = 'TR'
UNION ALL
-- tables that reference defaults via columns (only default objects)
SELECT object_schema_name(clmns.object_id), object_name(clmns.object_id), 'U', 'hard', object_schema_name(o.object_id), o.name, o.type
FROM sys.objects o
INNER JOIN sys.columns AS clmns ON clmns.default_object_id = o.object_id
WHERE o.parent_object_id = 0
UNION ALL
-- types that reference defaults (only default objects)
SELECT object_schema_name(o.object_id), o.name, 'UDT', 'hard', object_schema_name(o.object_id), types.name, o.type
FROM sys.objects o
INNER JOIN sys.types AS types ON types.default_object_id = o.object_id
WHERE o.parent_object_id = 0
UNION ALL
-- tables that reference rules via columns
SELECT object_schema_name(clmns.object_id), object_name(clmns.object_id), 'U', 'hard', object_schema_name(o.object_id), o.name, o.type
FROM sys.objects o
INNER JOIN sys.columns AS clmns ON clmns.rule_object_id = o.object_id
UNION ALL
-- types that reference rules
SELECT object_schema_name(o.object_id), types.name, 'UDT', 'hard', object_schema_name(o.object_id), o.name, o.type
FROM sys.objects o
INNER JOIN sys.types AS types ON types.rule_object_id = o.object_id
UNION ALL
-- tables that reference XmlSchemaCollections
SELECT object_schema_name(clmns.object_id), object_name(clmns.object_id), 'U', 'hard', object_schema_name(clmns.object_id), xml_schema_collections.name, 'XMLC'
FROM sys.columns clmns --should we eliminate views?
INNER JOIN sys.xml_schema_collections ON xml_schema_collections.xml_collection_id = clmns.xml_collection_id
UNION ALL
-- table types that reference XmlSchemaCollections
SELECT object_schema_name(clmns.object_id), object_name(clmns.object_id), 'UDTT', 'hard', object_schema_name(clmns.object_id), xml_schema_collections.name, 'XMLC'
FROM sys.columns AS clmns
INNER JOIN sys.table_types AS tt ON tt.type_table_object_id = clmns.object_id
INNER JOIN sys.xml_schema_collections ON xml_schema_collections.xml_collection_id = clmns.xml_collection_id
UNION ALL
-- procedures that reference XmlSchemaCollections
SELECT object_schema_name(params.object_id), o.name, o.type, 'hard', object_schema_name(params.object_id), xml_schema_collections.name, 'XMLC'
FROM sys.parameters AS params
INNER JOIN sys.xml_schema_collections ON xml_schema_collections.xml_collection_id = params.xml_collection_id
INNER JOIN sys.objects o ON o.object_id = params.object_id
/*
UNION ALL
-- table references table - commented out b/c FK name is skipped
SELECT object_schema_name(tbl.object_id), tbl.name, tbl.type, 'hard', object_schema_name(referenced_object_id), object_name(referenced_object_id), 'U'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS tbl ON tbl.object_id = fk.parent_object_id
*/
-- begin FK
union all
-- fk_name -> referencING table (alter table [referencING] add constraint fk_name ... references [referencED])
SELECT object_schema_name(fk.object_id), fk.name, fk.type, 'hard', object_schema_name(tbl.object_id), tbl.name, 'FK'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS tbl ON tbl.object_id = fk.parent_object_id
-- referencED table -> fk_name (alter table [referencING] add constraint fk_name ... references [referencED])
union all
select object_schema_name(fkc.referenced_object_id), OBJECT_NAME(fkc.referenced_object_id), 'Ren', 'hard', object_schema_name(fk.object_id), fk.name, 'FR'
from sys.foreign_key_columns as fkc
inner join sys.foreign_keys as fk on fkc.constraint_object_id = fk.object_id
group by fkc.referenced_object_id, fk.object_id, fk.name
-- end FK
-- begin indexes
-- clustered index name and table name - cl index -> table
union all
select object_schema_name(IDX.[object_id]), IDX.name, 'CI', 'hard', object_schema_name(O.[object_id]), OBJECT_NAME(O.[object_id]), 'CI'
from sys.indexes as IDX
inner join sys.objects as O on IDX.[object_id] = O.[object_id]
where O.type_desc = 'USER_TABLE'
and IDX.type_desc = 'CLUSTERED'
-- clustered index name and table name - table -> cl index
union all
select
object_schema_name(O.[object_id]), OBJECT_NAME(O.[object_id])
, 'CI', 'hard'
, object_schema_name(IDX.[object_id]), IDX.name
, 'CI'
from sys.indexes as IDX
inner join sys.objects as O on IDX.[object_id] = O.[object_id]
where O.type_desc = 'USER_TABLE'
and IDX.type_desc = 'CLUSTERED'
-- nc index name and table name - nc index -> table
union all
--( ObjectSchemaName, ObjectName, ObjectType, DependencyType, ReferredObjectSchema, ReferredObjectName, ReferredType )
--select object_schema_name(IDX.[object_id]), IDX.name, 'NCI', 'hard', object_schema_name(O.[object_id]), OBJECT_NAME(O.[object_id]), 'NCI'
select object_schema_name(IDX.[object_id]), IDX.name, 'NCI', 'hard', object_schema_name(O.[object_id]), OBJECT_NAME(O.[object_id]), 'NCI'
from sys.indexes as IDX
inner join sys.objects as O on IDX.[object_id] = O.[object_id]
where O.type_desc = 'USER_TABLE'
and IDX.type_desc <> 'CLUSTERED'
and IDX.name is not null -- filter out heaps
-- nc index name and table name - table -> index
union all
select
object_schema_name(O.[object_id]), OBJECT_NAME(O.[object_id])
, 'NCI', 'hard'
, object_schema_name(IDX.[object_id]), IDX.name
, 'NCI'
from sys.indexes as IDX
inner join sys.objects as O on IDX.[object_id] = O.[object_id]
where O.type_desc = 'USER_TABLE'
and IDX.type_desc <> 'CLUSTERED'
and IDX.name is not null -- filter out heaps
-- end indexes
UNION ALL
-- uda references types
SELECT object_schema_name(params.object_id), o.name, o.type, 'hard', object_schema_name(params.object_id), types.name, 'UDT'
FROM sys.parameters AS params
INNER JOIN sys.types ON types.user_type_id = params.user_type_id
INNER JOIN sys.objects o ON o.object_id = params.object_id
WHERE is_user_defined <> 0
UNION ALL
-- table,view references partition scheme
SELECT object_schema_name(o.object_id), o.name, o.type, 'hard', object_schema_name(idx.object_id), ps.name, 'PS'
FROM sys.indexes AS idx
INNER JOIN sys.partitions p ON idx.object_id = p.object_id AND idx.index_id = p.index_id
INNER JOIN sys.partition_schemes ps ON idx.data_space_id = ps.data_space_id
INNER JOIN sys.objects AS o ON o.object_id = idx.object_id
UNION ALL
-- partition scheme references partition function
SELECT object_schema_name(o.object_id), ps.name, 'PS', 'hard', object_schema_name(o.object_id), o.name, o.type
FROM sys.partition_schemes ps
INNER JOIN sys.objects AS o ON ps.function_id = o.object_id
UNION ALL
-- plan guide references sp, udf, triggers
SELECT object_schema_name(o.object_id), pg.name, 'PG', 'hard', object_schema_name(o.object_id), o.name, o.type
FROM sys.objects o
INNER JOIN sys.plan_guides AS pg ON pg.scope_object_id = o.object_id
UNION ALL
-- synonym refrences object
SELECT object_schema_name(o.object_id), s.name, 'SYN', 'hard', object_schema_name(o.object_id), o.name, o.type
FROM sys.objects o
INNER JOIN sys.synonyms AS s ON object_id(s.base_object_name) = o.object_id
UNION ALL
-- sequences that reference uddts
SELECT object_schema_name(o.object_id), s.name, 'SYN', 'hard', object_schema_name(o.object_id), o.name, o.type
FROM sys.objects o
INNER JOIN sys.sequences AS s ON s.user_type_id = o.object_id
UNION ALL
SELECT DISTINCT
coalesce(object_schema_name(referencing_id), ''), object_name(referencing_id), referencer.type, 'soft'
--,coalesce(referenced_schema_name, '') --likely schema name
,object_schema_name(referencer.object_id)
,coalesce(referenced_entity_name, ''), --very likely entity name
referenced.type
FROM sys.sql_expression_dependencies
INNER JOIN sys.objects referencer ON referencing_id = referencer.object_id
INNER JOIN sys.objects referenced ON referenced_id = referenced.object_id
WHERE referencing_class = 1 AND referenced_class = 1 AND referencer.type IN ( 'v', 'tf', 'f', 'fk', 'fn', 'p', 'tr', 'u' )
DECLARE @rowcount INT
DECLARE @ii INT
-- firstly we put in the object as a seed.
INSERT INTO @References (ObjectPath, ObjectSchema, ObjectName, ObjectType, iteration) -- ( ThePath, TheFullEntityName, TheType, iteration )
SELECT coalesce(object_schema_name(object_id) + '.', '') + name -- ObjectPath: full path with "/"
, coalesce(object_schema_name(object_id), ''), name, type, 1 -- original is "1"
FROM sys.objects
WHERE object_schema_name(object_id) = @p_schema and name = @ObjectName
-- then we just pull out the dependencies at each level. watching out for
-- self-references and circular references
SELECT @rowcount = @@ROWCOUNT
set @ii = 2 -- original
--set @ii = 1
IF @ObjectsOnWhichItDepends <> 0 --if we are looking for objects on which it depends
WHILE @ii < 50 AND @rowcount > 0
BEGIN
INSERT INTO @References (ObjectPath, ObjectSchema, ObjectName, ObjectType, iteration) -- ( ThePath, TheFullEntityName, TheType, iteration )
SELECT DISTINCT
PR.ObjectPath + '/' + DD.ReferredObjectSchema + '.' + DD.ReferredObjectName -- ObjectPath: full path with "/"
, DD.ReferredObjectSchema, DD.ReferredObjectName, DD.ReferredType, @ii
FROM @DatabaseDependencies as DD
INNER JOIN @References as PR ON /*PR.TheFullEntityName = DD.EntityName*/
-- @DatabaseDependencies ( ObjectSchemaName, ObjectName, ObjectType, DependencyType, ReferredObjectSchema, ReferredObjectName, ReferredType )
PR.ObjectSchema = DD.ObjectSchemaName
and PR.ObjectName = DD.ObjectName
AND PR.iteration = @ii - 1
--WHERE TheReferredEntity <> DD.EntityName
WHERE DD.ObjectSchemaName+'.'+DD.ObjectName <> DD.ReferredObjectSchema+'.'+DD.ReferredObjectName
--AND TheReferredEntity NOT IN (SELECT TheFullEntityName FROM @References)
and DD.ReferredObjectSchema+'.'+DD.ReferredObjectName not in (select ObjectSchema+'.'+ObjectName from @References)
SELECT @rowcount = @@rowcount
SELECT @ii = @ii + 1
END
ELSE --we are looking for objects that depend on it.
WHILE @ii < 50 AND @rowcount > 0
BEGIN
INSERT INTO @References (ObjectPath, ObjectSchema, ObjectName, ObjectType, iteration) -- ( ThePath, TheFullEntityName, TheType, iteration )
SELECT DISTINCT
PR.ObjectPath + '/' + DD.ObjectSchemaName + '.' + DD.ObjectName -- ObjectPath: full path with "/"
, DD.ObjectSchemaName, DD.ObjectName, DD.ObjectType, @ii
FROM @DatabaseDependencies as DD
--INNER JOIN @References as PR ON PR.TheFullEntityName = TheReferredEntity
INNER JOIN @References as PR ON
PR.ObjectSchema = DD.ReferredObjectSchema
and PR.ObjectName = DD.ReferredObjectName
AND PR.iteration = @ii - 1
--WHERE TheReferredEntity<>EntityName
WHERE DD.ReferredObjectSchema+'.'+DD.ReferredObjectName <> DD.ObjectSchemaName+'.'+DD.ObjectName
--AND EntityName NOT IN (SELECT TheFullEntityName FROM @References)
and DD.ObjectSchemaName+'.'+DD.ObjectName not in (select ObjectSchema+'.'+ObjectName from @References)
SELECT @rowcount = @@rowcount
SELECT @ii = @ii + 1
END
;with cte_current_table_info as (
-- clustered index name and table name - cl index -> table
select object_schema_name(IDX.[object_id]) as [object_schema_name]
, IDX.name as [object_name]
, 'CI' as object_type
, object_schema_name(O.[object_id]) as table_schema
, OBJECT_NAME(O.[object_id]) as table_name
from sys.indexes as IDX
inner join sys.objects as O on IDX.[object_id] = O.[object_id]
where O.type_desc = 'USER_TABLE'
and IDX.type_desc = 'CLUSTERED'
and O.name = @ObjectName
-- nc index name and table name - nc index -> table
union
--( ObjectSchemaName, ObjectName, ObjectType, DependencyType, ReferredObjectSchema, ReferredObjectName, ReferredType )
--select object_schema_name(IDX.[object_id]), IDX.name, 'NCI', 'hard', object_schema_name(O.[object_id]), OBJECT_NAME(O.[object_id]), 'NCI'
select object_schema_name(IDX.[object_id]) as [object_schema_name]
, IDX.name as [object_name]
, 'NCI' as object_type
, object_schema_name(O.[object_id]) as table_schema
, OBJECT_NAME(O.[object_id]) as table_name
from sys.indexes as IDX
inner join sys.objects as O on IDX.[object_id] = O.[object_id]
where O.type_desc = 'USER_TABLE'
and IDX.type_desc <> 'CLUSTERED'
and IDX.name is not null -- filter out heaps
and O.name = @ObjectName
union
-- fk_name -> referencING table (alter table [referencING] add constraint fk_name ... references [referencED])
SELECT object_schema_name(fk.object_id) as [object_schema_name]
, fk.name as [object_name]
, fk.type as object_type
, object_schema_name(tbl.object_id) as table_schema
, OBJECT_NAME(tbl.[object_id]) as table_name
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS tbl ON tbl.object_id = fk.parent_object_id
where tbl.name = @ObjectName
)
INSERT INTO @References (ObjectPath, ObjectSchema, ObjectName, ObjectType, iteration) -- ( ThePath, TheFullEntityName, TheType, iteration )
SELECT --DISTINCT
'/dbo.' + @ObjectName + '/' + CTI.[object_schema_name] + '.' + CTI.[object_name]
,CTI.[object_schema_name], CTI.[object_name], CTI.object_type, 0
from cte_current_table_info as CTI
RETURN
END
go
/*
select * from deps_it_depends('dbo', 'table_name', 1)
*/
IF OBJECT_ID (N'dbo.deps_generate_create_and_drop_index') IS NOT NULL
DROP FUNCTION dbo.deps_generate_create_and_drop_index
GO
create function deps_generate_create_and_drop_index (@p_schema varchar(255), @p_index_name varchar(255))
RETURNS @sql_commands TABLE (
ObjectSchema VARCHAR(200)
,ObjectTableName varchar(200)
,ObjectName VARCHAR(200)
,IsPrimaryKey bit
,TypeDesc varchar(200)
,create_command varchar(max)
,drop_command varchar(max)
)
as
begin
;with cte_index_column_names as (
SELECT
SCHEMA_NAME(tbl.schema_id) as [schema],
tbl.name as table_name,
i.name AS index_name,
case when xi.xml_index_type_description is null then i.type_desc else xi.xml_index_type_description end as type_desc,
i.is_primary_key,
case when i.is_unique=1 then 'unique ' else '' end as [unique],
i.is_unique,
xi2.name as parent_xml_index,
xi.secondary_type_desc as secondary_xml_type_desc,
xi2.xml_index_type_description,
(
ltrim(stuff((
SELECT
', '
+quotename(clmns.name)
+' '
+CASE WHEN sub_ic.is_descending_key = 1 THEN 'desc' ELSE 'asc' END
from sys.tables as sub_tbl
inner join sys.indexes as sub_i on sub_i.index_id>0 and sub_i.is_hypothetical=0 and sub_i.[object_id]=sub_tbl.[object_id]
inner join sys.index_columns as sub_ic on (sub_ic.column_id > 0 and (sub_ic.key_ordinal > 0 or sub_ic.partition_ordinal = 0)) AND (sub_ic.index_id=CAST(sub_i.index_id AS int) AND sub_ic.[object_id]=sub_i.[object_id])
inner join sys.columns as clmns on clmns.[object_id]=sub_ic.[object_id] and clmns.column_id=sub_ic.column_id
where sub_i.[object_id]=i.[object_id] and sub_i.index_id=i.index_id
and sub_ic.is_included_column=0
order by sub_ic.key_ordinal
FOR XML PATH('')
), 1, len(', '), ''))
) as csv_index_columns_with_order
,(
ltrim(stuff((
SELECT
', '
+quotename(clmns.name)
from sys.tables as sub_tbl
inner join sys.indexes as sub_i on sub_i.index_id>0 and sub_i.is_hypothetical=0 and sub_i.[object_id]=sub_tbl.[object_id]
inner join sys.index_columns as sub_ic on (sub_ic.column_id > 0 and (sub_ic.key_ordinal > 0 or sub_ic.partition_ordinal = 0)) AND (sub_ic.index_id=CAST(sub_i.index_id AS int) AND sub_ic.[object_id]=sub_i.[object_id])
inner join sys.columns as clmns on clmns.[object_id]=sub_ic.[object_id] and clmns.column_id=sub_ic.column_id
where sub_i.[object_id]=i.[object_id] and sub_i.index_id=i.index_id
and sub_ic.is_included_column=0
order by sub_ic.key_ordinal
FOR XML PATH('')
), 1, len(', '), ''))
) as csv_index_columns_without_order
,(
ltrim(stuff((
SELECT
', '
+quotename(clmns.name)
from sys.tables as sub_tbl
inner join sys.indexes as sub_i on sub_i.index_id>0 and sub_i.is_hypothetical=0 and sub_i.[object_id]=sub_tbl.[object_id]
inner join sys.index_columns as sub_ic on (sub_ic.column_id > 0 and (sub_ic.key_ordinal > 0 or sub_ic.partition_ordinal = 0 or sub_ic.is_included_column != 0)) AND (sub_ic.index_id=CAST(sub_i.index_id AS int) AND sub_ic.[object_id]=sub_i.[object_id])
inner join sys.columns as clmns on clmns.[object_id]=sub_ic.[object_id] and clmns.column_id=sub_ic.column_id
where sub_i.[object_id]=i.[object_id] and sub_i.index_id=i.index_id
AND sub_ic.is_included_column = 1
order by sub_ic.key_ordinal
FOR XML PATH('')
), 1, len(', '), ''))
) as csv_include_columns
,(
select cast(count(*) as bit)
from sys.columns as sub_c
inner join sys.tables as sub_t on sub_c.[object_id]=sub_t.[object_id]
where
sub_t.[object_id]=tbl.[object_id]
and sub_c.is_computed=1
) as table_has_computed_columns
,i.filter_definition
,indexedpaths.name AS indexed_xml_path_name
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.[object_id]=tbl.[object_id])
LEFT OUTER JOIN sys.stats AS s ON s.stats_id = i.index_id AND s.[object_id] = i.[object_id]
LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.[object_id] AND k.unique_index_id = i.index_id
LEFT OUTER JOIN sys.xml_indexes AS xi ON xi.[object_id] = i.[object_id] AND xi.index_id = i.index_id
LEFT OUTER JOIN sys.xml_indexes AS xi2 ON xi2.[object_id] = xi.[object_id] AND xi2.index_id = xi.using_xml_index_id
LEFT OUTER JOIN sys.spatial_indexes AS spi ON i.[object_id] = spi.[object_id] and i.index_id = spi.index_id
LEFT OUTER JOIN sys.spatial_index_tessellations as si ON i.[object_id] = si.[object_id] and i.index_id = si.index_id
LEFT OUTER JOIN sys.data_spaces AS dsi ON dsi.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.tables AS t ON t.[object_id] = i.[object_id]
LEFT OUTER JOIN sys.data_spaces AS dstbl ON dstbl.data_space_id = t.filestream_data_space_id and i.index_id < 2
LEFT OUTER JOIN sys.filetable_system_defined_objects AS filetableobj ON i.[object_id] = filetableobj.[object_id]
LEFT OUTER JOIN sys.selective_xml_index_paths AS indexedpaths ON xi.[object_id] = indexedpaths.[object_id] AND xi.using_xml_index_id = indexedpaths.index_id AND xi.path_id = indexedpaths.path_id
)
, command_parts as (
select
[schema] as unquoted_schema
,quotename([schema]) as [schema]
,quotename([schema])+'.'+quotename(table_name) as qualified_table_name
,[schema]+'.'+table_name as unquoted_qualified_table_name
,table_name as unquoted_table_name
,quotename(index_name) as index_name
,index_name as unquoted_index_name
,is_primary_key
,[type_desc]
,is_unique
,case
when table_has_computed_columns=1 or [type_desc] in ('PRIMARY_XML', 'SECONDARY_XML') then 'set quoted_identifier on' else 'set quoted_identifier off'
end
as quoted_identifier_setting
,case
when is_primary_key=1 then 'alter table '+quotename([schema])+'.'+quotename(table_name)+' add constraint '+quotename(index_name)+' primary key '+[type_desc]
when is_unique=1 then 'alter table '+quotename([schema])+'.'+quotename(table_name)+' add constraint '+quotename(index_name)+' unique '+[type_desc]
when [type_desc]='PRIMARY_XML' then 'create primary xml index '+quotename(index_name)+' on '+quotename([schema])+'.'+quotename(table_name)
when [type_desc]='SECONDARY_XML' then 'create xml index '+quotename(index_name)+' on '+quotename([schema])+'.'+quotename(table_name)
else 'create '+[unique]+[type_desc] collate database_default +' index '+quotename(index_name)+' on '+quotename([schema])+'.'+quotename(table_name)
end as create_command_prefix
,case
when type_desc in ('PRIMARY_XML', 'SECONDARY_XML') then csv_index_columns_without_order
else csv_index_columns_with_order
end as csv_index_columns
,case
when type_desc='SECONDARY_XML' then 'using xml index '+quotename(parent_xml_index)+' for '+secondary_xml_type_desc collate database_default
end as secondary_xml_index_using
,case when csv_include_columns is not null then 'include ('+csv_include_columns+')' end as include_clause
,case when filter_definition is not null then 'where '+filter_definition end as filter_definition
,case
when is_primary_key=1 or is_unique=1 then 'alter table '+quotename([schema])+'.'+quotename(table_name)+' drop constraint '+quotename(index_name)
when [type_desc]='PRIMARY_XML' then 'drop index '+quotename(index_name)+' on '+quotename([schema])+'.'+quotename(table_name)
when [type_desc]='SECONDARY_XML' then 'drop index '+quotename(index_name)+' on '+quotename([schema])+'.'+quotename(table_name)
else 'drop index '+quotename(index_name)+' on '+quotename([schema])+'.'+quotename(table_name)
end as drop_command
from cte_index_column_names
)
insert into @sql_commands (
ObjectSchema
,ObjectTableName
,ObjectName
,IsPrimaryKey
,TypeDesc
,create_command
,drop_command
)
select
unquoted_schema
,unquoted_table_name
,unquoted_index_name
,is_primary_key
,[type_desc]
,create_command_prefix + ' (' + csv_index_columns + ')'
+ coalesce(' ' + secondary_xml_index_using, '')
+ coalesce(' ' + include_clause, '')
+ coalesce(' ' + filter_definition, '')
as create_command
,drop_command
from command_parts
where
unquoted_schema = @p_schema
and unquoted_index_name = @p_index_name
;
return
end
go
IF OBJECT_ID (N'dbo.deps_generate_create_and_drop_fk') IS NOT NULL
DROP FUNCTION dbo.deps_generate_create_and_drop_fk
GO
create function deps_generate_create_and_drop_fk (@p_schema varchar(255), @p_fk_name varchar(255))
RETURNS @sql_commands TABLE (
ObjectSchema VARCHAR(200)
,ObjectTableName varchar(200)
,ObjectName VARCHAR(200)
,create_command varchar(max)
,drop_command varchar(max)
)
as
begin
;with command_parts as (
SELECT QUOTENAME(fk.name) AS [const_name]
,fk.name as unquoted_constraint_name
,QUOTENAME(schParent.name) + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)) AS [parent_obj]
,schParent.name as unquoted_parent_schema
,OBJECT_NAME(fkc.parent_object_id) as unquoted_parent_table_name
,STUFF((
SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcP.parent_column_id))
FROM sys.foreign_key_columns AS fcP
WHERE fcP.constraint_object_id = fk.[object_id]
FOR XML path('')
), 1, 1, '') AS [parent_col_csv]
,QUOTENAME(schRef.name) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
,STUFF((
SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
FROM sys.foreign_key_columns AS fcR
WHERE fcR.constraint_object_id = fk.[object_id]
FOR XML path('')
), 1, 1, '') AS [ref_col_csv]
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk ON fk.[object_id] = fkc.constraint_object_id
INNER JOIN sys.objects AS oParent ON oParent.[object_id] = fkc.parent_object_id
INNER JOIN sys.schemas AS schParent ON schParent.[schema_id] = oParent.[schema_id]
INNER JOIN sys.objects AS oRef ON oRef.[object_id] = fkc.referenced_object_id
INNER JOIN sys.schemas AS schRef ON schRef.[schema_id] = oRef.[schema_id]
GROUP BY fkc.parent_object_id
,fkc.referenced_object_id
,fk.name
,fk.[object_id]
,schParent.name
,schRef.name
)
insert into @sql_commands (
ObjectSchema
,ObjectTableName
,ObjectName
,drop_command
,create_command
)
select
CP.unquoted_parent_schema -- schema
,CP.unquoted_parent_table_name -- table name
,CP.unquoted_constraint_name -- fk name
,'alter table ' + CP.parent_obj + ' drop constraint ' + CP.const_name as drop_command
,'alter table ' + CP.parent_obj + ' add constraint ' + CP.const_name + ' foreign key (' + CP.parent_col_csv + ') references ' + CP.ref_obj + ' (' + CP.ref_col_csv + ')' as create_command
from command_parts as CP
where CP.unquoted_parent_schema = @p_schema
and CP.unquoted_constraint_name = @p_fk_name
return
end
go
/*
The SQL command below generates DROP and CREATE commands for all PKs, FKs, and
indexes in dependency order below your specified TABLE_NAME.
It does not execute the DROP or CREATE commands; it only generates them.
I used it to change datetime2 datatypes to datetime on a database with 11 levels of dependencies.
It worked pretty well for me, but I still suggest you test it on a backup of your target database first.
You'll need to run the SELECT command twice: once to generate DROP commands, and once to generate CREATE commands.
I recommend generating/copying/pasting DROP commands, enter your dependency change
("alter table TABLE_NAME alter column DATATYPE not null"),
generating/copying/pasting CREATE commands, saving the file, all before running the script.
*/
declare @create_or_drop varchar(6) = 'drop' -- set to 'create' or 'drop'
declare @table_name varchar(250) = 'your_table_name' -- set to table name
if @create_or_drop = 'create'
begin
select
-- create_command
'-- ' + ID.ObjectPath + '
print ''' + cast(ID.iteration as varchar(5)) + ' / ' + replace(coalesce(GCD.create_command, GFK.create_command), '''', '''''') + '''
go
' + coalesce(GCD.create_command, GFK.create_command) + '
go
'
as create_command
FROM dbo.deps_it_depends('dbo', @table_name, 1) as ID
outer apply dbo.deps_generate_create_and_drop_index(ID.ObjectSchema, ID.ObjectName) as GCD
outer apply dbo.deps_generate_create_and_drop_fk(ID.ObjectSchema, ID.ObjectName) as GFK
where @create_or_drop = 'create'
and (
(GCD.ObjectSchema = ID.ObjectSchema and GCD.ObjectName = ID.ObjectName)
or (GFK.ObjectSchema = ID.ObjectSchema and GFK.ObjectName = ID.ObjectName)
)
ORDER BY ID.iteration desc -- asc: drop order; desc: create order
,case
when ID.ObjectType = 'NCI' then 1 -- non-clustered index
when ID.ObjectType = 'CI' then 2 -- clustered index
when ID.ObjectType = 'F' then 3 -- fk
end desc -- asc: drop order; desc: create order
,ID.ObjectPath
end
if @create_or_drop = 'drop'
begin
select
-- drop_command
'-- ' + ID.ObjectPath + '
print ''' + cast(ID.iteration as varchar(5)) + ' / ' + replace(coalesce(GCD.drop_command, GFK.drop_command), '''', '''''') + '''
go
' + coalesce(GCD.drop_command, GFK.drop_command) + '
go
'
as drop_command
FROM dbo.deps_it_depends('dbo', @table_name, 1) as ID
outer apply dbo.deps_generate_create_and_drop_index(ID.ObjectSchema, ID.ObjectName) as GCD
outer apply dbo.deps_generate_create_and_drop_fk(ID.ObjectSchema, ID.ObjectName) as GFK
where @create_or_drop = 'drop' and (
(GCD.ObjectSchema = ID.ObjectSchema and GCD.ObjectName = ID.ObjectName)
or (GFK.ObjectSchema = ID.ObjectSchema and GFK.ObjectName = ID.ObjectName)
)
ORDER BY ID.iteration asc -- asc: drop order; desc: create order
,case
when ID.ObjectType = 'NCI' then 1 -- non-clustered index
when ID.ObjectType = 'CI' then 2 -- clustered index
when ID.ObjectType = 'F' then 3 -- fk
end asc -- asc: drop order; desc: create order
,ID.ObjectPath
end