Important SQL Server Statements/Searchs

[bharathreddy]

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.

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

(select sd.depid from sysobjects so, sysdepends sd  
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.

select name from sysobjects where id in 
(select sd.depid from sysobjects so, sysdepends sd  
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)