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=’
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
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: <br />SELECT NAME<br />FROM SYSOBJECTS S<br />WHERE <br /> OBJECTPROPERTY(ID,’IsUserTable’) = 1<br />AND EXISTS(<br /> SELECT 1<br /> FROM SYSCOMMENTS<br /> WHERE<br /> ID = OBJECT_ID(‘sp_name’) <br /> AND(<br /> CHARINDEX(S.NAME + ‘,’, text)>0 –FROM TABLE1, or use the PATINDEX<br /> OR CHARINDEX(S.NAME + ‘ ‘,text) > 0 –FROM TABLE1 JOIN, or use the PATINDEX<br /> OR CHARINDEX(S.NAME + ‘] ‘,text) > 0 OR — FROM [TABLE1], or use the PATINDEX<br /> OR PATINDEX(‘INTO %’+ S.NAME +’%’, text) > 0 –into table<br /> )<br />)<br /><br />–you can use the patindex if you want instead of the CHARINDEX.(or use only the CHARINDEX(S.NAME, text)>0 if you can).<br />–for remote objects, you may need to parse the actual statement, if it is not protected <img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
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=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
]]>