SQL Server Text Search

yuvalsol

4.29/5 (21 votes)

Jul 28, 2015

CPOL

7 min read

23821

560

A stored procedure that searches for a text in the SQL Server database (DDL)

SQL Server Text Search at GitHub.

Download source - 3.1 KB

Introduction

Did you ever need to search for a string in your stored procedures? Or to search for a column name in all your user tables? There are many instances when you want to search your database for a given text during the course of programming. You might have a paid tool that does exactly that (Redgate products come to mind), but of course you have to pay for it and it usually opens a new tab for search results which I hate.

What I was looking for is a more natural solution inherent to SSMS, meaning I can execute it in SSMS and get the results right below. At first, I started with a script. When in need, I pulled it up to SSMS, changed the value of the search string and executed. That got tired very quickly and eventually the script evolved into a stored procedure with much more options than a script could contain without being cumbersome. For every SQL Server database that I work on, I create this search stored procedure and it's there whenever I need it.

While this article explains the various aspects of this search stored procedure with plenty of examples, I do find that there is nothing like trying it out for yourself. I strongly recommend that you create sp_searchtext in your local AdventureWorks database and experiment with it.

Usage

First, here's a list of sp_searchtext parameters:

@Search_String - Text string to search for in the current database
@Xtypes - Comma-delimited list of object types that include and exclude what objects to search in
@Case_Sensitive - Whether the search is case sensitive or not. The default is case insensitive
@Name - Include results that have a name that includes @Name
@Schema - Include results that have a schema name that includes @Schema
@Refine_Search_String - Second search string
@Exclude_Name - Exclude results that have a name that includes @Exclude_Name
@Exclude_Schema - Exclude results that have a schema name that includes @Exclude_Schema
@Exclude_Search_String - Exclude results that have @Exclude_Search_String

There are no wildcards in @Search_String parameter or any other string parameters. All wildcard characters are treated as literal characters, so % and _ are just regular characters like any other one.

Xtype is a type of object, denoted by a code of char(2). MSDN has a list of all types. You don't have to memorise it all by heart. You'll remember the useful ones as you go along. Here are the most common ones:

U = User table
V = View
P = Stored procedure
FN = Scalar function
TF = Table function
TT = Table type

What sp_searchtext results are? Each row is one object (table, view, stored procedure, function, ...) in the database that the string was found with it. The columns are:

schema - The schema of the object
name - The name of the object
type - The type of the object
type_desc - The type's description of the object
sp_helptext - A script that gets the full text of the object
sp_help - A script that gets various details on the object. Very useful with user tables
sp_columns - A script that gets the list of the object's columns
sysobjects - A script that gets the object from sys.sysobjects table
sp_searchtext - A script to search for the name of the object

Some scripts are not applicable for some type of objects so they will be empty in that case.

Simple Search

exec sp_searchtext 'BusinessEntityID'      -- all objects with 'BusinessEntityID'
exec sp_searchtext 'BusinessEntityID',p    -- stored procedures with 'BusinessEntityID'
exec sp_searchtext 'BusinessEntityID','-p' -- everything except stored procedures

The first query searches for 'BusinessEntityID' in all objects. Since there's no type filtering, it'll return user tables, views, stored procedures, functions, PKs & FKs and more. The second query will return only stored procedures that have 'BusinessEntityID' case insensitive in them. The third query will return all types of objects except stored procedures that have 'BusinessEntityID' case insensitive in them.

exec sp_searchtext ''   -- all objects
exec sp_searchtext '',u -- all user tables
exec sp_searchtext '',p -- all stored procedures

These queries return everything or everything with the specified object type. They're not very productive but just know you can do that.

Search Multiple Types

exec sp_searchtext 'BusinessEntityID','-f,-pk,-tr'     -- everything except PKs, FKs, Triggers
exec sp_searchtext 'BusinessEntityID','u,v,p,fn,tf,tt' -- "interesting" objects

The first query filters out primary keys, foreign keys and triggers and returns every other object with 'BusinessEntityID'. The second query is looking for specific objects with 'BusinessEntityID': user tables, views, stored procedures, scalar & table functions, table types.

Search User Tables

exec sp_searchtext 'BusinessEntityID',u
exec sp_searchtext 'BusinessEntityID',u,@Schema='Person'

The first query searches for all user tables that have at least one column with 'BusinessEntityID' in its name. The second query searches for all user tables, within the 'Person' schema, that have at least one column with 'BusinessEntityID' in its name.

Filter by Name & Schema

exec sp_searchtext 'BusinessEntityID',@Name='Employee'
exec sp_searchtext 'BusinessEntityID',p,@Name='Employee'
exec sp_searchtext 'BusinessEntityID',p,@Name='Employee',@Schema='HumanResources'

The first query searches for all objects with 'BusinessEntityID' and with 'Employee' in their name. The second query searches for all stored procedures with 'BusinessEntityID' and with 'Employee' in their name. The third query searches for all stored procedures with 'BusinessEntityID' and with 'Employee' in their name and within the 'HumanResources' schema.

Case Sensitive & Insensitive Search

-- all objects with 'BusinessEntityID' case insensitive
exec sp_searchtext 'BusinessEntityID'
exec sp_searchtext 'BUSINESSENTITYID'
exec sp_searchtext 'BUSINESSENTITYID','',0

-- all objects with 'BUSINESSENTITYID' case sensitive
exec sp_searchtext 'BUSINESSENTITYID','',1
exec sp_searchtext 'BUSINESSENTITYID',@Case_Sensitive=1

The first three queries are the same. They return all objects with 'BusinessEntityID' case insensitive. The next two queries return all objects with 'BUSINESSENTITYID' case sensitive. Since there is no object with 'BUSINESSENTITYID', they both return 0 results.

When the search is set to case sensitive, then all the string arguments are treated as case sensitive: @Search_String, @Name, @Schema, @Refine_Search_String, ...

Refine & Exclude Search

exec sp_searchtext 'BusinessEntityID',p,@Refine_Search_String='Update'

exec sp_searchtext 'BusinessEntityID',p,
    @Exclude_Name='Update',
    @Exclude_Schema='HumanResources',
    @Exclude_Search_String='Update'

The first query search for stored procedures with 'BusinessEntityID' and with 'Update'. The @Refine_Search_String parameter lets you search for a second string and refine the returned results.

The second query utilizes all three exclude arguments. The query searches for stored procedures with 'BusinessEntityID' that don't have 'Update' in their name, don't have 'HumanResources' in their schema name, and don't have the string 'Update' in their text.

Implementation

The system tables that sp_searchtext uses are:

sys.sysobjects - The database user-defined objects
sys.schemas - The database schemas
sys.columns - The database columns (User Tables, Views, Table-values Functions, ...)
sys.table_types - The properties of the database's table types
sys.syscomments - The SQL definition statements of the database objects

The most interesting column is the text column in sys.syscomments. The text column holds the text definition statement of an object. This is where you would find the text of stored procedures, views and other objects. The text column is defined as nvarchar(4000), so if the definition statement of an object, for example a stored procedure, is more than 4000 characters by length, there would be several entries in sys.syscomments that are associated with that object. So, an object might have several entries in sys.syscomments and the colid column holds their order.

This is the main script at the heart of the stored procedure. The script makes a link between an object in sys.sysobjects and its SQL definitions in sys.syscomments (if it has any). Once you get that essential information, type, name, schema & text, you can start testing it for other things.

-- objects
select distinct 
    so.id,              -- object id
    so.xtype,           -- object type
    [schema] = ss.name, -- object schema
    so.name,            -- object name
    sc.colid,           -- order of object definitions
    sc.[text]           -- object definition
from sys.sysobjects so
inner join sys.schemas ss on so.[uid] = ss.[schema_id]
left outer join sys.syscomments sc on so.id = sc.id
order by xtype, [schema], name, colid

Since columns do not appear in sys.sysobjects, the second script augment the previous one by taking the columns in sys.columns and linking them with their owners in sys.sysobjects, user tables, views and such. Now if we are searching for a column name, we know what object it belongs to.

-- columns
select distinct 
    so.id,               -- object id
    so.xtype,            -- object type
    [schema] = ss.name,  -- object schema
    so.name,             -- object name
    c.column_id,         -- columns order
    column_name = c.name -- column name
from sys.columns c
inner join sys.sysobjects so on c.[object_id] = so.id
inner join sys.schemas ss on so.[uid] = ss.[schema_id]
order by xtype, [schema], name, column_id

Last word about the implementation of the case sensitive search. If you remember your SQL Server collations, then you know that a collation is a set of rules that governs how a set of characters are sorted and compared. A set of characters can be a language (e.g. Greek) or an alphabet (e.g. Latin). The set of rules include case sensitivity (A vs. a), Accent sensitivity (a vs. á), Kana Sensitivity (Hiragana vs. Katakan Japanese kana characters) and Width sensitivity (single-byte vs. double-byte). There are several levels of collation. The out-most is server, then database, then column and last is a SQL expression. the inner one trumps up the outer one. The way I implemented a case sensitive search is to collate every LIKE clause with Latin1_General_BIN.

-- part of a WHERE clause
so.name collate Latin1_General_BIN like '%' + @Search_String + '%' collate Latin1_General_BIN
sc.text collate Latin1_General_BIN like '%' + @Search_String + '%' collate Latin1_General_BIN

The BIN in Latin1_General_BIN stands for Binary. A binary collation sorts and compares data based on the bit pattern for each character. Since every character has a different binary value from any other character, that implies case sensitive (and accent sensitive) sorting.

History

03/09/2015: Bugfix. @Exclude_Search_String didn't work properly.