SQL 2005 utility

[R7Dave]

Hello

I was creating a TSQL statement that would tell me every stored procedure that contained a certain table.

Next, I needed a way to select each database on the server.

My question is - Does SQL Server Manager already have a utility to do this?

declare @SEARCHWORD as  varchar(500)
set @SEARCHWORD = 'anytable'

Select specific_name, routine_definition, *
From Information_Schema.Routines
Where Routine_definition Like '%'+@SEARCHWORD+'%'

Thanks
Dave

 

[markros]

If you need to run this code for each database in SQL Server, you may use officially undocumented (but documented in various sources) sp_msForEachDB procedure.

See example execute sp_MSForEachDB 'print ''[?]'''

 

[mrdenny]

Why not simply use the sp_depends system stored procedure to find out what tables are referenced by which procedures?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog