Tables referenced by a stored proc (2000)

David McKinney, 2008-12-30 (first published: 2008-12-05)

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