You'd have thought that nothing would be easier than using SQL Server Management Studio (SSMS) for searching through the DDL for both the names and definitions of the structural metadata of your databases, for the occurrence of a particular string of letters. Not so easy, it turns out, though Phil Factor is able to come up with various methods for various purposes.
Have you ever wanted a search button in SQL Server Management Studio (SSMS)? Of course, there is a ‘find’ menu item (Cntl F) but it won’t work in the Object Explorer pane of SSMS to find things in your database schema. You also have an object Search feature in SSMS 2008 within the Object Explorer 'Details' window, but that only searches the names of objects, not their definitions. It also doesn't search all objects, not even columns. Obviously, you can find strings within the current query window, but this is only a small fraction of what you actually need when you’re developing a database. You want to search for all your database objects, their names, their definitions and their comments.
How difficult can it be to search through the code (definition) as well as the name of a database object? After all, the object explorer must have a great deal of information about the objects in it. The answer, it seems, is ‘very difficult’.
Summary
Why isn’t it there in SSMS?
I don't know for sure, but I suspect that the reason there is no way of searching through the code as well as the name of structural metadata by code in SSMS is that it isn’t straightforward within the architecture that Microsoft has chosen to use, without breaking a few rules.
One problem is that there could be quite a few database objects around, such as the CLR functions, check constraints, defaults, default constraints, foreign keys , scalar functions, CLR scalar functions, CLR table-valued functions, inline table-functions, internal tables, stored procedures, CLR stored-procedures, plan guides, primary keys, rules, replication filters, service queues, CLR DML triggers, table functions , indexes, default constraints, table types ,user tables , unique constraints, views, XML Schema collections and extended stored procedures. Quite a few of these objects will have code in them.
The code ,or definition, of objects will be found in user-defined rules, defaults, unencrypted Transact-SQL stored procedures, user-defined Transact-SQL functions, triggers, computed columns, CHECK constraints, views, or system objects such as a system stored procedure. Even if you can search through all the code, you’d also probably need to look at the extended properties too. However, that’s just scraping the surface in terms of what is there or potentially there. .
Another problem is that SSMS is wedded to SMO, which provides an object-oriented programmatic model in place of the real way that SQL Server stores information about its objects such as tables, procedures and columns, and the hierarchical structure of objects. It is clever, it is useful, but it is dead slow to navigate when you’re looking at a large database, and trying to extract the contents of routines.
Why do you need to search your structural metadata?
There are a number of reasons why you might want to peruse your DDL. If you are used to glancing at, or clicking away at, the Object Explorer or using SQL Prompt, then you are probably scanning parts of the metadata. Most searching goes on when you are refactoring or maintaining an existing database that was written by someone else. An especially irksome task is renaming a view, table or a column. A dependency tracker will find the dependent objects but will, even if working perfectly, miss anything that is in dynamic code, embedded in strings. You may think that code only lurks in stored procedures or functions. Oh no. What about constraints, computed columns, defaults, rules, triggers or views? What of code in a CLR that accesses a database table? Code appears in a lot of places. Even if you are familiar with the database, it is easy, for example, to forget about a trigger on a table, miss-spell a column-name or overlook an index. For high-speed programming, the ‘point, click-and-curse’ technique isn’t really an option.
How do you do it?
We’ll go through the various alternative approaches to searching for stuff in your database definition.
Get the free tool
If you don’t want to get immersed in SQL code to do this, SQL Search is free and you don’t even have to give anyone your email address to get it.
This is perfect for the majority of requirements. At the moment, SQL Search is slightly limited in that the current version will search only the main database objects, but promised soon is the ability to search such things as index names and the contents of extended properties. It doesn’t yet entirely replace the requirement for a TSQL-based solution. However, you can see that it does more than the 'Object-Explorer-Details' Search of SSMS since it has found the search term in the text or definition of the stored procedure as well as finding it in the name.
Searching the entire build-script
Let’s start with the very simplest, but one of the most reliable methods of searching. You get out your build script. No build script? You generate it. If you like clicking at things, then use SSMS; otherwise use an automated procedure with Powershell and SMO(remember that you have to regenerate the build script every time someone else makes an alteration). I still use a slightly retro stored procedure with DMO; it works very well for me. Then, you read it into your favorite text editor and use the search facilities in it. This sounds clunky, but if your editor uses RegEx search, then a lot can be done, including the automatic generation of lists. Personally, I am pretty happy with this approach, but it isn’t always convenient.
The use of a query pane with the build script in it is almost as good, but you don’t have the regular expressions, or the performance of a programmers’ text editor. It also doesn’t help with some of the more advanced operations that you might need. I like making lists, with the help of a RegEx string, of the lines, or context, where each match happened. I can then scan them quickly to find a particular occurrence. (see TSQL Regular Expression Workbench for an introduction to RegEx). With a Grep tool, and SQLCMD, you can get lists of lines containing your search string.
The toe-in-the-water. The ‘Help’ system-procedures
There are a number of ‘traditional’ approaches to looking at your structural metadata, but they aren’t going to help much. If you just want a list of the most important objects, then just use…
Execute sp_help –-list all objects in the sys.sysobjects table
...but you will miss out on columns and indexes, and quite a few of the less important objects as well. There are other similar stored procedures, some of which are listed below, which are fine for getting specific information, but not much use for answering questions like ‘Where, in the database, is a date conversion used with a German (104) date format?’ The famous sp_helptext is fine for getting the text of a particular object but no more than that. There is also...
- sp_helpconstraint -- all constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines the constraint
- sp_helpdb -- Reports information about a specified database
- sp_helpextendedproc --currently defined extended stored procedures
- sp_helpfile -- the physical names and attributes of files associated with the current database
- sp_helpfilegroup –lists file groups
- sp_helpgroup –lists roles
- sp_helpindex –lists indexes, and the columns on which the index is built
- sp_helptext –displays the definition of a routine
- sp_helptrigger list the triggers and their types
Going Standard: Using Information Schema
Edgar Codd's fourth rule for a relational database management system is that there should be an Active online catalog based on the relational model that is accessible to authorized users by means of their regular query language: This means that users must be able to access the data about the database's structural metadata (catalog) just as easily as they can access data, and using the same query language that they use to access the database's data.
The Information_Schema views are a standard way of doing this. You can use them for searching if you don’t want too much. The queries you use for ad-hoc work, such as ‘which index covers this column?’, are best put into templates and dragged/dropped onto your workspace, or held as some form of snippet. Typing them out laboriously was never an option. You can, of course, use a stored procedure if you want to do a general search, or run a utility that queries via ODBC.
However, before we get too excited about the information Schema views, note that they have one or two big drawbacks. The first is that they only store the first 4000 characters of the definition of an object. If you write long stored procedures, you can move on to a later part of this article, or stick cosily to the idea of using a traditional programmers’ text editor as I’ve already described. The second drawback is that if you are interested in anything out of the ordinary, such as triggers or extended properties, I’m afraid you’ll be disappointed. Information Schema are provided only to be compatible with the standard, it seems.
Here is a procedure that does what it can to search your database, using Information schema.
IF EXISTS ( SELECT *
FROM information_Schema.routines
WHERE specific_name = 'FindStringInInformationSchema' )
DROP PROCEDURE FindStringInInformationSchema
go
CREATE PROCEDURE FindStringInInformationSchema
/**
summary: >
This finds the string that you specify within the name of many database objects including indexes and parameters of routines. It searches within the text (definition) for every routine. it displays the full path of the database object and the object type
This cannot find the text (Definition) or names of triggers, and knows nothing of extended properties
example:
- code: FindStringInInformationSchema '' --list every object, along with creation date etc
- code: FindStringInInformationSchema 'getdate'--find where the string 'getdate' appears!
- code: FindStringInInformationSchema 'gender'--find where the string 'gender' appears!
returns: >
result
**/
@SearchString VARCHAR(2000)
AS
IF CHARINDEX('%', @SearchString) = 0
SELECT @SearchString = '%' + @SearchString + '%'
SELECT--report on the routines first, name and definition
Specific_Catalog + '.' + Specific_Schema + '.' + Specific_Name
AS Qualified_Name,
LOWER(Routine_Type) + ' ' + CASE WHEN specific_name LIKE @SearchString
THEN 'name'
ELSE 'definition'
END AS Object_Type
FROM information_Schema.routines
WHERE specific_name LIKE @SearchString OR routine_Definition LIKE @SearchString
UNION ALL
SELECT--and search view definitions
Table_Catalog + '.' + Table_Schema + '.' + Table_Name,
'view definition'
FROM information_Schema.views
WHERE View_Definition LIKE @SearchString
UNION ALL
SELECT Table_Catalog + '.' + Table_Schema + '.' + Table_Name,
LOWER(table_type) + ' Name'
FROM information_Schema.tables
WHERE Table_name LIKE @SearchString
UNION ALL
SELECT Table_Catalog + '.' + Table_Schema + '.' + Table_Name + '.' + constraint_name,
LOWER(Constraint_type) + ' constraint Name'
FROM information_Schema.table_constraints
WHERE constraint_name LIKE @SearchString
UNION ALL
SELECT catalog_name + '.' + Schema_name, 'Schema'
FROM information_Schema.schemata
WHERE schema_name LIKE @SearchString AND schema_name NOT LIKE 'db_%' AND schema_name NOT LIKE 'information_Schema%'
UNION ALL
SELECT Table_Catalog + '.' + Table_Schema + '.' + Table_Name + '.' + column_name,
'TVF Column name'
FROM information_Schema.ROUTINE_COLUMNS
WHERE column_name LIKE @SearchString
UNION ALL
SELECT DISTINCT
Constraint_Catalog + '.' + constraint_Schema + '.' + constraint_Name,
'Foregn Key constraint'
FROM information_Schema.Referential_constraints
WHERE constraint_name LIKE @SearchString
UNION ALL
SELECT DISTINCT
Unique_Constraint_Catalog + '.' + Unique_constraint_Schema + '.' + Unique_constraint_Name,
'Unique constraint'
FROM information_Schema.Referential_constraints
WHERE Unique_constraint_name LIKE @SearchString
UNION ALL
SELECT Specific_Catalog + '.' + Specific_Schema + '.' + Specific_Name + '(' + Parameter_name + ')',
'routine parameter'
FROM information_schema.parameters
WHERE parameter_name <> '' AND parameter_name LIKE @SearchString
UNION ALL
SELECT Table_Catalog + '.' + Table_Schema + '.' + Table_Name + '.' + Column_Name,
'column Name'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE @SearchString
UNION ALL
SELECT Table_Catalog + '.' + Table_Schema + '.' + Table_Name + '.' + Column_Name + '.default',
'default constraint text'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_default LIKE @SearchString
...Which will give you something like this....
Learning patience: Using SMO
The most potentially powerful means of searching your database objects is by use of Server Management Objects (SMO). You can get a huge range of database and server objects with SMO, as long as you don’t mind waiting, and if you like coding in a .NET language. The problems come when you try to tackle a large database. Because you have to walk the hierarchy of SMOs objects, rather than do a set-oriented operation, you are soon in trouble if you have a lot of tables and columns. SMO provides a logical and consistent interface into a database, but it does not represent the reality of the way that this information is actually stored in the database.
SMO comes into its own as a way of quickly finding out how to access a ‘difficult’ database object. If you use SMO, and run Profiler at the same time to see what SQL is being executed, you can quickly achieve SQL MegaStar status in your development team.
Using the Object Catalog views
I love the Object Catalog views. Even Edgar Codd might have smiled. I wouldn’t suggest using them ‘raw’, as they are not as ‘unwrapped’ or denormalised as the Information Schema views. Really, the only way to survive is to have all the information tucked into a stored procedure that you’d call up to find the things you wanted. Here is the routine I use to search through as much as possible of the structure of databases. I've written it like a Lego tower, with a series of UNION ALLs so as to allow you to break it up and create your own Lego tower.
IF EXISTS ( SELECT *
FROM information_Schema.routines
WHERE specific_name = 'FindString' )
DROP PROCEDURE FindString
go
CREATE PROCEDURE FindString
/**
summary: >
This finds the string that you specify withing the name of every database object including indices, agent jobs, extended properties and parameters of routines. It searches within the text (definition) for every routine, and within the value of every extended property. it displays the full path of the database object, the object type the dates of creation and modification (sometimes that of the parent if this information isn't available) as well as the name of the parent of the object.
example:
- code: findstring '' --list every object, along with creation date etc
example:
- code: findString 'GetDate'--find where the string 'getdate' appears!
example:
- code: findstring 'b[aeiou]t'--find but, bot,bit,bet and bat!
returns: >
Qualified_Name Varchar
This is the fully-qualified name of the object so that you know where it lurks. Sometimes, the path is a bit artificial, but it should be obvious how the convention works.
Object_Type Varchar
A description of the type of object the string was found in. 'Definition' refers to the code within the routine.
created DateTime
The date that either the object or its' parent was created.
Last_Modified DateTime
The date that either the object or its' parent was last modified
Parent Varchar
The name of the parent of the object if known
**/
@SearchString VARCHAR(2000)
AS
-------------------------------------------------------------------------------------
IF CHARINDEX('%', @SearchString) = 0 --if he hasn't done in the SQL wildcard format.
SELECT @SearchString = '%' + @SearchString + '%' --add it
SELECT --firstly, we'll search the names of the basic objects
DB_NAME() + '.' + Object_Schema_name(s.[object_ID]) + '.'
+ COALESCE(p.name + '.', '') + s.name
AS [Qualified_Name],
replace(SUBSTRING(v.name, 5, 31),'cns','constraint')
+ ' name' AS Object_Type,
s.create_date AS 'Created',
s.modify_date AS 'Last_Modified',
COALESCE(p.name, '-') AS 'parent'
FROM sys.objects S --to get the objects
LEFT OUTER JOIN master.dbo.spt_values v--to get the type of object
ON s.type = SUBSTRING(v.name, 1, 2) COLLATE database_default
AND v.type = 'O9T' LEFT OUTER JOIN sys.objects p
--to get any parent object
ON s.parent_Object_ID = p.[object_ID]
WHERE s.name LIKE @SearchString--string you want to search for
AND Object_Schema_name(s.object_ID) NOT LIKE 'sys%'
UNION ALL
SELECT--and search all the names of the columns too
DB_NAME() + '.' + Object_Schema_name(s.object_ID) + '.' + '.'
+ s.name + '.' + c.name
AS [name], 'Column name' AS [object_type],
s.create_date AS 'created',
s.modify_date AS 'Last Modified',
COALESCE(s.name, '-') AS 'parent'
FROM sys.columns c INNER JOIN sys.objects S --get table data
ON c.object_ID = s.object_ID
WHERE c.name LIKE @SearchString--string you want to search for
AND Object_Schema_name(s.object_ID) NOT LIKE 'sys%'
UNION ALL
SELECT--and search all the definitions of the computed columns too
DB_NAME() + '.' + Object_Schema_name(s.object_ID)
+ '.' + s.name + ',' + c.name
AS [name],
'computed Column definition' AS [object_type],
s.create_date AS 'created',
s.modify_date AS 'Last Modified',
COALESCE(s.name, '-') AS 'parent'
FROM sys.computed_columns c INNER JOIN sys.objects S
ON c.object_ID = s.object_ID
WHERE c.definition LIKE @SearchString--string you want to search for
AND Object_Schema_name(s.object_ID) NOT LIKE 'sys$'
UNION ALL --now search the XML schema collection names
SELECT DB_NAME() + '.' + name,
'XML Schema Collection name',
create_date AS 'created',
modify_date AS 'Last Modified', '-' AS 'parent'
FROM sys.xml_schema_collections
WHERE name LIKE @SearchString
UNION ALL --and now search the names of the DDL triggers (they arent in sys.objects)
SELECT DB_NAME() + '.' + name,
LOWER(type_desc) COLLATE database_default,
create_date AS 'created',
modify_date AS 'Last Modified', '-' AS 'parent'
FROM sys.triggers
WHERE name LIKE @SearchString--string you want to search for
AND parent_class = 0--only DDL triggers
UNION ALL --and search the names of all the indexes
SELECT DB_NAME() + '.' + Object_Schema_name(p.object_ID) + '.' + p.name + '.' + i.name,
LOWER(i.type_desc) + ' index name' COLLATE database_default,
create_date AS 'created',
modify_date AS 'Last Modified', p.name AS 'parent'
FROM sys.indexes i INNER JOIN sys.objects p
ON i.object_ID = p.object_ID
WHERE i.name LIKE @SearchString--string you want to search for
AND Object_Schema_name(i.object_ID) NOT LIKE 'sys%'
AND is_primary_key = 0 AND i.type_desc <> 'HEAP'
UNION ALL--and we want to know the parameters to the routines
SELECT DB_NAME() + '.' + Object_Schema_name(s.[object_ID]) + +s.name + '(' + pa.name + ')'
AS [name],
SUBSTRING(v.name, 5, 31) + ' parameter name' AS [object_type],
s.create_date AS 'created',
s.modify_date AS 'Last Modified',
COALESCE(s.name, '-') AS 'parent'
FROM sys.parameters pa INNER JOIN sys.objects S
--to get the objects
ON pa.object_ID = S.object_ID LEFT OUTER JOIN master.dbo.spt_values v
ON s.type = SUBSTRING(v.name, 1, 2) COLLATE database_default AND v.type = 'O9T'
WHERE pa.name <> '' AND pa.name LIKE @SearchString--string you want to search for
AND Object_Schema_name(s.object_ID) NOT LIKE 'sys%'
UNION ALL--and the text (definition) of the default constraints
SELECT DB_NAME() + '.' + Object_Schema_name(d.parent_object_id)
+ '.' + OBJECT_NAME(d.parent_object_id) + '.' +c.name+ '.' + d.name,
'default constraint definition', NULL AS 'created',
NULL AS 'Last Modified',
OBJECT_NAME(d.parent_object_id) AS 'parent'
FROM sys.default_constraints d INNER JOIN sys.columns c
ON d.parent_column_id = c.column_id and d.parent_object_ID=c.object_ID
WHERE definition LIKE @SearchString
UNION ALL --the text of other table objects
SELECT DB_NAME() + '.' + Object_Schema_name(p.object_id) + '.' + OBJECT_NAME(p.object_id),
SUBSTRING(v.name, 5, 31) + ' definition' AS [object_type],
p.create_date AS 'created',
p.modify_date AS 'Last Modified', '-' AS 'parent'
FROM sys.sql_modules m INNER JOIN sys.objects p
ON m.object_ID = p.object_ID LEFT OUTER JOIN master.dbo.spt_values v
ON p.type = SUBSTRING(v.name, 1, 2) COLLATE database_default AND v.type = 'O9T'
WHERE definition LIKE @SearchString
UNION ALL--and the text of the check constraints
SELECT DB_NAME() + '.' + Object_Schema_name(d.parent_object_id) + '.'
+ OBJECT_NAME(d.parent_object_id) + '.' + c.name + '.' + d.name,
'check constraint definition', create_date AS 'created',
modify_date AS 'Last Modified',
OBJECT_NAME(d.parent_object_id) + '.' + c.name AS 'parent'
FROM sys.check_constraints d INNER JOIN sys.columns c
ON d.parent_column_id = c.column_id AND d.parent_object_ID = c.Object_ID
WHERE definition LIKE @SearchString
UNION ALL --what about the extended properties? Let's check them
SELECT DB_NAME() + '.' + Object_Schema_name(s.[object_ID]) + '.'
+ COALESCE(p.name + '.', '') + s.name
AS [name],
SUBSTRING(v.name, 5, 31) + ' Extended property' AS [object_type],
s.create_date AS 'created',
s.modify_date AS 'Last Modified',
COALESCE(p.name, '-') AS 'parent'
FROM sys.extended_properties ep INNER JOIN sys.objects s
ON s.object_ID = ep.major_ID LEFT OUTER JOIN master.dbo.spt_values v
ON s.type = SUBSTRING(v.name, 1, 2) COLLATE database_default
AND v.type = 'O9T' LEFT OUTER JOIN sys.objects p
--to get any parent object
ON s.parent_Object_ID = p.[object_ID]
WHERE CONVERT(VARCHAR(MAX), ep.value) LIKE @SearchString--string you want to search for
AND Object_Schema_name(s.object_ID) NOT LIKE 'sys%'
AND class = 1 AND minor_ID = 0--object
UNION ALL --and extended property comments on columns
SELECT DB_NAME() + '.' + Object_Schema_name(s.[object_ID]) + '.' + s.name + '.' + c.name
AS [name],
'Column Extended property' AS [object_type],
s.create_date AS 'created',
s.modify_date AS 'Last Modified',
COALESCE(c.name, '-') AS 'parent'
FROM sys.extended_properties ep INNER JOIN sys.objects s
ON s.object_ID = ep.major_ID LEFT OUTER JOIN sys.columns c
ON c.column_ID = minor_ID AND c.object_ID = ep.major_ID
WHERE CONVERT(VARCHAR(MAX), ep.value) LIKE @SearchString
AND Object_Schema_name(s.object_ID) NOT LIKE 'sys%'
AND class = 1 AND minor_ID <> 0--object
UNION ALL
--and we need to search the properties of parameters to routines
SELECT DB_NAME() + '.' + Object_Schema_name(s.[object_ID]) + '.'
+ s.name + '(' + p.name + ')'
AS [name],
SUBSTRING(v.name, 5, 31) + ' parameter x Property' AS [object_type],
s.create_date AS 'created',
s.modify_date AS 'Last Modified',
COALESCE(s.name, '-') AS 'parent'
FROM sys.extended_properties ep INNER JOIN sys.objects s
ON s.object_ID = ep.major_ID LEFT OUTER JOIN master.dbo.spt_values v
ON s.type = SUBSTRING(v.name, 1, 2) COLLATE database_default
AND v.type = 'O9T' LEFT OUTER JOIN sys.parameters p
ON p.parameter_ID = minor_ID AND p.object_ID = ep.major_ID
WHERE CONVERT(VARCHAR(MAX), ep.value) LIKE @SearchString
AND Object_Schema_name(s.object_ID) NOT LIKE 'sys%'
AND class = 2--it is a parameter
UNION ALL
--and we need to search the properties of indexes
SELECT DB_NAME() + '.' + Object_Schema_name(s.[object_ID]) + '.'
+ s.name + '.' + i.name
AS [name],
SUBSTRING(v.name, 5, 31) + ' index' AS [object_type],
s.create_date AS 'created',
s.modify_date AS 'Last Modified',
COALESCE(s.name, '-') AS 'parent'
FROM sys.extended_properties ep INNER JOIN sys.objects s
ON s.object_ID = ep.major_ID LEFT OUTER JOIN master.dbo.spt_values v
ON s.type = SUBSTRING(v.name, 1, 2) COLLATE database_default
AND v.type = 'O9T' LEFT OUTER JOIN sys.indexes i
ON i.index_ID = minor_ID AND i.object_ID = ep.major_ID
WHERE CONVERT(VARCHAR(MAX), ep.value) LIKE @SearchString
AND Object_Schema_name(s.object_ID) NOT LIKE 'sys%'
AND class = 7--it is an index
UNION ALL -- we get XML Schema Collection extended properties
SELECT DB_NAME() + '.' + sc.name + '.' + xsc.name + '.' + ep.name
AS [name], 'XML Schema Collection X property',
xsc.create_date AS 'created',
xsc.modify_date AS 'Last Modified',
xsc.name AS 'parent'
FROM sys.extended_properties ep INNER JOIN sys.xml_schema_collections xsc
ON xsc.xml_collection_id = ep.major_id INNER JOIN sys.schemas sc
ON sc.schema_ID = xsc.schema_ID
WHERE CONVERT(VARCHAR(MAX), ep.value) LIKE @SearchString
AND class = 10--all the other properties
UNION ALL --now scoop up all other extended properties (loadsa comments)
SELECT DB_NAME() + '.' + sc.name + '.' + ep.name AS [name],
LOWER(class_desc) + ' X Property',
NULL AS 'created', NULL AS 'Last Modified',
'-' AS 'parent'
FROM sys.extended_properties ep INNER JOIN sys.schemas sc
ON sc.schema_ID = ep.major_ID
WHERE CONVERT(VARCHAR(MAX), ep.value) LIKE @SearchString
AND class NOT IN (1, 2, 7, 10)--all the other properties
UNION ALL --names of CLR assemblies
SELECT DB_NAME() + '.' + name, 'CLR Assembly',
create_date AS 'created',
modify_date AS 'Last Modified', '-' AS 'parent'
FROM sys.assemblies
WHERE clr_name LIKE @SearchString OR name LIKE @SearchString
union all --almost done. We do the agent jobs too here
SELECT 'Agent' + '.' + DB_NAME() + '.' + [name] + '.'
+ step_name COLLATE database_default ,
'Agent ' + CASE TypeOfHit
WHEN 1 THEN 'job description'
WHEN 2 THEN 'job name'
WHEN 3 THEN 'step name'
WHEN 4 THEN 'job contents'
ELSE 'impossible'
END,
date_created, date_modified,
[name] COLLATE database_default
FROM
(SELECT
Job.name, step_name, date_created,
Date_modified,
CASE WHEN Job.Description LIKE @SearchString COLLATE database_default
THEN 1 --job description
WHEN Job.name LIKE @SearchString COLLATE database_default THEN 2--jpb name
WHEN step_name LIKE @SearchString COLLATE database_default THEN 3--step name
WHEN Step.command LIKE @SearchString COLLATE database_default THEN 4 --job contents
ELSE 0 END AS TypeOfHit
FROM MSDB.dbo.sysJobs Job INNER JOIN MSDB.dbo.sysJobSteps Step
ON Job.Job_Id = Step.Job_Id
WHERE Database_name LIKE DB_NAME() COLLATE database_default
) Oursteps
WHERE typeOfHit > 0
...which will give you something like...
Using System Tables
Now, you’ll see that this will only work with SQL Server 2005 or 2008. This shouldn’t stop you dead in your tracks if you are stuck on SQL Server 2000 or older, because there are still ways to do it. Instead of using the catalog views, you have to use the system tables. The mapping between the System tables and the System catalog views are all listed for you on Books on Line here. The big problem is that the code of each routine is stored in as many NVARCHAR(4000) chunks as is required to hold the entire definition, which complicates the code. A solution that I use for searching through the definitions of routines in SQL Server 2000 database is given here http://www.simple-talk.com/community/blogs/philfactor/archive/2006/06/03/854.aspx but I wouldn’t want to bulk up this article with a SQL Server 2000 version of the FindString code.
In Conclusion
I use all the methods I’ve described for perusing the structures in databases. If I want quick information in the course of writing a database routine, I use SQL Search. When I’m doing some intensive refactoring, I’ll use the full Build script, but always in a Programmers Text Editor rather than SSMS. (I once hit Execute instead of ‘open file’, and deleted a database). I use Information Schema Views wherever possible as they are reasonably future-proof and open-standard. I use Object Catalog views when I can’t get what I want from Information_Schema views, and use System tables when I’m having to use older versions of SQL Server.
I realise, with a guilty start, that this article has been slightly self-centred in that I’ve talked about my own preferences and work practices. If you use a different approach to searching your database DDL scripts that I haven't mentioned, then I’d be fascinated to hear about in it a comment on this article.
