جستجو در همه جدول‌ها در SQL Server

 1DECLARE @SearchStr nvarchar(100)
 2SET @SearchStr = '## YOUR STRING HERE ##'
 3 
 4CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 5 
 6SET NOCOUNT ON
 7 
 8DECLARE @TableName nvarchar(256),
 9	@ColumnName nvarchar(128),
10	@SearchStr2 nvarchar(110)
11
12SET @TableName = ''
13
14SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
15 
16WHILE @TableName IS NOT NULL
17BEGIN
18    SET @ColumnName = ''
19    SET @TableName = 
20    (
21        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
22        FROM     INFORMATION_SCHEMA.TABLES
23        WHERE         TABLE_TYPE = 'BASE TABLE'
24            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
25            AND    OBJECTPROPERTY(
26                    OBJECT_ID(
27                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
28                         ), 'IsMSShipped'
29                           ) = 0
30    )
31 
32    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)         
33    BEGIN
34        SET @ColumnName =
35        (
36            SELECT MIN(QUOTENAME(COLUMN_NAME))
37            FROM     INFORMATION_SCHEMA.COLUMNS
38            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
39                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
40                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
41                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
42        )
43 
44        IF @ColumnName IS NOT NULL        
45        BEGIN
46            INSERT INTO #Results
47            EXEC
48            (
49                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
50                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
51            )
52        END
53    END   
54END
55 
56SELECT ColumnName, ColumnValue FROM #Results
57 
58DROP TABLE #Results

 1CREATE PROC SearchAllTables
 2(
 3    @SearchStr nvarchar(100)
 4)
 5AS 
 6BEGIN
 7    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 8 
 9    SET NOCOUNT ON
10 
11    DECLARE @TableName nvarchar(256), 
12		@ColumnName nvarchar(128),
13		@SearchStr2 nvarchar(110)
14		
15    SET @TableName = ''
16    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
17 
18    WHILE @TableName IS NOT NULL    
19    BEGIN
20        SET @ColumnName = ''
21        SET @TableName = 
22        (
23            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
24            FROM     INFORMATION_SCHEMA.TABLES
25            WHERE         TABLE_TYPE = 'BASE TABLE'
26                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
27                AND    OBJECTPROPERTY(
28                        OBJECT_ID(
29                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
30                             ), 'IsMSShipped'
31                               ) = 0
32        )
33 
34        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)             
35        BEGIN
36            SET @ColumnName =
37            (
38                SELECT MIN(QUOTENAME(COLUMN_NAME))
39                FROM     INFORMATION_SCHEMA.COLUMNS
40                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
41                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
42                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
43                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
44            )
45     
46            IF @ColumnName IS NOT NULL            
47            BEGIN
48                INSERT INTO #Results
49                EXEC
50                (
51                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
52                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
53                )
54            END
55        END   
56    END
57 
58    SELECT ColumnName, ColumnValue FROM #Results
59    DROP TABLE #Results
60END