Getting tables referenced in every stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Getting tables referenced in every stored proc

Author

Topic

steppinthrax
Starting Member

27 Posts

Posted - 2011-07-07 : 11:21:44

Ok,

I have the following query


SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT = '%somtab%'

This gets tables being referenced within a stored procedure. It displays all tables referenced within a stored procedure.

I then have this.

quote:

select Table_Name
from INFORMATION_SCHEMA.TABLES
where Table_type = 'BASE TABLE'
order by table_name

This gives me all tables within the database.

I need to combine these queries so that I get all stored procedures for EVERY TABLE in the database. I've tried doing this with a cursor but it dosen't seem to like the cursor varible in the like clause. Is there another way to do this?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-07 : 12:08:31

[code]SELECT distinct t.name [table], p.name [sp]
FROM sys.tables t
JOIN sysdepends d
On d.depid = t.object_id
JOIN sys.procedures p
On p.object_id = d.id[/code]

steppinthrax
Starting Member

27 Posts

Posted - 2011-07-07 : 13:02:33

Holy shit, this works 10 times better than my cursor which pulled table names out of comments within a stored procedure resulting in incorrect search

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-07 : 13:13:00

Note that it won't resolve cross-database references properly.

Also, have a look at sys.sql_dependencies

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources