Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: 238 | Views in the last 30 days: 1
 
Related Articles
FORUM

Sproc calling Sproc

Question about calling a sproc from a sproc

FORUM

Dynamic Query/sproc

Create columns with dynamic Query/sproc

FORUM

Reducing execution time of sproc

sproc contains lots of temp tables.

FORUM

Tunning Sproc for Web Application

Tunning Sproc for Web Application(Help required)

FORUM

Transaction Control ADO/Sproc

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

Tags
 
Contribute