Home > Functions and DMVs, SQL Queries, SQL Server, Stored Procedures, T-SQL > Check for the existence of a string in all procs and functions within a database.

Check for the existence of a string in all procs and functions within a database.

Many developers have asked me,  if there is any way to check the existence of a string or text with in the procedures or functions in a database. This especially will come handy when you change a table or any other scripts in a database which can affect the dependent objects. For example  renaming the users of a database may affect the procedures as permissions might be assigned to these users within the SP. 

The trick is very simple. We can use the table “SYSCOMMENTS” to get the name of the objects. it will be easier to work by narrowing down the list. For example if you want to search for a text “user_new” use the following script as given below. The script will list all the object that contains the text mentioned earlier. 

SELECT OBJECT_NAME(ID) 

FROM  SYSCOMMENTS

WHERE TEXT LIKE ‘%USER_NEW%’ 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: