Find dependency refering a table in SQL SERVER

Here is the query to know the dependency of table in sql server.
Just replace the @TableName variable.


DECLARE @TableName Varchar(50)
SET @TableName = 'Association'
SELECT  
    DISTINCT 
    ISNULL(sd.referenced_schema_name+'.','')+ OBJECT_NAME(sd.referenced_id) As TableName,
    OBJECT_NAME(sd.referencing_id) As ObjectName,
    CASE 
	WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsUserTable') = 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 ObjectType
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,ObjectName,ObjectType

Advertisements
Posted in Uncategorized. Leave a Comment »

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

%d bloggers like this: