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 so.name from sysobjects so, syscomments sc, #endchars ec where so.type='U' and object_name(sc.id)=@sproc

group by so.name

having sum(charindex(so.name+ec.endchar ,sc.text+' '))<>0

order by name



DROP TABLE #endchars

Rate

2.5 (6)

Share

Share

Rate

2.5 (6)