Tìm kiếm dữ liệu theo từ khóa .

-- 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

*/

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

Source : sqlusa