datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to search all tables in database for text keyword?

Execute the following Microsoft SQL Server 2008 T-SQL database scripts in Management Studio Query Editor to create a stored procedure for searching all tables and all columns in a database for text, keyword or wildcard expression. Stored procedure execution examples listed.

-- SQL search all columns in all tables for wildcard - search all tables in database
-- SQL search database for text - SQL Server search all tables in database for keyword

-- Search all text & xml columns of all tables for keyword - information_schema columns

-- Searching for text / data in SQL database without full-text search

-- Search all columns of all tables in a database for a keyword - list all table columns
USE AdventureWorks2008;
GO
-- T-SQL create stored procedure for searching all tables in database
CREATE PROC sprocSearchDatabaseForText

           @WildcardSearchTerm NVARCHAR(64)

AS

  /* The following algorithm builds on metada from INFORMATION_SCHEMA views.

 

It loops through all tables in the database

by using an outer cursor WHILE loop.

 

Within the table cursor loop, it loops through all string/text/xml columns

by using an inner cursor WHILE loop.

 

It executes a dynamic SQL search for the wildcard keyword in each column

within the nested cursor loop.

*/

  BEGIN

    SET NOCOUNT  ON

    

    DECLARE  @OutputLength VARCHAR(4) = '256',    -- SQL Server 2008+ feature

             @NolockOption CHAR(8)    = '(NOLOCK))'

    

    DECLARE  @DynamicSQL        NVARCHAR(MAX),

             @SchemaTableName   NVARCHAR(256),

             @SchemaTableColumn NVARCHAR(128),

             @SearchWildcard    NVARCHAR(128)

    

    SELECT @SearchWildcard = CHAR(39)+REPLACE(@WildcardSearchTerm,CHAR(39),

                             CHAR(39)+CHAR(39))+CHAR(39)

    

    PRINT @SearchWildcard

    

    DECLARE  @SearchResults  TABLE(

                                   SchemaTableColumn NVARCHAR(384),

                                   TextWithKeyword   NVARCHAR(MAX)

                                   )

    

    DECLARE curAllTables CURSOR  FOR

    SELECT   QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) AS SchemaTable

    FROM     INFORMATION_SCHEMA.TABLES

    WHERE    TABLE_TYPE = 'BASE TABLE'

             AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +

                 QUOTENAME(TABLE_NAME)), 'IsMSShipped') != 1

    ORDER BY SchemaTable

    

    OPEN curAllTables

    

    FETCH NEXT FROM curAllTables

    INTO @SchemaTableName

    

    WHILE (@@FETCH_STATUS = 0) -- Outer cursor loop

      BEGIN

        PRINT @SchemaTableName

        

        SET @SchemaTableColumn = ''

        

        DECLARE curAllColumns CURSOR  FOR -- Nested cursor

        

        SELECT   QUOTENAME(COLUMN_NAME)

        FROM     INFORMATION_SCHEMA.COLUMNS

        WHERE    TABLE_NAME = PARSENAME(@SchemaTableName,1)

                 AND TABLE_SCHEMA = PARSENAME(@SchemaTableName,2)

                 AND DATA_TYPE IN ('varchar','nvarchar','char','nchar','xml')

        ORDER BY ORDINAL_POSITION

        

        OPEN curAllColumns

        

        FETCH NEXT FROM curAllColumns

        INTO @SchemaTableColumn

        

        WHILE (@@FETCH_STATUS = 0) -- Inner cursor loop (nested cursor while)

          BEGIN

            PRINT '  ' + @SchemaTableColumn

            

            SET @DynamicSQL = 'SELECT ''' + @SchemaTableName + '.' +

                @SchemaTableColumn + ''', LEFT(CONVERT(nvarchar(max),' +

                @SchemaTableColumn + '),' + @OutputLength + ')  FROM ' +

                @SchemaTableName + ' ' +

                @NolockOption + ' WHERE CONVERT(nvarchar(max),' +

                @SchemaTableColumn + ') LIKE ' + @SearchWildcard

            

            INSERT INTO @SearchResults

            EXEC sp_executesql

              @DynamicSQL

            

            FETCH NEXT FROM curAllColumns

            INTO @SchemaTableColumn

          END  -- Inner cursor loop

        

        CLOSE curAllColumns

        DEALLOCATE curAllColumns

        

        FETCH NEXT FROM curAllTables

        INTO @SchemaTableName

      END  -- Outer cursor loop

    

    CLOSE curAllTables

    DEALLOCATE curAllTables

    

    SELECT DISTINCT SchemaTableColumn,

                    TextWithKeyword

    FROM   @SearchResults

  END

GO

------------

 

-- Test searching entire database for keywords

EXEC sprocSearchDatabaseForText  '%Hamilton%'

/* Partial results

 

SchemaTableColumn                   TextWithKeyWord

[Person].[Address].[AddressLine1]   9215 Hamilton Ave.

[Person].[Address].[AddressLine1]   9719 Hamilton Ave

[Person].[Address].[AddressLine1]   9732 Hamilton Ct.

[Person].[Address].[AddressLine1]   9738 Hamilton Drive

[Person].[Person].[LastName]        Hamilton

*/

EXEC sprocSearchDatabaseForText  'Sánchez'

/* SchemaTableColumn                TextWithKeyWord

[Person].[Person].[LastName]        Sánchez

*/

EXEC sprocSearchDatabaseForText  '%O''Donnell%'

/* SchemaTableColumn                TextWithKeyWord

[Person].[Person].[LastName]        O'Donnell

*/

-- XML column search example

EXEC sprocSearchDatabaseForText  '%Programming%'

/*

SchemaTableColumn TextWithKeyWord

[HumanResources].[JobCandidate].[Resume]  <ns:Resume xmlns:ns="http://schemas.microsoft.com/
sqlserver/2004/07/adventure-works/Resume"><ns:Name><ns:Name.Prefix>Mr.</ns:Name.Prefix><ns:Name.First>Krishna<
/ns:Name.First><ns:Name.Middle/><ns:Name.Last>Sunkammurali</ns:Name.Last><
ns:Name.Suffix/></ns:N

*/

 

------------

 

Related articles:

How to search every table and field in a SQL Server Database

Searching in all tables and columns of a database

 

Exam Prep 70-461
Exam 70-461