Tables referenced by a stored proc (2000)


I had to do some work on a 2000 database with very long stored procedures. The script was designed to pull out the names of all tables which a stored procedure references.

I'm posting it here in the knowledge that you'll alert me if it doesn't work properly. I'm already aware of a potential problem when a table name is split over two lines in syscomments.

The #endchars thing is to avoid getting a match on a table 'Table1' when the sp contains a reference to 'Table12'

declare @sproc varchar(8000)

set @sproc='spMyStoredProc'

CREATE TABLE #endchars (endchar char(1))

INSERT INTO #endchars (endchar)

select ' ' union select ')' union select ']' union select ';' union select char(9) union select char(10) union select char(13)

select from sysobjects so, syscomments sc, #endchars ec where so.type='U' and object_name(

group by

having sum(charindex( ,sc.text+' '))<>0

order by name

DROP TABLE #endchars


2.5 (6)




2.5 (6)