Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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).

The procedure will output the following values:

  • object_type: Indicates if the object containing the search result is a table or view.
  • 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'
    ,@Data_Types = N'CHAR, NCHAR, NVARCHAR, VARCHAR'
    ,@Table_Max_Rows = 10000
    ,@Column_Max_Length = 50

Any friendly feedback is always welcome. Enjoy!

Total article views: 3118 | Views in the last 30 days: 58
 
Related Articles
FORUM
SCRIPT

Object Search

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

SCRIPT

Find a text string in any column in a database

Find a string in any "string" (char, varchar, nchar etc) column in the database.

FORUM

Searching complete database

Searching complete database

FORUM

search string help needed...

search string help needed...

Tags
character    
find    
lookup    
search    
string    
text    
word    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones