Finding all SQL Table References in a Database

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.

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

Happy Coding!

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

Tags: , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *


5 − 2 =

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>