Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Search all objects in all DBs for code fragments

By Cade Bryant,

This procedure allows you to search through all objects in all databases on your server for words/phrases in your object code.  Very handy for cases in which, for example, a column name on a table has been changed and you need to search your entire server for any sprocs/views/etc. that might reference it.

Syntax: [EXEC] sp_FindCodeStr [@SearchStr=]'', [@PrintOnly=]{0, 1}

Notice that no cursors or temp tables are used in this sproc.  Dynamic T-SQL execution appends all the needed statements onto the @sql variable, building the batch in a set-oriented manner, and then the final batch is executed via sp_executesql.  As a result, this sproc performs surprisingly well.

Total article views: 237 | Views in the last 30 days: 1
Related Articles

Sproc calling Sproc

Question about calling a sproc from a sproc


Dynamic Query/sproc

Create columns with dynamic Query/sproc


Reducing execution time of sproc

sproc contains lots of temp tables.


Tunning Sproc for Web Application

Tunning Sproc for Web Application(Help required)


Transaction Control ADO/Sproc

Is there Transaction control when an ADO Tran is opened and Sproc is called within it?


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones