SQL SERVER – Find Stored Procedure,Table,Function Related to Table

 

Some times we need to find Stored procedure,Tables,View,Function related to table.

Following are two script I have written  to find out Object Dependent on Table.

 

Script1:SQL Server 2005 and Later


SELECT DISTINCT   OBJECT_NAME(sd.referenced_major_id)TableName,
        OBJECT_NAME(sd.object_id)Ref_Object,
        CASE WHEN OBJECTPROPERTYEX(sd.object_id,N'ISTABLE')= 1
                     THEN'Table'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsTableFunction')= 1
                     THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsTableFunction')= 1
                     THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsScalarFunction')= 1
                     THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsTrigger')= 1
                     THEN'Trigger'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsView')= 1
                     THEN'View'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsUserTable')= 1
                     THEN'Table'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsProcedure')= 1
                     THEN'Procedure'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsIndexed')= 1
                     THEN'Index'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsForeignKey')= 1
                     THEN'ForeignKey'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsPrimaryKey')= 1
                     THEN'PrimaryKey'
        END AS Ref_Object_Name
FROM    sys.sql_dependencies SD
        INNER JOIN sys.objects obj
                     ON obj.object_id=sd.referenced_major_id
WHERE   obj.is_ms_shipped= 0
        AND obj.type_desc='USER_TABLE'
        ORDER BY TableName,Ref_Object,Ref_Object_Name

Script 2:SQL Server 2008 and Later

SELECT  DISTINCT ISNULL(sd.referenced_schema_name+'.','')+ OBJECT_NAME(sd.referenced_id)TableName,
        OBJECT_NAME(sd.referencing_id)Ref_Object,
        CASE WHEN OBJECTPROPERTYEX(sd.referencing_id,N'ISTABLE')= 1
                     THEN'Table'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTableFunction')= 1
                     THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTableFunction')= 1
                     THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsScalarFunction')=1
                     THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTrigger')= 1
                     THEN'Trigger'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsView')= 1
                     THEN'View'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsUserTable')= 1
                     THEN'Table'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsProcedure')= 1
                     THEN'Procedure'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsIndexed')= 1
                     THEN'Index'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsForeignKey')= 1
                     THEN'ForeignKey'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsPrimaryKey')= 1
                     THEN'PrimaryKey'
        END AS Ref_Object_Name
FROM    sys.sql_expression_dependencies SD
        INNER JOIN sys.objects obj
                     ON obj.object_id=sd.referenced_id
WHERE   obj.is_ms_shipped= 0
--AND  referenced_id=object_id('TableName') /*Where one can Replace table Name*/
        AND obj.type_desc='USER_TABLE'
        ORDER BY TableName,Ref_Object,Ref_Object_Name

sys.sql_dependencies View will be removed from future verison of SQL Server 
so it is better to use script2 Both Version are able to give table

Dependencyon other object.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s