This is a micro-post to show you how easy it is to search through all objects of a SQL Server Database for a specific string. Say that you want to rename a function and you want to know how many procedures are using this function – instead of opening all procedures trying to find the matching string, this article will give you a solution that simplifies the matter ten fold!
All your databases have a SYS schema; and within this schema is a view called OBJECTS.
It is in this OBJECTS view where all your database elements are stored and referenced; including Table names, Primary/Foreign Key constraints, Procedures, Functions, View.. everything!
So here’s the basic query…
This will list every element associated to the Database you run the query under:
SELECT * FROM SYS.OBJECTS O
Filtering by Element Type
To narrow down your search, you can filter the above query using the [type] column that is returned from the OBJECTS view. Below is a short list of the different type codes available and what they refer to:
- P = Stored Procedure
- U = User Table
- V = View
- TT = Type Table
- FN = Scalar Function
- IF = Inline Table Function
- PK = Primary Key Constraint
- F = Foreign Key Constraint
SELECT * FROM SYS.OBJECTS O WHERE [type] = 'P'
Searching the Element Text
The final step is to locate the data content of your database elements to complete your search. Here we need the help of a second View called SYSCOMMENTS. This view returns a ‘text’ component to your database elements. Specifically for Stored Procedures, Function and View this component holds the code counterpart for those elements.
To proceed, join your OBJECTS view with the SYSCOMMENTS view on the object_id and id columns respectively as shown below:
SELECT * FROM SYS.OBJECTS O INNER JOIN SYS.syscomments C on C.id = O.object_id WHERE O.[type] = 'P' AND C.text LIKE '%xyz%'
There you go. So whenever you need to search your database for strings, you know how it’s done!!
Hope this was helpful, make use of it well!!