.NET Musings

Not to be out done, Chris Barth (also of the Cincinnati SQL Server Users' Group and Pinnacle Solutions Group, Inc) , had this great tip for SQL Server on finding objects in a SQL Server database.

I recently wanted to change some table structures, and needed to know the impact of such a change before plowing forward. By running this script:


SELECT OBJECT_NAME(OBJECT_ID)
FROM sys.sql_modules
WHERE definition like '%text_to_find%'

I was able to quickly determine the impact of the change.

I went one step further, made the script block into a Template. If you haven't done this, open up Template Explorer (from the View menu) and create a new Folder to house your custom templates. Right click on the folder, select New Template. In the query editor window, copy the following:

USE <databasename,string,@p1>
GO
SELECT OBJECT_NAME(OBJECT_ID)
FROM sys.sql_modules
WHERE definition like '%<string_to_find,string,@p2>%'

and then save the new template.

Now, to use it, double click on the template, and it will load into a new window. Press Ctl-Shift-M, and you will get prompted with a dialog to replace the placeholders with the text of your choosing.

A GREAT timesaver. Thanks, Chris!

Happy Coding!