SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

String Search

By Sean Smith,

It's been rare in my career that I've needed to find a string of text within any and all of the many possible tables of a database. For the few occasions when I did, I would just go to Google, find something that worked, run it, and be done.

More recently my need for this type of script has increased, and I began to notice that most of the code out there was either slow (even when doing an extremely small search), didn't return all of the fields I wanted to see in the output, didn't search all of the character data types I was interested in, gave limited control / filter options (if any), and / or needed to be run from within the context of the database that was being searched (AKA: not feasible to make it a stored procedure unless you are only ever going to search within a specific database or deploy the procedure to all existing and future databases).

Hence my inspiration for creating my own stored procedure which allows one to search a multitude of data types in any specified database and includes some optional input parameter options / filters.

NOTE: Depending on your environment and needs (number of tables / columns / search criteria, etc.) these types of queries have the possibility to run long and have a performance impact on the system. Always use common sense when running queries such as these and limit your search via the input parameter options rather than casting as wide a net as possible to see what you will get back as results (especially in a production or shared environment).

The procedure accepts the following input parameters:

  • @Search_String: Text string you wish to search for
  • @Database_Name: Database whose table(s) and / or view(s) you wish to search against
  • @Object_Types: Object types to search against (U for tables, V for views; default is both)
  • @Data_Types: Data types to search against (default includes CHAR, NCHAR, NTEXT, NVARCHAR, TEXT, VARCHAR, and XML)
  • @Table_Max_Rows: Limits the search to objects of a maximum row count, avoiding scans against large datasets (default is no limit, though using this option will remove NTEXT, TEXT, XML, and views from the search)
  • @Column_Max_Length: Limits the search to columns of a maximum defined data length; handy for when you only want to search against short description fields (default is no limit)
  • @Creation_Source: Search objects created by users (U - default value), SQL Server (S), or both (B)

The procedure will output the following values:

  • object_type: Indicates if the object containing the search result is a table or view
  • is_ms_shipped: Indicates if the object was created by SQL Server
  • data_type: Indicates the data type of the column containing the search result
  • data_length: Indicates the column length, as defined in the database, containing the search result
  • database_name: Indicates the database name the search was performed against
  • schema_name: Indicates the schema name the search result was found in
  • object_name: Indicates the object name the search result was found in
  • column_name: Indicates the column name the search result was found in
  • column_data: Full contents of the column containing the search criteria match
  • column_data_xml: When the contents of the column_data field are XML then this column will return the same data but converted to the XML data type
  • occurrences: Count of the total number of occurrences of the full content of the column_data field

To execute the stored procedure you must at least populate the @Search_String and @Database_Name input parameters:

EXECUTE dbo.usp_String_Search

     @Search_String = N'random text'
    ,@Database_Name = N'my_database'

You can also use any or all of the optional input parameters to limit the scope of your search and produce results quicker while lessening the load on your server (recommended):

EXECUTE dbo.usp_String_Search

     @Search_String = N'random text'
    ,@Database_Name = N'my_database'
    ,@Object_Types = N'U'
    ,@Table_Max_Rows = 10000
    ,@Column_Max_Length = 50
    ,@Creation_Source = N'U'

Any friendly feedback is always welcome. Enjoy!

Total article views: 5813 | Views in the last 30 days: 3
Related Articles

Object Search

Procedure to search any database (or a combination of databases, including all) for a specific strin...


Search objects in Database

Use this script to search objects in a Database. Also used to check a tables column types and detail...


Searching complete database

Searching complete database


Search for objects in a database

Find in a flexible way different type of objects on databases.


Search All SQL Script Objects in All Databases for Text

Edit the @SearchText and you can enter any text you would like to search for in SQL Objects.