Finding text in SQL Server Objects By Querying System Tables

Back in the days of SQL Server 6.5 I developed a query to search stored procedures for a given string, which I used to email to myself on every assignment that I took up. It’s now well out of date (and I’ve lost the file) so I’ve decided to do a quick rewrite, this time including text from other types of objects such as views.

I like SQL Server Management Studio and I know that somewhere it has a search facility to do this, but I tried to find it and couldn’t. In general it’s nice to have scripts or procedures available for these jobs – then you still have options if the IDE changes or starts misbehaving.

I have a set of dev utility stored procedures with very short names (“c” for one that returns a select list of columns in a table for example, “ct” for the same list with data type and nullability included). When I convert the script into a procedure I’ll call it “f” or “ft”. In SQL Server the utility procedures can be stored in a schema called Dev to keep them out of the way of the business objects. What if the DBA won’t allow you to add utility procedures? You can still keep a set of scripts, which is why I’ve presented this as a script.

Now that I have an Oracle setup on my workstation I’ll also write some Oracle equivalents using the data dictionary. In Oracle a schema is always owned by a user of the same name. The “dev” user created would no doubt be used to write the app as well as querying the dictionary. This is clearly a lot better than developing as an admin user, provided the permissions are defined carefully. I’ll probably cover the permissions needed for tuning and testing in a later posting.

The script below isn’t the final word. For example, I’d like to show characters preceding the word found as well as those after it. It did the job I wanted though, so I’ll leave improvements until I have time.

There are only a couple of features of minor technical interest. I’ve used a CTE (common table expression or “With clause”). This is so that the “find” string is only edited at the top of the script, easier to do than a search and replace. You’ll note that the CTE isn’t joined to the system tables. The results will therefore be a Cartesian product of the two joined system tables and the CTE. This doesn’t increase the number of rows returned: there’s only one row in the CTE.

SELECT ‘OUTPUT’ As SearchStr –SQL extension, in Oracle Dual table needed–
–search string and text following–
,SUBSTRING(sm.definition, CHARINDEX(SearchStr, definition), LEN(sm.definition))
–following columns useful only if this portion converted to derived table–
,CHARINDEX(SearchStr, definition)
FROM sys.all_objects o –sys.objects doesn’t include triggers
JOIN sys.sql_modules sm
ON o.object_id = sm.object_id
WHERE CHARINDEX(SearchStr, definition) > 0
And type_desc
views contain logic so return them as well
exclude unwanted objects so any new types will appear in query
Not In

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.