470,648 Members | 1,317 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Important SQL Server Statements/Searchs

111 100+
In this article I would like to share some of the important searches, like searching for table used in stored procedures and searching for list of tables and views used in a stored procedure.

1) List stored procedures which contain a table.
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT syso.name FROM syscomments sysc INNER JOIN sysobjects syso ON sysc.id= syso.id WHERE sysc.TEXT LIKE '%tablename%' and upper(xtype)='P'

Here this query will return all the stored procedures using the given table.
Note: Keep your table name in place of: tablename in the above query.

What is syscomments object?.

The actual code for views, rules, defaults, triggers, CHECK constraints, DEFAULT constraints and stored procedures are stored in the syscomments table. The column TEXT in the syscomments table contains the actual code for all these objects. Knowing this allows you to write some simple T-SQL code that can scan the syscomments table looking for an actual table column name or database object name that we want to search.

2) List tables used in a stored procedure.
select name from sysobjects where id in

Expand|Select|Wrap|Line Numbers
  1. (select sd.depid from sysobjects so, sysdepends sd  
  2. where so.name = 'usp_Your_Stored_Procedure' and sd.id = so.id ) and upper(xtype) = 'U'
This statement will list all the tables used in the given stored procedure. (In our case its ''usp_Your_Stored_Procedure')

Note: This will return only the tables used in this procedure.

If you want to get the views also then you have to use below query.
Expand|Select|Wrap|Line Numbers
  1. select name from sysobjects where id in 
  2. (select sd.depid from sysobjects so, sysdepends sd  
  3. where so.name = 'usp_getRepReport_by_OU' and sd.id = so.id ) and upper(xtype) in ('U','V')
I hope this helps to debugging larger existing application where you stand as a support developer.

Thanks
Bharath Reddy VasiReddy
Sr Software Engineer(RBC)
Jun 2 '10 #1
0 2957

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

28 posts views Thread by (Pete Cresswell) | last post: by
1 post views Thread by Korara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.