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.

WITH
SearchStrTable
–CTE–
As
(
/*
ENTER THE SEARCH STRING HERE
*/
SELECT ‘OUTPUT’ As SearchStr –SQL extension, in Oracle Dual table needed–
)
SELECT
–object–
o.name
–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)
,LEN(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
,SearchStrTable
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
(
‘SYSTEM_TABLE’
,’FOREIGN_KEY_CONSTRAINT’
,’USER_TABLE’
,’SYNONYM’
,’SERVICE_QUEUE’
,’CHECK_CONSTRAINT’
)