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)

Find strings in text of stored procedures

By bliviu,

Ussualy this kind of scripts queries sysobjects and syscomments like this:

select distinct object_name(O.id)
from syscomments as C
join sysobjects as O on O.id = C.id
and o.type = 'P' -- search string only in stored procedures
where C.text like '%insert into MyTable%'


When the text of stored procedures, triggers, functions is over 4000 characters, it is
saved in syscomments on many records. The string I want to find may be splitted in 2
records:

......................insert in
to MyTable.....................

In this case the upper query will not work.

This script creates a temporary table with a [text] column where the entire text of stored
procedure is saved. It applies LIKE on that column and in this way no result is lost.
It also shows the number of occurrences of the string.

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

String Operation in Stored Procedure

String Operation in Stored Procedure

ARTICLE

Searching Syscomments Accurately

As a SQL Server DBA you should know that your code is stored in syscomments by default. While most D...

SCRIPT

Search for a String in all Stored Procedures and beyond?

Search for String Occurence with this script in your stored procedures.

FORUM

Retrieve Multiple Roleid's by passing csv strings into a stored procedure

How to Retrieve roleid's by passing csv strings into stored procedure

SCRIPT

Nested Stored Procedure

Nested Stored Procedure

 
Contribute