SQL Server Performance Forum – Threads Archive

How do I find list of Table Names from SP?

Hi All,
I am trying to find all the table names that are referencing to the stored procedure in the database. How can I get the list of table names that are using in stored procedure without going through the sp_helptext on every individual stored procedure? Any help would be greatly appreciated. Thanks Dan

there is no direct way. You have to perform some parsing on the Stored Procedure code your self. You can find the stored procedure codes from syscomments.text provided that your stored procedure is not encrypted
KH

Or from the INFORMATION_SCHEMA.ROUTINES select ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES where SPECIFIC_NAME = ‘sp_name_here’ KH

Another indirect way of finding those is… SELECT Text
FROM syscomments
WHERE Text like ‘%FROM TABLE_NAME%’
AND object_name(id) = ‘sp_name’
UNION ALL
SELECT Text
FROM syscomments
WHERE Text like ‘%UPDATE TABLE_NAME%’
AND object_name(id) = ‘sp_name’
UNION ALL
SELECT Text
FROM syscomments
WHERE Text like ‘%INTO TABLE_NAME%’
AND object_name(id) = ‘sp_name’ A drawback is that there should be only one space between the table_name and the keywords FROM. UPDATE AND INTO… Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

sp_depends ‘Procedure Name’ Madhivanan Failing to plan is Planning to fail

quote:Originally posted by Madhivanan sp_depends ‘Procedure Name’ Madhivanan Failing to plan is Planning to fail

That’s great !
KH

IIRC, sp_depends isn’t always reliable. But as always, I can’t find a reference right now. [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

As you’re aware the information in sysdepends points to other database objects using the id column from sysobjects. When the object is dropped the ID becomes meaningless. When the object is recreated, it gets a new object ID and the old dependency information remains invalid. I’m sure this was covered in one of the kNowledgebase articles and I will refer I find it. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Frank is right, I found a lot of objects that were not shown as sp_depends ‘Procedure Name’ even thought I could find them from syscomments. Is there any way to go through the loop and check every single sp for Table references? Get the list of table names and put them into table variable. something like this(may not be correct): declare @substrtext varchar(50)
, @tablename varchar(50) select @substrtext = substring(text, (6 + CHARINDEX(‘ FROM ‘, text)), 50)
from syscomments –where id = 1490104349 select @substrtext select @tablename = substring(@substrtext, 1, charindex(‘ ‘, @substrtext)) select @tablename Dan

Another thing with syscomments is that the ‘text’ column is NVARCHAR(4000). So you also need to UNION on a a query that does a self join like so: SELECT OBJECT_NAME(t1.id)
FROM syscomments t1
WHERE t1.Text like ‘%FROM TABLE_NAME%’
UNION
SELECT OBJECT_NAME(t1.id)
FROM syscomments t1
INNER JOIN syscomments t2 ON t1.id = t2.id
AND t2.colid = t1.colid + 1
WHERE t1.Text + t2.text LIKE ‘%FROM TABLE_NAME%’

assuming the stored procedure is only referring to tables on the same database and the stored procedure is not encrypted, try this: SELECT NAME FROM SYSOBJECTS S WHERE OBJECTPROPERTY(ID,’IsUserTable’) = 1 AND EXISTS( SELECT 1 FROM SYSCOMMENTS WHERE ID = OBJECT_ID(‘sp_name’) AND( CHARINDEX(S.NAME + ‘,’, text)>0 –FROM TABLE1, or use the PATINDEX OR CHARINDEX(S.NAME + ‘ ‘,text) > 0 –FROM TABLE1 JOIN, or use the PATINDEX OR CHARINDEX(S.NAME + ‘] ‘,text) > 0 OR — FROM [TABLE1], or use the PATINDEX OR PATINDEX(‘INTO %’+ S.NAME +’%’, text) > 0 –into table ) ) –you can use the patindex if you want instead of the CHARINDEX.(or use only the CHARINDEX(S.NAME, text)>0 if you can). –for remote objects, you may need to parse the actual statement, if it is not protected <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> May the Almighty God bless us all! www.empoweredinformation.com

Thanks a lot guys. I need to work on these to make it a dynamic so that i don’t need to explicitly give sp_name and table_name. It should go through the database to find the table names and dependent objects for these tables or vice versa. Anyone who thinks that i have not asked too much can help me on this logic. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Thanks guys and have a nice weekend. Dan

]]>