How to search all columns of all tables in a database for a keyword? (SQL Server)


To totally unlock this section you need to Log-in


Login

This stored procedure accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database. Feel free to extend this procedure to search other datatypes.

The output of this stored procedure contains two [gs column]s:

  • The table name and column name in which the search string was found
  • The actual content/value of the column (Only the first 3630 characters are displayed)
  • Here's a word of caution, before you go ahead and run this procedure. Though this procedure is quite quick on smaller databases, it could take hours to complete, on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (you could use the locking hint NOLOCK to reduce any locking).

    It is efficient to use Full-Text search feature for free text searching, but it doesn't make sense for this type of ad-hoc requirements.

    [tweet]

    Create this [gs procedure] in the required database and here is how you run it:

    --To search all columns of all tables in Pubs database for the keyword "Computer"
    EXEC SearchAllTables 'Computer'
    GO

    Here is the complete stored procedure code:

    Heelpbook Staff: this [gs stored procedure] has been tested successfully even on SQL Server 2005/2008 environment;


    To totally unlock this section you need to Log-in


    Login

    1 thought on “How to search all columns of all tables in a database for a keyword? (SQL Server)”

    Comments are closed.