/*
Author: Konstantin Taranov
Source: https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Find_Object_Dependecy.sql
*/
DECLARE @objectName SYSNAME = 'ObjectName';
DECLARE @schemaName SYSNAME = 'SchemaName';
IF Object_id('tempdb..#tempdep', 'U') IS NOT NULL
DROP TABLE #tempdep;
IF Object_id('tempdb..#t1', 'U') IS NOT NULL
DROP TABLE #t1;
IF Object_id('tempdb..#t2', 'U') IS NOT NULL
DROP TABLE #t2;
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 tbl.object_id AS [ID], tbl.name AS [Name], SCHEMA_NAME(tbl.schema_id) AS [Schema], db_name(), 3
FROM sys.tables AS tbl
WHERE (tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)',
N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',
@_msparam_0=@objectName,
@_msparam_1=@schemaName;
DECLARE @find_referencing_objects INT;
SET @find_referencing_objects = 1;
-- 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) AS 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;
DECLARE @quote_quoted_dbname SYSNAME;
DECLARE @bracket_quoted_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
SET @quote_quoted_dbname = QUOTENAME(@dbname, '''');
SET @bracket_quoted_dbname = QUOTENAME(@dbname, ']');
DECLARE @query NVARCHAR(max);
-- WHEN schema is not null
-- @obj
SET @query = 'UPDATE #t2 set object_db = N'
+ @quote_quoted_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 '
+ @bracket_quoted_dbname
+ '.sys.objects as obj JOIN '
+ @bracket_quoted_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 = N'
+ @quote_quoted_dbname
+ ') AND #t2.rank = ('
+ CAST(@iter_no AS NVARCHAR(8))
+ '+1) AND #t2.object_id IS NULL and #t2.object_svr IS NULL;'
EXEC sp_executesql @query;
-- @type
SET @query = 'update #t2 set object_db = N'
+ @quote_quoted_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 '
+ @bracket_quoted_dbname
+ '.sys.types AS t JOIN '
+ @bracket_quoted_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 = N'
+ @quote_quoted_dbname
+ ') AND #t2.rank = ('
+ CAST(@iter_no AS NVARCHAR(8))
+ '+1) AND #t2.object_id IS NULL and #t2.object_svr IS NULL;';
EXEC sp_executesql @query;
-- @xml
SET @query = 'UPDATE #t2 set object_db = N'
+ @quote_quoted_dbname
+ ', object_id = x.xml_collection_id FROM '
+ @bracket_quoted_dbname
+ '.sys.xml_schema_collections AS x JOIN '
+ @bracket_quoted_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 = N'
+ @quote_quoted_dbname
+ ') AND #t2.rank = ('
+ CAST(@iter_no AS NVARCHAR(8))
+ '+1) AND #t2.object_id IS NULL and #t2.object_svr IS NULL;';
EXEC sp_executesql @query;
-- @part_func - schema is always null
-- @schema is null
-- consider schema as 'dbo'
-- @obj
SET @query = 'UPDATE #t2 set object_db = N'
+ @quote_quoted_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 '
+ @bracket_quoted_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 = N'
+ @quote_quoted_dbname
+ ') AND #t2.rank = ('
+ CAST(@iter_no AS NVARCHAR(8))
+ '+1) AND #t2.object_id IS NULL and #t2.object_svr IS NULL;';
EXEC sp_executesql @query;
-- @type
SET @query = 'update #t2 set object_db = N'
+ @quote_quoted_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 '
+ @bracket_quoted_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 = N'
+ @quote_quoted_dbname
+ ') AND #t2.rank = ('
+ CAST(@iter_no AS NVARCHAR(8))
+ '+1) AND #t2.object_id IS NULL AND #t2.object_svr IS NULL;';
EXEC sp_executesql @query;
-- @xml
SET @query = 'update #t2 set object_db = N'
+ @quote_quoted_dbname
+ ', object_id = x.xml_collection_id, object_schema = SCHEMA_NAME(x.schema_id) FROM '
+ @bracket_quoted_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 = N'
+ @quote_quoted_dbname
+ ') and #t2.rank = ('
+ CAST(@iter_no AS NVARCHAR(8))
+ '+1) and #t2.object_id IS NULL and #t2.object_svr IS NULL;';
EXEC sp_executesql @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'
+ @quote_quoted_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 '
+ @bracket_quoted_dbname
+ '.sys.objects as obj JOIN '
+ @bracket_quoted_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 = N'
+ @quote_quoted_dbname
+ ') AND #t2.rank = ('
+ CAST(@iter_no AS NVARCHAR(8))
+ '+1) AND #t2.object_id IS NULL AND #t2.object_svr IS NULL;';
EXEC sp_executesql @query;
-- @type
SET @query = 'UPDATE #t2 set object_db = N'
+ @quote_quoted_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 '
+ @bracket_quoted_dbname
+ '.sys.types as t JOIN '
+ @bracket_quoted_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 = N'
+ @quote_quoted_dbname
+ ') AND #t2.rank = ('
+ CAST(@iter_no AS NVARCHAR(8))
+ '+1) AND #t2.object_id IS NULL AND #t2.object_svr IS NULL;'
EXEC sp_executesql @query;
-- @xml
SET @query = 'UPDATE #t2 set object_db = N'
+ @quote_quoted_dbname
+ ', object_id = x.xml_collection_id, object_schema = SCHEMA_NAME(x.schema_id) FROM '
+ @bracket_quoted_dbname
+ '.sys.xml_schema_collections as x JOIN '
+ @bracket_quoted_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 = N'
+ @quote_quoted_dbname
+ ') AND #t2.rank = ('
+ CAST(@iter_no AS NVARCHAR(8))
+ '+1) and #t2.object_id IS NULL AND #t2.object_svr IS NULL;';
EXEC sp_executesql @query;
-- @part_func always have schema as null
SET @query = 'update #t2 set object_db = N'
+ @quote_quoted_dbname
+ ', object_id = p.function_id FROM '
+ @bracket_quoted_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 = N'
+ @quote_quoted_dbname
+ ') and #t2.rank = ('
+ CAST(@iter_no AS NVARCHAR(8))
+ '+1) AND #t2.object_id IS NULL and #t2.object_svr IS NULL';
EXEC sp_executesql @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 sp_executesql @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]
,t.rank
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;
DROP TABLE #t1;
DROP TABLE #t2;
DROP TABLE #tempdep;
IF @must_set_nocount_off > 0
SET nocount OFF;