Click here to monitor SSC
  • Av rating:
  • Total votes: 22
  • Total comments: 8
Phil Factor

Finding Stuff in SQL Server Database DDL

04 February 2010

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

        ELSEEND 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 >

...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.

Phil Factor

Author profile:

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career. See also :

Google + To translate this article...

Search for other articles by Phil Factor

Rate this article:   Avg rating: from a total of 22 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: OBJECT_DEFINITION
Posted by: David McKinney (not signed in)
Posted on: Monday, February 08, 2010 at 2:05 AM
Message: Hi Phil,

Any reason why object_definition didn't get a mention? I generally use the following on SQL2005. I keep it in a 'snippet' with sql prompt and it pops up when I type 'find'. (I work with case sensitive databases, hence the collate.)

DECLARE
@find NVARCHAR(4000)

SELECT  @find = '' ;
WITH  definitions
        
AS (SELECT  so.name, OBJECT_DEFINITION(id) AS TEXT
            FROM    
sys.sysobjects so
WHERE name NOT LIKE 'sys%'
          
)
  
SELECT  TEXT, name,
          
SUBSTRING(TEXT,
                    
CHARINDEX(@find,
                              
TEXT COLLATE Latin1_General_CI_AI) - 25,
                    
50) AS context
  
FROM    definitions
  
WHERE   CHARINDEX(@find, TEXT COLLATE Latin1_General_CI_AI) <> 0




Subject: SQL Search?
Posted by: Dave (view profile)
Posted on: Monday, February 08, 2010 at 2:37 AM
Message: Perhaps I've missed the point here but didn't Red Gate recently release a free awesome search tool called SQL Search (http://www.red-gate.com/products/SQL_Search/index.htm)?

Subject: re: SQL Search?
Posted by: Phil Factor (view profile)
Posted on: Monday, February 08, 2010 at 3:30 AM
Message: Correct me by all means, but I have a vague feeling I mentioned it!

Subject: Re: Object_Definition.
Posted by: Phil Factor (view profile)
Posted on: Monday, February 08, 2010 at 5:45 AM
Message: Nice bit of code, David. Thanks for that.

Yes, OBJECT_Definition, and ObjectProperty are powerful magic, when you are querying your structural metadata of just your Schema-based objects. I should have mentioned these 'help' functions such as object_Name(), object_ID(), Object_Schema_Name(), OBJECT_Definition(), objectProperty(), columnProperty() and TypeProperty(), but to do them justice would have bulked the article up a bit.

Subject: Re: Template Searches in TSQL
Posted by: Phil Factor (view profile)
Posted on: Tuesday, February 09, 2010 at 4:53 AM
Message: You can also do quick searches using SSMS templates as I mentioned in the article, similar to David's 'snippet' trick, but not as slickly as you can with SQL Prompt. Go to the template pane, right-click to create a new template, right-click on the new template to edit it, and paste in this code. Then hit save. You will then  have a search template. You can either click 0on a template to create a new pane or drag/drop to put the code in your existing pane. Then hit Cntl/Shift/M.  You'll note that I've merely adapted a 'lego' section of the code in the article. (I like to know what type of  object it was, etc.)

  SELECT  DB_NAME() + '.' + Object_Schema_name(p.OBJECT_ID)
                         +
'.' + OBJECT_NAME(p.OBJECT_ID) AS [Name],
          
SUBSTRING(v.name, 5, 31) + ' definition' AS [object_type],
          
'...' + SUBSTRING(definition,
              
CHARINDEX('<Code To Search For, Varchar(100),>', definition) - 20,
              
LEN('<Code To Search For, Varchar(100),>') + 40) + '...' AS Context
  
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   CHARINDEX('<Code To Search For, Varchar(100),>', definition) > 0



Subject: first proc problem
Posted by: Dennis Cronin (not signed in)
Posted on: Thursday, February 11, 2010 at 8:42 AM
Message: I've tried copying your first proc, adding an END GO and EXEC statement. But I get the error, 'Msg 102, Level 15, State 1, Procedure FindStringInInformationSchema, Line 168
Incorrect syntax near 'END'.

Do you see the problem?

Subject: Re: First Proc Problem
Posted by: Phil Factor (view profile)
Posted on: Thursday, February 11, 2010 at 11:40 AM
Message: The source to the two procs can be downloaded from the speech-bubble at the top of the article

Subject: Re: Article
Posted by: Paul White NZ (view profile)
Posted on: Thursday, March 11, 2010 at 3:52 AM
Message: SQL Server 2008 improves things markedly - see http://msdn.microsoft.com/en-us/library/ms345449.aspx (Understanding SQL Dependencies) in Books Online.

The new system views:

sys.sql_expression_dependencies
sys.dm_sql_referencing_entities
sys.dm_sql_referenced_entities

...represent the best solution from Microsoft so far, rivalling third-party solutions to this long-standing problem.

Paul

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.