Managing SQL server objects can sometimes be quite challenging. With tables references used in over 40 different object types, locating and isolating one particular table can be time consuming. The script below has helped me several times when trying to make sure that I have addressed all instances of a table. Add your table name to the where clause and the result will provide all locations of that table in the database.

[code language=”sql”]
SELECT DISTINCT
syso.Name as SP_NAME,
sysob.name as Table_Name,
Case syso.xtype
When ‘V’ then ‘View’
When ‘P’ then ‘Stored Procedure’
When ‘AF’ then ‘Aggregate function (CLR)’
When ‘C’ then ‘Check Constraint’
When ‘D’ then ‘Default’
When ‘F’ then ‘Foreign Key Constraint’
When ‘FN’ then ‘SQL scalar function’
When ‘FS’ then ‘Assembly (CLR) scalar-function’
When ‘FT’ then ‘Assembly (CLR) table-valued function’
When ‘FS’ then ‘Assembly (CLR) scalar-function’
When ‘FT’ then ‘Assembly (CLR) table-valued function’
When ‘IF’ then ‘SQL inline table-valued function’
When ‘IT’ then ‘Internal Table’
When ‘PC’ then ‘Assembly (CLR) stored-procedure’
When ‘PG’ then ‘Plan guide’
When ‘PK’ then ‘Primary Key Constraint’
When ‘R’ then ‘Rule (old-Style, stand-alone)’
When ‘RF’ then ‘Replication-filter-procedure’
When ‘S’ then ‘System base table’
When ‘SN’ then ‘Synonym’
When ‘SO’ then ‘Sequence Object’
When ‘SQ’ then ‘Service queue’
When ‘TA’ then ‘Assembly (CLR) DML trigger’
When ‘TF’ then ‘SQL table-valued-function’
When ‘TR’ then ‘SQL DML Trigger’
When ‘TT’ then ‘Table Type’
When ‘U’ then ‘Table (User Defined)’
When ‘UQ’ then ‘UNIQUE Constraint’
When ‘X’ then ‘Extended stored procedure’
Else
syso.xtype
End
From
sys.sysdepends dps
inner Join sys.sysobjects syso on syso.id = dps.id
inner Join sys.sysobjects sysob on sysob.id = dps.depid
Where sysob.name='<<Table Name>>’ –Table Name
[/code]

Happy Coding!

Looking for quality web hosting? Look no further than Arvixe Web Hosting!