This post originated from an RSS feed registered with .NET Buzz by Raymond Lewallen. Original Post: Find Sql Stored Procedures, Tables and Views Containing Some Text Feed Title: Raymond Lewallen Feed URL: /error.htm?aspxerrorpath=/blogs/raymond.lewallen/rss.aspx Feed Description: Patterns and Practices, OOP, .Net and Sql Latest .NET Buzz Posts Latest .NET Buzz Posts by Raymond Lewallen Latest Posts From Raymond Lewallen

Last week, John posted a T-Sql script to find all stored procedures that contain some text. Along those sames lines, I have expanded what he has done to also include returning tables and views that have column names that contain the same text.

CREATE PROCEDURE sp_find_objects_containing

            @search varchar(128)

AS

SET @search = '%' + @search + '%'

SELECT o.name As "Stored Procedures"

            FROM SYSOBJECTS o INNER JOIN SYSCOMMENTS c

                        ON o.id = c.id

            WHERE c.text LIKE @search

                        AND o.xtype = 'P'

            GROUP BY o.name

            ORDER BY o.name

SELECT o.name As "Views"

            FROM SYSOBJECTS o INNER JOIN SYSCOMMENTS c

                        ON o.id = c.id

            WHERE c.text LIKE @search

                        AND o.xtype = 'V'

            GROUP BY o.name

            ORDER BY o.name

SELECT o.name As "Tables"

            FROM SYSCOLUMNS c INNER JOIN SYSOBJECTS o

                        ON c.id = c.id

                        INNER JOIN MASTER.DBO.SYSTYPES t

                        ON c.xtype = t.xtype

            WHERE o.name LIKE @search

                        AND o.Type = 'U'

            GROUP BY o.name

            ORDER BY o.name

GO