CREATE TABLE #tempdep
(
objid INT NOT NULL ,
objname sysname NOT NULL ,
objschema sysname NULL ,
objdb sysname NOT NULL ,
objtype SMALLINT NOT NULL
);
EXEC sp_executesql N'INSERT INTO #tempdep
SELECT
sp.object_id AS [ID],
sp.name AS [Name],
SCHEMA_NAME(sp.schema_id) AS [Schema],
db_name(),
4
FROM
sys.all_objects AS sp
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(sp.name=@_msparam_3 and SCHEMA_NAME(sp.schema_id)=@_msparam_4)',
N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',
@_msparam_0 = N'P', @_msparam_1 = N'RF', @_msparam_2 = N'PC',
@_msparam_3 = N'sp_GetValidEnterprisePositioCountnByActionField',
@_msparam_4 = N'dbo';
DECLARE @find_referencing_objects INT;
SET @find_referencing_objects = 0;
-- parameters:
-- 1. create table #tempdep (objid int NOT NULL, objtype smallint NOT NULL)
-- contains source objects
-- 2. @find_referencing_objects defines ordering
-- 1 order for drop
-- 0 order for script
DECLARE @must_set_nocount_off BIT;
SET @must_set_nocount_off = 0;
IF @@OPTIONS & 512 = 0
SET @must_set_nocount_off = 1;
SET NOCOUNT ON;
DECLARE @u INT;
DECLARE @udf INT;
DECLARE @v INT;
DECLARE @sp INT;
DECLARE @def INT;
DECLARE @rule INT;
DECLARE @tr INT;
DECLARE @uda INT;
DECLARE @uddt INT;
DECLARE @xml INT;
DECLARE @udt INT;
DECLARE @assm INT;
DECLARE @part_sch INT;
DECLARE @part_func INT;
DECLARE @synonym INT;
DECLARE @sequence INT;
DECLARE @udtt INT;
DECLARE @ddltr INT;
DECLARE @unknown INT;
DECLARE @pg INT;
SET @u = 3;
SET @udf = 0;
SET @v = 2;
SET @sp = 4;
SET @def = 6;
SET @rule = 7;
SET @tr = 8;
SET @uda = 11;
SET @synonym = 12;
SET @sequence = 13;
--above 100 -> not in sys.objects
SET @uddt = 101;
SET @xml = 102;
SET @udt = 103;
SET @assm = 1000;
SET @part_sch = 201;
SET @part_func = 202;
SET @udtt = 104;
SET @ddltr = 203;
SET @unknown = 1001;
SET @pg = 204;
-- variables for referenced type obtained from sys.sql_expression_dependencies
DECLARE @obj INT;
SET @obj = 20;
DECLARE @type INT;
SET @type = 21;
-- variables for xml and part_func are already there
CREATE TABLE #t1
(
object_id INT NULL ,
object_name sysname COLLATE DATABASE_DEFAULT
NULL ,
object_schema sysname COLLATE DATABASE_DEFAULT
NULL ,
object_db sysname NULL ,
object_svr sysname NULL ,
object_type SMALLINT NOT NULL ,
relative_id INT NOT NULL ,
relative_name sysname COLLATE DATABASE_DEFAULT
NOT NULL ,
relative_schema sysname COLLATE DATABASE_DEFAULT
NULL ,
relative_db sysname NULL ,
relative_svr sysname NULL ,
relative_type SMALLINT NOT NULL ,
schema_bound BIT NOT NULL ,
rank SMALLINT NULL ,
degree INT NULL
);
-- we need to create another temporary table to store the dependencies from sys.sql_expression_dependencies till the updated values are inserted finally into #t1
CREATE TABLE #t2
(
object_id INT NULL ,
object_name sysname COLLATE DATABASE_DEFAULT
NULL ,
object_schema sysname COLLATE DATABASE_DEFAULT
NULL ,
object_db sysname NULL ,
object_svr sysname NULL ,
object_type SMALLINT NOT NULL ,
relative_id INT NOT NULL ,
relative_name sysname COLLATE DATABASE_DEFAULT
NOT NULL ,
relative_schema sysname COLLATE DATABASE_DEFAULT
NULL ,
relative_db sysname NULL ,
relative_svr sysname NULL ,
relative_type SMALLINT NOT NULL ,
schema_bound BIT NOT NULL ,
rank SMALLINT NULL
);
-- This index will ensure that we have unique parent-child relationship
CREATE UNIQUE CLUSTERED INDEX i1 ON #t1(object_name, object_schema, object_db, object_svr, object_type, relative_name, relative_schema, relative_type) WITH IGNORE_DUP_KEY;
DECLARE @iter_no INT;
SET @iter_no = 1;
DECLARE @rows INT;
SET @rows = 1;
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT l.objid ,
l.objname ,
l.objschema ,
l.objdb ,
l.objtype ,
l.objid ,
l.objname ,
l.objschema ,
l.objdb ,
l.objtype ,
1 ,
@iter_no
FROM #tempdep l;
-- change the object_id of table types to their user_defined_id
UPDATE #t1
SET object_id = tt.user_type_id ,
relative_id = tt.user_type_id
FROM sys.table_types AS tt
WHERE tt.type_table_object_id = #t1.object_id
AND object_type = @udtt;
WHILE @rows > 0
BEGIN
SET @rows = 0;
IF ( 1 = @find_referencing_objects )
BEGIN
-- HARD DEPENDENCIES
-- these dependencies have to be in the same database only
-- tables that reference uddts or udts
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT tbl.object_id ,
tbl.name ,
SCHEMA_NAME(tbl.schema_id) ,
t.object_db ,
@u ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.columns AS c ON c.user_type_id = t.object_id
JOIN sys.tables AS tbl ON tbl.object_id = c.object_id
WHERE @iter_no = t.rank
AND ( t.object_type = @uddt
OR t.object_type = @udt
)
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- udtts that reference uddts or udts
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT tt.user_type_id ,
tt.name ,
SCHEMA_NAME(tt.schema_id) ,
t.object_db ,
@udtt ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.columns AS c ON c.user_type_id = t.object_id
JOIN sys.table_types AS tt ON tt.type_table_object_id = c.object_id
WHERE @iter_no = t.rank
AND ( t.object_type = @uddt
OR t.object_type = @udt
)
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- tables/views that reference triggers
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT o.object_id ,
o.name ,
SCHEMA_NAME(o.schema_id) ,
t.object_db ,
@tr ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.objects AS o ON o.parent_object_id = t.object_id
AND o.type = 'TR'
WHERE @iter_no = t.rank
AND ( t.object_type = @u
OR t.object_type = @v
)
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- tables that reference defaults (only default objects)
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT o.object_id ,
o.name ,
SCHEMA_NAME(o.schema_id) ,
t.object_db ,
@u ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.columns AS clmns ON clmns.default_object_id = t.object_id
JOIN sys.objects AS o ON o.object_id = clmns.object_id
AND 0 = ISNULL(o.parent_object_id,
0)
WHERE @iter_no = t.rank
AND t.object_type = @def
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- types that reference defaults (only default objects)
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT tp.user_type_id ,
tp.name ,
SCHEMA_NAME(tp.schema_id) ,
t.object_db ,
@uddt ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.types AS tp ON tp.default_object_id = t.object_id
JOIN sys.objects AS o ON o.object_id = t.object_id
AND 0 = ISNULL(o.parent_object_id,
0)
WHERE @iter_no = t.rank
AND t.object_type = @def
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- tables that reference rules
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT tbl.object_id ,
tbl.name ,
SCHEMA_NAME(tbl.schema_id) ,
t.object_db ,
@u ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.columns AS clmns ON clmns.rule_object_id = t.object_id
JOIN sys.tables AS tbl ON tbl.object_id = clmns.object_id
WHERE @iter_no = t.rank
AND t.relative_type = @rule
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- types that reference rules
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT tp.user_type_id ,
tp.name ,
SCHEMA_NAME(tp.schema_id) ,
t.object_db ,
@uddt ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.types AS tp ON tp.rule_object_id = t.object_id
WHERE @iter_no = t.rank
AND t.object_type = @rule
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- tables that reference XmlSchemaCollections
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT tbl.object_id ,
tbl.name ,
SCHEMA_NAME(tbl.schema_id) ,
t.object_db ,
@u ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.columns AS c ON c.xml_collection_id = t.object_id
JOIN sys.tables AS tbl ON tbl.object_id = c.object_id -- eliminate views
WHERE @iter_no = t.rank
AND t.object_type = @xml
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- table types that reference XmlSchemaCollections
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT tt.user_type_id ,
tt.name ,
SCHEMA_NAME(tt.schema_id) ,
t.object_db ,
@udtt ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.columns AS c ON c.xml_collection_id = t.object_id
JOIN sys.table_types AS tt ON tt.type_table_object_id = c.object_id
WHERE @iter_no = t.rank
AND t.object_type = @xml
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- procedures that reference XmlSchemaCollections
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT o.object_id ,
o.name ,
SCHEMA_NAME(o.schema_id) ,
t.object_db ,
( CASE WHEN o.type IN ( 'P', 'RF', 'PC' )
THEN @sp
ELSE @udf
END ) ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.parameters AS c ON c.xml_collection_id = t.object_id
JOIN sys.objects AS o ON o.object_id = c.object_id
WHERE @iter_no = t.rank
AND t.object_type = @xml
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- udf, sp, uda, trigger all that reference assembly
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT o.object_id ,
o.name ,
SCHEMA_NAME(o.schema_id) ,
t.object_db ,
( CASE o.type
WHEN 'AF' THEN @uda
WHEN 'PC' THEN @sp
WHEN 'FS' THEN @udf
WHEN 'FT' THEN @udf
WHEN 'TA' THEN @tr
ELSE @udf
END ) ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.assembly_modules AS am ON ( ( am.assembly_id = t.object_id )
AND ( am.assembly_id >= 65536 )
)
JOIN sys.objects AS o ON am.object_id = o.object_id
WHERE @iter_no = t.rank
AND t.object_type = @assm
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- udt that reference assembly
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT at.user_type_id ,
at.name ,
SCHEMA_NAME(at.schema_id) ,
t.object_db ,
@udt ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.assembly_types AS at ON ( ( at.assembly_id = t.object_id )
AND ( at.is_user_defined = 1 )
)
WHERE @iter_no = t.rank
AND t.object_type = @assm
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- assembly that reference assembly
INSERT #t1
( object_id ,
object_name ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT asm.assembly_id ,
asm.name ,
t.object_db ,
@assm ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.assembly_references AS ar ON ( ( ar.referenced_assembly_id = t.object_id )
AND ( ar.referenced_assembly_id >= 65536 )
)
JOIN sys.assemblies AS asm ON asm.assembly_id = ar.assembly_id
WHERE @iter_no = t.rank
AND t.object_type = @assm
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- table references table
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT tbl.object_id ,
tbl.name ,
SCHEMA_NAME(tbl.schema_id) ,
t.object_db ,
@u ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.foreign_keys AS fk ON fk.referenced_object_id = t.object_id
JOIN sys.tables AS tbl ON tbl.object_id = fk.parent_object_id
WHERE @iter_no = t.rank
AND t.object_type = @u
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- uda references types
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT o.object_id ,
o.name ,
SCHEMA_NAME(o.schema_id) ,
t.object_db ,
@uda ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.parameters AS p ON p.user_type_id = t.object_id
JOIN sys.objects AS o ON o.object_id = p.object_id
AND o.type = 'AF'
WHERE @iter_no = t.rank
AND t.object_type IN ( @udt, @uddt, @udtt )
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
-- table,view references partition scheme
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT o.object_id ,
o.name ,
SCHEMA_NAME(o.schema_id) ,
t.object_db ,
( CASE o.type
WHEN 'V' THEN @v
ELSE @u
END ) ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.indexes AS idx ON idx.data_space_id = t.object_id
JOIN sys.objects AS o ON o.object_id = idx.object_id
WHERE @iter_no = t.rank
AND t.object_type = @part_sch
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- partition scheme references partition function
INSERT #t1
( object_id ,
object_name ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT ps.data_space_id ,
ps.name ,
t.object_db ,
@part_sch ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.partition_schemes AS ps ON ps.function_id = t.object_id
WHERE @iter_no = t.rank
AND t.object_type = @part_func
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- plan guide references sp, udf, triggers
INSERT #t1
( object_id ,
object_name ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT pg.plan_guide_id ,
pg.name ,
t.object_db ,
@pg ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.plan_guides AS pg ON pg.scope_object_id = t.object_id
WHERE @iter_no = t.rank
AND t.object_type IN ( @sp, @udf, @tr )
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- synonym refrences object
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT s.object_id ,
s.name ,
SCHEMA_NAME(s.schema_id) ,
t.object_db ,
@synonym ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
0 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.synonyms AS s ON OBJECT_ID(s.base_object_name) = t.object_id
WHERE @iter_no = t.rank
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- sequences that reference uddts
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT s.object_id ,
s.name ,
SCHEMA_NAME(s.schema_id) ,
t.object_db ,
@sequence ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
0 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.sequences AS s ON s.user_type_id = t.object_id
WHERE @iter_no = t.rank
AND ( t.object_type = @uddt )
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- SOFT DEPENDENCIES
DECLARE name_cursor CURSOR
FOR
SELECT DISTINCT
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_type
FROM #t1 AS t
WHERE @iter_no = t.rank
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
)
AND t.object_type NOT IN ( @part_sch, @assm, @tr,
@ddltr );
OPEN name_cursor;
DECLARE @objid INT;
DECLARE @objname sysname;
DECLARE @objschema sysname;
DECLARE @objtype SMALLINT;
DECLARE @fullname sysname;
DECLARE @objecttype sysname;
FETCH NEXT FROM name_cursor INTO @objid, @objname, @objschema,
@objtype;
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
SET @fullname = CASE WHEN @objschema IS NULL
THEN QUOTENAME(@objname)
ELSE QUOTENAME(@objschema) + '.'
+ QUOTENAME(@objname)
END;
SET @objecttype = CASE WHEN @objtype IN ( @uddt, @udt,
@udtt )
THEN 'TYPE'
WHEN @objtype = @xml
THEN 'XML_SCHEMA_COLLECTION'
WHEN @objtype = @part_func
THEN 'PARTITION_FUNCTION'
ELSE 'OBJECT'
END;
INSERT #t2
( object_type ,
object_id ,
object_name ,
object_schema ,
object_db ,
object_svr ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT CASE dep.referencing_class
WHEN 1
THEN ( SELECT CASE WHEN obj.type = 'U'
THEN @u
WHEN obj.type = 'V'
THEN @v
WHEN obj.type = 'TR'
THEN @tr
WHEN obj.type IN (
'P', 'RF', 'PC' )
THEN @sp
WHEN obj.type IN (
'AF' ) THEN @uda
WHEN obj.type IN (
'TF', 'FN', 'IF',
'FS', 'FT' )
THEN @udf
WHEN obj.type = 'D'
THEN @def
WHEN obj.type = 'SN'
THEN @synonym
WHEN obj.type = 'SO'
THEN @sequence
ELSE @obj
END
FROM sys.objects AS obj
WHERE obj.object_id = dep.referencing_id
)
WHEN 6
THEN ( SELECT CASE WHEN ( tp.is_assembly_type = 1 )
THEN @udt
WHEN ( tp.is_table_type = 1 )
THEN @udtt
ELSE @uddt
END
FROM sys.types AS tp
WHERE tp.user_type_id = dep.referencing_id
)
WHEN 7 THEN @u
WHEN 9 THEN @u
WHEN 10 THEN @xml
WHEN 12 THEN @ddltr
WHEN 21 THEN @part_func
END ,
dep.referencing_id ,
dep.referencing_entity_name ,
dep.referencing_schema_name ,
DB_NAME() ,
NULL ,
@objid ,
@objname ,
@objschema ,
DB_NAME() ,
@objtype ,
0 ,
@iter_no + 1
FROM sys.dm_sql_referencing_entities(@fullname,
@objecttype) dep;
FETCH NEXT FROM name_cursor INTO @objid, @objname,
@objschema, @objtype;
END;
CLOSE name_cursor;
DEALLOCATE name_cursor;
UPDATE #t2
SET object_id = obj.object_id ,
object_name = obj.name ,
object_schema = SCHEMA_NAME(obj.schema_id) ,
object_type = CASE WHEN obj.type = 'U' THEN @u
WHEN obj.type = 'V' THEN @v
END
FROM sys.objects AS o
JOIN sys.objects AS obj ON obj.object_id = o.parent_object_id
WHERE o.object_id = #t2.object_id
AND ( #t2.object_type = @obj
OR o.parent_object_id != 0
)
AND #t2.rank = @iter_no + 1;
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_svr ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_svr ,
relative_type ,
schema_bound ,
rank
)
SELECT object_id ,
object_name ,
object_schema ,
object_db ,
object_svr ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_svr ,
relative_type ,
schema_bound ,
rank
FROM #t2
WHERE @iter_no + 1 = rank
AND #t2.object_id != #t2.relative_id;
SET @rows = @rows + @@rowcount;
END;
ELSE
BEGIN
-- SOFT DEPENDENCIES
-- insert all values from sys.sql_expression_dependencies for the corresponding object
-- first insert them in #t2, update them and then finally insert them in #t1
INSERT #t2
( object_type ,
object_name ,
object_schema ,
object_db ,
object_svr ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT CASE dep.referenced_class
WHEN 1 THEN @obj
WHEN 6 THEN @type
WHEN 7 THEN @u
WHEN 9 THEN @u
WHEN 10 THEN @xml
WHEN 21 THEN @part_func
END ,
dep.referenced_entity_name ,
dep.referenced_schema_name ,
dep.referenced_database_name ,
dep.referenced_server_name ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
dep.is_schema_bound_reference ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.sql_expression_dependencies AS dep ON dep.referencing_id = t.object_id
WHERE @iter_no = t.rank
AND t.object_svr IS NULL
AND t.object_db = DB_NAME();
-- insert all the dependency values in case of a table that references a check
INSERT #t2
( object_type ,
object_name ,
object_schema ,
object_db ,
object_svr ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT CASE dep.referenced_class
WHEN 1 THEN @obj
WHEN 6 THEN @type
WHEN 7 THEN @u
WHEN 9 THEN @u
WHEN 10 THEN @xml
WHEN 21 THEN @part_func
END ,
dep.referenced_entity_name ,
dep.referenced_schema_name ,
dep.referenced_database_name ,
dep.referenced_server_name ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
dep.is_schema_bound_reference ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.sql_expression_dependencies AS d ON d.referenced_id = t.object_id
JOIN sys.objects AS o ON o.object_id = d.referencing_id
AND o.type = 'C'
JOIN sys.sql_expression_dependencies AS dep ON dep.referencing_id = d.referencing_id
AND dep.referenced_id != t.object_id
WHERE @iter_no = t.rank
AND t.object_svr IS NULL
AND t.object_db = DB_NAME()
AND t.object_type = @u;
-- insert all the dependency values in case of an object that belongs to another object whose dependencies are being found
INSERT #t2
( object_type ,
object_name ,
object_schema ,
object_db ,
object_svr ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT CASE dep.referenced_class
WHEN 1 THEN @obj
WHEN 6 THEN @type
WHEN 7 THEN @u
WHEN 9 THEN @u
WHEN 10 THEN @xml
WHEN 21 THEN @part_func
END ,
dep.referenced_entity_name ,
dep.referenced_schema_name ,
dep.referenced_database_name ,
dep.referenced_server_name ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
dep.is_schema_bound_reference ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.objects AS o ON o.parent_object_id = t.object_id
JOIN sys.sql_expression_dependencies AS dep ON dep.referencing_id = o.object_id
WHERE @iter_no = t.rank
AND t.object_svr IS NULL
AND t.object_db = DB_NAME();
-- queries for objects with object_id null and object_svr null - resolve them
-- we will build the query to resolve the objects
-- increase @rows as we bind the objects
DECLARE db_cursor CURSOR
FOR
SELECT DISTINCT
ISNULL(object_db, DB_NAME())
FROM #t2 AS t
WHERE t.rank = ( @iter_no + 1 )
AND t.object_id IS NULL
AND t.object_svr IS NULL;
OPEN db_cursor;
DECLARE @dbname sysname;
FETCH NEXT FROM db_cursor INTO @dbname;
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
IF ( DB_ID(@dbname) IS NULL )
BEGIN
FETCH NEXT FROM db_cursor INTO @dbname;
CONTINUE;
END;
DECLARE @query NVARCHAR(MAX);
-- when schema is not null
-- @obj
SET @query = 'update #t2 set object_db = N'
+ QUOTENAME(@dbname, '''')
+ ', object_id = obj.object_id, object_type =
case when obj.type = ''U'' then '
+ CAST(@u AS NVARCHAR(8))
+ ' when obj.type = ''V'' then '
+ CAST(@v AS NVARCHAR(8))
+ ' when obj.type = ''TR'' then '
+ CAST(@tr AS NVARCHAR(8))
+ ' when obj.type in ( ''P'', ''RF'', ''PC'' ) then '
+ CAST(@sp AS NVARCHAR(8))
+ ' when obj.type in ( ''AF'' ) then '
+ CAST(@uda AS NVARCHAR(8))
+ ' when obj.type in ( ''TF'', ''FN'', ''IF'', ''FS'', ''FT'' ) then '
+ CAST(@udf AS NVARCHAR(8))
+ ' when obj.type = ''D'' then '
+ CAST(@def AS NVARCHAR(8))
+ ' when obj.type = ''SN'' then '
+ CAST(@synonym AS NVARCHAR(8))
+ ' when obj.type = ''SO'' then '
+ CAST(@sequence AS NVARCHAR(8)) + ' else '
+ CAST(@unknown AS NVARCHAR(8)) + ' end
from ' + QUOTENAME(@dbname) + '.sys.objects as obj
join ' + QUOTENAME(@dbname)
+ '.sys.schemas as sch on sch.schema_id = obj.schema_id
where obj.name = #t2.object_name collate database_default
and sch.name = #t2.object_schema collate database_default
and #t2.object_type = ' + CAST(@obj AS NVARCHAR(8))
+ ' and #t2.object_schema IS NOT NULL
and (#t2.object_db IS NULL or #t2.object_db = '''
+ @dbname + ''')
and #t2.rank = (' + CAST(@iter_no AS NVARCHAR(8))
+ '+1) and #t2.object_id IS NULL and #t2.object_svr IS NULL';
EXEC (@query);
-- @type
SET @query = 'update #t2 set object_db = N'
+ QUOTENAME(@dbname, '''')
+ ', object_id = t.user_type_id, object_type = case when t.is_assembly_type = 1 then '
+ CAST(@udt AS NVARCHAR(8))
+ ' when t.is_table_type = 1 then '
+ CAST(@udtt AS NVARCHAR(8)) + ' else '
+ CAST(@uddt AS NVARCHAR(8)) + ' end
from ' + QUOTENAME(@dbname) + '.sys.types as t
join ' + QUOTENAME(@dbname)
+ '.sys.schemas as sch on sch.schema_id = t.schema_id
where t.name = #t2.object_name collate database_default
and sch.name = #t2.object_schema collate database_default
and #t2.object_type = ' + CAST(@type AS NVARCHAR(8))
+ ' and #t2.object_schema IS NOT NULL
and (#t2.object_db IS NULL or #t2.object_db = '''
+ @dbname + ''')
and #t2.rank = (' + CAST(@iter_no AS NVARCHAR(8))
+ '+1) and #t2.object_id IS NULL and #t2.object_svr IS NULL';
EXEC (@query);
-- @xml
SET @query = 'update #t2 set object_db = N'
+ QUOTENAME(@dbname, '''')
+ ', object_id = x.xml_collection_id
from ' + QUOTENAME(@dbname)
+ '.sys.xml_schema_collections as x
join ' + QUOTENAME(@dbname)
+ '.sys.schemas as sch on sch.schema_id = x.schema_id
where x.name = #t2.object_name collate database_default
and sch.name = #t2.object_schema collate database_default
and #t2.object_type = ' + CAST(@xml AS NVARCHAR(8))
+ ' and #t2.object_schema IS NOT NULL
and (#t2.object_db IS NULL or #t2.object_db = '''
+ @dbname + ''')
and #t2.rank = (' + CAST(@iter_no AS NVARCHAR(8))
+ '+1) and #t2.object_id IS NULL and #t2.object_svr IS NULL';
EXEC (@query);
-- @part_func - schema is always null
-- @schema is null
-- consider schema as 'dbo'
-- @obj
SET @query = 'update #t2 set object_db = N'
+ QUOTENAME(@dbname, '''')
+ ', object_id = obj.object_id, object_schema = SCHEMA_NAME(obj.schema_id), object_type =
case when obj.type = ''U'' then '
+ CAST(@u AS NVARCHAR(8))
+ ' when obj.type = ''V'' then '
+ CAST(@v AS NVARCHAR(8))
+ ' when obj.type = ''TR'' then '
+ CAST(@tr AS NVARCHAR(8))
+ ' when obj.type in ( ''P'', ''RF'', ''PC'' ) then '
+ CAST(@sp AS NVARCHAR(8))
+ ' when obj.type in ( ''AF'' ) then '
+ CAST(@uda AS NVARCHAR(8))
+ ' when obj.type in ( ''TF'', ''FN'', ''IF'', ''FS'', ''FT'' ) then '
+ CAST(@udf AS NVARCHAR(8))
+ ' when obj.type = ''D'' then '
+ CAST(@def AS NVARCHAR(8))
+ ' when obj.type = ''SN'' then '
+ CAST(@synonym AS NVARCHAR(8))
+ ' when obj.type = ''SO'' then '
+ CAST(@sequence AS NVARCHAR(8)) + ' else '
+ CAST(@unknown AS NVARCHAR(8)) + ' end
from ' + QUOTENAME(@dbname) + '.sys.objects as obj
where obj.name = #t2.object_name collate database_default
and SCHEMA_NAME(obj.schema_id) = ''dbo''
and #t2.object_type = ' + CAST(@obj AS NVARCHAR(8))
+ ' and #t2.object_schema IS NULL
and (#t2.object_db IS NULL or #t2.object_db = '''
+ @dbname + ''')
and #t2.rank = (' + CAST(@iter_no AS NVARCHAR(8))
+ '+1) and #t2.object_id IS NULL and #t2.object_svr IS NULL';
EXEC (@query);
-- @type
SET @query = 'update #t2 set object_db = N'
+ QUOTENAME(@dbname, '''')
+ ', object_id = t.user_type_id, object_schema = SCHEMA_NAME(t.schema_id), object_type = case when t.is_assembly_type = 1 then '
+ CAST(@udt AS NVARCHAR(8))
+ ' when t.is_table_type = 1 then '
+ CAST(@udtt AS NVARCHAR(8)) + ' else '
+ CAST(@uddt AS NVARCHAR(8)) + ' end
from ' + QUOTENAME(@dbname) + '.sys.types as t
where t.name = #t2.object_name collate database_default
and SCHEMA_NAME(t.schema_id) = ''dbo''
and #t2.object_type = ' + CAST(@type AS NVARCHAR(8))
+ ' and #t2.object_schema IS NULL
and (#t2.object_db IS NULL or #t2.object_db = '''
+ @dbname + ''')
and #t2.rank = (' + CAST(@iter_no AS NVARCHAR(8))
+ '+1) and #t2.object_id IS NULL and #t2.object_svr IS NULL';
EXEC (@query);
-- @xml
SET @query = 'update #t2 set object_db = N'
+ QUOTENAME(@dbname, '''')
+ ', object_id = x.xml_collection_id, object_schema = SCHEMA_NAME(x.schema_id)
from ' + QUOTENAME(@dbname)
+ '.sys.xml_schema_collections as x
where x.name = #t2.object_name collate database_default
and SCHEMA_NAME(x.schema_id) = ''dbo''
and #t2.object_type = ' + CAST(@xml AS NVARCHAR(8))
+ ' and #t2.object_schema IS NULL
and (#t2.object_db IS NULL or #t2.object_db = '''
+ @dbname + ''')
and #t2.rank = (' + CAST(@iter_no AS NVARCHAR(8))
+ '+1) and #t2.object_id IS NULL and #t2.object_svr IS NULL';
EXEC (@query);
-- consider schema as t.relative_schema
-- the parent object will have the default schema of user in case of dynamic schema binding
-- @obj
SET @query = 'update #t2 set object_db = N'
+ QUOTENAME(@dbname, '''')
+ ', object_id = obj.object_id, object_schema = SCHEMA_NAME(obj.schema_id), object_type =
case when obj.type = ''U'' then '
+ CAST(@u AS NVARCHAR(8))
+ ' when obj.type = ''V'' then '
+ CAST(@v AS NVARCHAR(8))
+ ' when obj.type = ''TR'' then '
+ CAST(@tr AS NVARCHAR(8))
+ ' when obj.type in ( ''P'', ''RF'', ''PC'' ) then '
+ CAST(@sp AS NVARCHAR(8))
+ ' when obj.type in ( ''AF'' ) then '
+ CAST(@uda AS NVARCHAR(8))
+ ' when obj.type in ( ''TF'', ''FN'', ''IF'', ''FS'', ''FT'' ) then '
+ CAST(@udf AS NVARCHAR(8))
+ ' when obj.type = ''D'' then '
+ CAST(@def AS NVARCHAR(8))
+ ' when obj.type = ''SN'' then '
+ CAST(@synonym AS NVARCHAR(8))
+ ' when obj.type = ''SO'' then '
+ CAST(@sequence AS NVARCHAR(8)) + ' else '
+ CAST(@unknown AS NVARCHAR(8)) + ' end
from ' + QUOTENAME(@dbname) + '.sys.objects as obj
join ' + QUOTENAME(@dbname)
+ '.sys.schemas as sch on sch.schema_id = obj.schema_id
where obj.name = #t2.object_name collate database_default
and sch.name = #t2.relative_schema collate database_default
and #t2.object_type = ' + CAST(@obj AS NVARCHAR(8))
+ ' and #t2.object_schema IS NULL
and (#t2.object_db IS NULL or #t2.object_db = '''
+ @dbname + ''')
and #t2.rank = (' + CAST(@iter_no AS NVARCHAR(8))
+ '+1) and #t2.object_id IS NULL and #t2.object_svr IS NULL';
EXEC (@query);
-- @type
SET @query = 'update #t2 set object_db = N'
+ QUOTENAME(@dbname, '''')
+ ', object_id = t.user_type_id, object_schema = SCHEMA_NAME(t.schema_id), object_type = case when t.is_assembly_type = 1 then '
+ CAST(@udt AS NVARCHAR(8))
+ ' when t.is_table_type = 1 then '
+ CAST(@udtt AS NVARCHAR(8)) + ' else '
+ CAST(@uddt AS NVARCHAR(8)) + ' end
from ' + QUOTENAME(@dbname) + '.sys.types as t
join ' + QUOTENAME(@dbname)
+ '.sys.schemas as sch on sch.schema_id = t.schema_id
where t.name = #t2.object_name collate database_default
and sch.name = #t2.relative_schema collate database_default
and #t2.object_type = ' + CAST(@type AS NVARCHAR(8))
+ ' and #t2.object_schema IS NULL
and (#t2.object_db IS NULL or #t2.object_db = '''
+ @dbname + ''')
and #t2.rank = (' + CAST(@iter_no AS NVARCHAR(8))
+ '+1) and #t2.object_id IS NULL and #t2.object_svr IS NULL';
EXEC (@query);
-- @xml
SET @query = 'update #t2 set object_db = N'
+ QUOTENAME(@dbname, '''')
+ ', object_id = x.xml_collection_id, object_schema = SCHEMA_NAME(x.schema_id)
from ' + QUOTENAME(@dbname)
+ '.sys.xml_schema_collections as x
join ' + QUOTENAME(@dbname)
+ '.sys.schemas as sch on sch.schema_id = x.schema_id
where x.name = #t2.object_name collate database_default
and sch.name = #t2.relative_schema collate database_default
and #t2.object_type = ' + CAST(@xml AS NVARCHAR(8))
+ ' and #t2.object_schema IS NULL
and (#t2.object_db IS NULL or #t2.object_db = '''
+ @dbname + ''')
and #t2.rank = (' + CAST(@iter_no AS NVARCHAR(8))
+ '+1) and #t2.object_id IS NULL and #t2.object_svr IS NULL';
EXEC (@query);
-- @part_func always have schema as null
SET @query = 'update #t2 set object_db = N'
+ QUOTENAME(@dbname, '''')
+ ', object_id = p.function_id
from ' + QUOTENAME(@dbname)
+ '.sys.partition_functions as p
where p.name = #t2.object_name collate database_default
and #t2.object_type = ' + CAST(@part_func AS NVARCHAR(8))
+ ' and (#t2.object_db IS NULL or #t2.object_db = '''
+ @dbname + ''')
and #t2.rank = (' + CAST(@iter_no AS NVARCHAR(8))
+ '+1) and #t2.object_id IS NULL and #t2.object_svr IS NULL';
EXEC (@query);
-- update the shared object if any (schema is not null)
UPDATE #t2
SET object_db = 'master' ,
object_id = o.object_id ,
object_type = @sp
FROM master.sys.objects AS o
JOIN master.sys.schemas AS sch ON sch.schema_id = o.schema_id
WHERE o.name = #t2.object_name COLLATE DATABASE_DEFAULT
AND sch.name = #t2.object_schema COLLATE DATABASE_DEFAULT
AND o.type IN ( 'P', 'RF', 'PC' )
AND #t2.object_id IS NULL
AND #t2.object_name LIKE 'sp/_%' ESCAPE '/'
AND #t2.object_db IS NULL
AND #t2.object_svr IS NULL;
-- update the shared object if any (schema is null)
UPDATE #t2
SET object_db = 'master' ,
object_id = o.object_id ,
object_schema = SCHEMA_NAME(o.schema_id) ,
object_type = @sp
FROM master.sys.objects AS o
WHERE o.name = #t2.object_name COLLATE DATABASE_DEFAULT
AND SCHEMA_NAME(o.schema_id) = 'dbo' COLLATE DATABASE_DEFAULT
AND o.type IN ( 'P', 'RF', 'PC' )
AND #t2.object_schema IS NULL
AND #t2.object_id IS NULL
AND #t2.object_name LIKE 'sp/_%' ESCAPE '/'
AND #t2.object_db IS NULL
AND #t2.object_svr IS NULL;
FETCH NEXT FROM db_cursor INTO @dbname;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
UPDATE #t2
SET object_type = @unknown
WHERE object_id IS NULL;
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_svr ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_svr ,
relative_type ,
schema_bound ,
rank
)
SELECT object_id ,
object_name ,
object_schema ,
object_db ,
object_svr ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_svr ,
relative_type ,
schema_bound ,
rank
FROM #t2
WHERE @iter_no + 1 = rank;
SET @rows = @rows + @@rowcount;
-- HARD DEPENDENCIES
-- uddt or udt referenced by table
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT tp.user_type_id ,
tp.name ,
SCHEMA_NAME(tp.schema_id) ,
t.object_db ,
CASE tp.is_assembly_type
WHEN 1 THEN @udt
ELSE @uddt
END ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.columns AS col ON col.object_id = t.object_id
JOIN sys.types AS tp ON tp.user_type_id = col.user_type_id
AND tp.schema_id != 4
WHERE @iter_no = t.rank
AND t.object_type = @u
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- uddt or udt referenced by table type
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT tp.user_type_id ,
tp.name ,
SCHEMA_NAME(tp.schema_id) ,
t.object_db ,
CASE tp.is_assembly_type
WHEN 1 THEN @udt
ELSE @uddt
END ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.table_types AS tt ON tt.user_type_id = t.object_id
JOIN sys.columns AS col ON col.object_id = tt.type_table_object_id
JOIN sys.types AS tp ON tp.user_type_id = col.user_type_id
AND tp.schema_id != 4
WHERE @iter_no = t.rank
AND t.object_type = @udtt
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- table or view referenced by trigger
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT o.object_id ,
o.name ,
SCHEMA_NAME(o.schema_id) ,
t.object_db ,
CASE o.type
WHEN 'V' THEN @v
ELSE @u
END ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.triggers AS tr ON tr.object_id = t.object_id
JOIN sys.objects AS o ON o.object_id = tr.parent_id
WHERE @iter_no = t.rank
AND t.object_type = @tr
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- defaults (only default objects) referenced by tables
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT o.object_id ,
o.name ,
SCHEMA_NAME(o.schema_id) ,
t.object_db ,
@def ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.columns AS clmns ON clmns.object_id = t.object_id
JOIN sys.objects AS o ON o.object_id = clmns.default_object_id
AND 0 = ISNULL(o.parent_object_id,
0)
WHERE @iter_no = t.rank
AND t.object_type = @u
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- defaults (only default objects) referenced by types
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT o.object_id ,
o.name ,
SCHEMA_NAME(o.schema_id) ,
t.object_db ,
@def ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.types AS tp ON tp.user_type_id = t.object_id
JOIN sys.objects AS o ON o.object_id = tp.default_object_id
AND 0 = ISNULL(o.parent_object_id,
0)
WHERE @iter_no = t.rank
AND t.object_type = @uddt
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- rules referenced by tables
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT o.object_id ,
o.name ,
SCHEMA_NAME(o.schema_id) ,
t.object_db ,
@rule ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.columns AS clmns ON clmns.object_id = t.object_id
JOIN sys.objects AS o ON o.object_id = clmns.rule_object_id
AND 0 = ISNULL(o.parent_object_id,
0)
WHERE @iter_no = t.rank
AND t.relative_type = @u
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- rules referenced by types
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT o.object_id ,
o.name ,
SCHEMA_NAME(o.schema_id) ,
t.object_db ,
@rule ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.types AS tp ON tp.user_type_id = t.object_id
JOIN sys.objects AS o ON o.object_id = tp.rule_object_id
AND 0 = ISNULL(o.parent_object_id,
0)
WHERE @iter_no = t.rank
AND t.relative_type = @uddt
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- XmlSchemaCollections referenced by tables
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT x.xml_collection_id ,
x.name ,
SCHEMA_NAME(x.schema_id) ,
t.object_db ,
@xml ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.columns AS c ON c.object_id = t.object_id
JOIN sys.xml_schema_collections AS x ON x.xml_collection_id = c.xml_collection_id
AND x.schema_id != 4
WHERE @iter_no = t.rank
AND t.object_type = @u
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- XmlSchemaCollections referenced by tabletypes
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT x.xml_collection_id ,
x.name ,
SCHEMA_NAME(x.schema_id) ,
t.object_db ,
@xml ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.table_types AS tt ON tt.user_type_id = t.object_id
JOIN sys.columns AS c ON c.object_id = tt.type_table_object_id
JOIN sys.xml_schema_collections AS x ON x.xml_collection_id = c.xml_collection_id
AND x.schema_id != 4
WHERE @iter_no = t.rank
AND t.object_type = @udtt
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- XmlSchemaCollections referenced by procedures
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT x.xml_collection_id ,
x.name ,
SCHEMA_NAME(x.schema_id) ,
t.object_db ,
@xml ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.parameters AS c ON c.object_id = t.object_id
JOIN sys.xml_schema_collections AS x ON x.xml_collection_id = c.xml_collection_id
AND x.schema_id != 4
WHERE @iter_no = t.rank
AND t.object_type IN ( @sp, @udf )
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- table referenced by table
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT tbl.object_id ,
tbl.name ,
SCHEMA_NAME(tbl.schema_id) ,
t.object_db ,
@u ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.foreign_keys AS fk ON fk.parent_object_id = t.object_id
JOIN sys.tables AS tbl ON tbl.object_id = fk.referenced_object_id
WHERE @iter_no = t.rank
AND t.object_type = @u
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- uddts referenced by uda
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT tp.user_type_id ,
tp.name ,
SCHEMA_NAME(tp.schema_id) ,
t.object_db ,
CASE WHEN tp.is_table_type = 1 THEN @udtt
WHEN tp.is_assembly_type = 1 THEN @udt
ELSE @uddt
END ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.parameters AS p ON p.object_id = t.object_id
JOIN sys.types AS tp ON tp.user_type_id = p.user_type_id
WHERE @iter_no = t.rank
AND t.object_type = @uda
AND t.object_type = @uda
AND tp.user_type_id > 256;
SET @rows = @rows + @@rowcount;
-- assembly referenced by assembly
INSERT #t1
( object_id ,
object_name ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT asm.assembly_id ,
asm.name ,
t.object_db ,
@assm ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.assembly_references AS ar ON ( ( ar.assembly_id = t.object_id )
AND ( ar.referenced_assembly_id >= 65536 )
)
JOIN sys.assemblies AS asm ON asm.assembly_id = ar.referenced_assembly_id
WHERE @iter_no = t.rank
AND t.object_type = @assm
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- assembly referenced by udt
INSERT #t1
( object_id ,
object_name ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT asm.assembly_id ,
asm.name ,
t.object_db ,
@assm ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.assembly_types AS at ON ( ( at.user_type_id = t.object_id )
AND ( at.is_user_defined = 1 )
)
JOIN sys.assemblies AS asm ON asm.assembly_id = at.assembly_id
WHERE @iter_no = t.rank
AND t.object_type = @udt
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- assembly referenced by udf, sp, uda, trigger
INSERT #t1
( object_id ,
object_name ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT asm.assembly_id ,
asm.name ,
t.object_db ,
@assm ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.assembly_modules AS am ON ( ( am.object_id = t.object_id )
AND ( am.assembly_id >= 65536 )
)
JOIN sys.assemblies AS asm ON asm.assembly_id = am.assembly_id
WHERE @iter_no = t.rank
AND t.object_type IN ( @udf, @sp, @uda, @tr )
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- Partition Schemes referenced by tables/views
INSERT #t1
( object_id ,
object_name ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT ps.data_space_id ,
ps.name ,
t.object_db ,
@part_sch ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.indexes AS idx ON idx.object_id = t.object_id
JOIN sys.partition_schemes AS ps ON ps.data_space_id = idx.data_space_id
WHERE @iter_no = t.rank
AND t.object_type IN ( @u, @v )
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- Partition Function referenced by Partition Schemes
INSERT #t1
( object_id ,
object_name ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT pf.function_id ,
pf.name ,
t.object_db ,
@part_func ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.partition_schemes AS ps ON ps.data_space_id = t.object_id
JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
WHERE @iter_no = t.rank
AND t.object_type = @part_sch
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- sp, udf, triggers referenced by plan guide
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT o.object_id ,
o.name ,
SCHEMA_NAME(o.schema_id) ,
t.object_db ,
( CASE o.type
WHEN 'P' THEN @sp
WHEN 'TR' THEN @tr
ELSE @udf
END ) ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.plan_guides AS pg ON pg.plan_guide_id = t.object_id
JOIN sys.objects AS o ON o.object_id = pg.scope_object_id
WHERE @iter_no = t.rank
AND t.object_type = @pg
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- objects referenced by synonym
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT o.object_id ,
o.name ,
SCHEMA_NAME(o.schema_id) ,
t.object_db ,
( CASE WHEN o.type = 'U' THEN @u
WHEN o.type = 'V' THEN @v
WHEN o.type IN ( 'P', 'RF', 'PC' )
THEN @sp
WHEN o.type = 'AF' THEN @uda
ELSE @udf
END ) ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
0 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.synonyms AS s ON s.object_id = t.object_id
JOIN sys.objects AS o ON o.object_id = OBJECT_ID(s.base_object_name)
AND o.type IN ( 'U',
'V', 'P', 'RF',
'PC', 'AF', 'TF',
'FN', 'IF', 'FS',
'FT' )
WHERE @iter_no = t.rank
AND t.object_type = @synonym
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
-- uddt referenced by sequence. Used to find UDDT that is in sequence dependencies.
INSERT #t1
( object_id ,
object_name ,
object_schema ,
object_db ,
object_type ,
relative_id ,
relative_name ,
relative_schema ,
relative_db ,
relative_type ,
schema_bound ,
rank
)
SELECT tp.user_type_id ,
tp.name ,
SCHEMA_NAME(tp.schema_id) ,
t.object_db ,
CASE tp.is_assembly_type
WHEN 1 THEN @udt
ELSE @uddt
END ,
t.object_id ,
t.object_name ,
t.object_schema ,
t.object_db ,
t.object_type ,
1 ,
@iter_no + 1
FROM #t1 AS t
JOIN sys.sequences AS s ON s.object_id = t.object_id
JOIN sys.types AS tp ON tp.user_type_id = s.user_type_id
AND tp.schema_id != 4
WHERE @iter_no = t.rank
AND t.object_type = @sequence
AND ( t.object_svr IS NULL
AND t.object_db = DB_NAME()
);
SET @rows = @rows + @@rowcount;
END;
SET @iter_no = @iter_no + 1;
END;
UPDATE #t1
SET rank = 0;
-- computing the degree of the nodes
UPDATE #t1
SET degree = ( SELECT COUNT(*)
FROM #t1 t
WHERE t.relative_id = #t1.object_id
AND t.object_id != t.relative_id
);
-- perform the topological sorting
SET @iter_no = 1;
WHILE 1 = 1
BEGIN
UPDATE #t1
SET rank = @iter_no
WHERE degree = 0;
-- end the loop if no more rows left to process
IF ( @@rowcount = 0 )
BREAK;
UPDATE #t1
SET degree = NULL
WHERE rank = @iter_no;
UPDATE #t1
SET degree = ( SELECT COUNT(*)
FROM #t1 t
WHERE t.relative_id = #t1.object_id
AND t.object_id != t.relative_id
AND t.object_id IN ( SELECT
tt.object_id
FROM #t1 tt
WHERE
tt.rank = 0 )
)
WHERE degree IS NOT NULL;
SET @iter_no = @iter_no + 1;
END;
--correcting naming mistakes of objects present in current database
--This part need to be removed once SMO's URN comparision gets fixed
DECLARE @collation sysname;
DECLARE db_cursor CURSOR
FOR
SELECT DISTINCT
ISNULL(object_db, DB_NAME())
FROM #t1 AS t
WHERE t.object_id IS NOT NULL
AND t.object_svr IS NULL;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
IF ( DB_ID(@dbname) IS NULL )
BEGIN
FETCH NEXT FROM db_cursor INTO @dbname;
CONTINUE;
END;
SET @collation = ( SELECT CONVERT(sysname, DATABASEPROPERTYEX(@dbname,
'Collation'))
);
SET @query = 'update #t1 set #t1.object_name = o.name,#t1.object_schema = sch.name from #t1 inner join '
+ QUOTENAME(@dbname)
+ '.sys.objects as o on #t1.object_id = o.object_id inner join '
+ QUOTENAME(@dbname)
+ '.sys.schemas as sch on sch.schema_id = o.schema_id where o.name = #t1.object_name collate '
+ @collation + ' and sch.name = #t1.object_schema collate '
+ @collation;
EXEC (@query);
FETCH NEXT FROM db_cursor INTO @dbname;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
--final select
SELECT ISNULL(t.object_id, 0) AS [object_id] ,
t.object_name ,
ISNULL(t.object_schema, '') AS [object_schema] ,
ISNULL(t.object_db, '') AS [object_db] ,
ISNULL(t.object_svr, '') AS [object_svr] ,
t.object_type ,
ISNULL(t.relative_id, 0) AS [relative_id] ,
t.relative_name ,
ISNULL(t.relative_schema, '') AS [relative_schema] ,
relative_db ,
ISNULL(t.relative_svr, '') AS [relative_svr] ,
t.relative_type ,
t.schema_bound ,
ISNULL(CASE WHEN p.type = 'U' THEN @u
WHEN p.type = 'V' THEN @v
END, 0) AS [ptype] ,
ISNULL(p.name, '') AS [pname] ,
ISNULL(SCHEMA_NAME(p.schema_id), '') AS [pschema]
FROM #t1 AS t
LEFT JOIN sys.objects AS o ON ( t.object_type = @tr
AND o.object_id = t.object_id
)
OR ( t.relative_type = @tr
AND o.object_id = t.relative_id
)
LEFT JOIN sys.objects AS p ON p.object_id = o.parent_object_id
ORDER BY rank DESC;
--SELECT * FROM #t1
--SELECT * FROM #tempdep
--SELECT * FROM #t2
DROP TABLE #t1;
DROP TABLE #t2;
DROP TABLE #tempdep;
IF @must_set_nocount_off > 0
SET NOCOUNT OFF;