vzFeEkRC

1Search all columns of a table for a value?
2IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchOneTable]') AND type in (N'P', N'PC'))
3DROP PROCEDURE [dbo].[SearchOneTable]
4GO
5
6SET ANSI_NULLS ON
7GO
8
9SET QUOTED_IDENTIFIER ON
10GO
11
12CREATE PROC [dbo].[SearchOneTable]
13(
14    @SearchStr nvarchar(100) = 'A',
15    @TableName nvarchar(256) = 'dbo.Alerts'
16)
17AS
18BEGIN
19
20    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
21
22    --SET NOCOUNT ON
23
24    DECLARE @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
25    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
26    --SET @SearchStr2 = QUOTENAME(@SearchStr, '''') --exact match
27    SET @ColumnName = ' '
28
29
30        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
31        BEGIN
32            SET @ColumnName =
33            (
34                SELECT MIN(QUOTENAME(COLUMN_NAME))
35                FROM    INFORMATION_SCHEMA.COLUMNS
36                WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
37                    AND TABLE_NAME  = PARSENAME(@TableName, 1)
38                    AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
39                    AND QUOTENAME(COLUMN_NAME) > @ColumnName
40            )
41
42            IF @ColumnName IS NOT NULL
43            BEGIN
44                INSERT INTO #Results
45                EXEC
46                (
47                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
48                    FROM ' + @TableName + ' (NOLOCK) ' +
49                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
50                )
51            END
52        END 
53    SELECT ColumnName, ColumnValue FROM #Results
54END
55
56
57GO
58	
59DECLARE @SearchTerm NVARCHAR(32) = 'foo';
60
61DECLARE @TableName NVARCHAR(128) = NULL;
62
63SET NOCOUNT ON;
64
65DECLARE @s NVARCHAR(MAX) = '';
66
67WITH [tables] AS
68(
69    SELECT [object_id] 
70        FROM sys.tables AS t
71        WHERE (name = @TableName OR @TableName IS NULL)
72        AND EXISTS
73        (
74            SELECT 1 
75                FROM sys.columns
76                WHERE [object_id] = t.[object_id]
77                AND system_type_id IN (35,99,167,175,231,239)
78        )
79)
80SELECT @s = @s + 'SELECT ''' 
81    + REPLACE(QUOTENAME(OBJECT_SCHEMA_NAME([object_id])),'''','''''')
82    + '.' + REPLACE(QUOTENAME(OBJECT_NAME([object_id])), '''','''''')
83    + ''',* FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
84    + '.' + QUOTENAME(OBJECT_NAME([object_id])) + ' WHERE ' + 
85    (
86        SELECT name + ' LIKE ' + CASE 
87            WHEN system_type_id IN (99,231,239) 
88            THEN 'N' ELSE '' END
89            + '''%' + @SearchTerm + '%'' OR '
90        FROM sys.columns
91        WHERE [object_id] = [tables].[object_id]
92        AND system_type_id IN (35,99,167,175,231,239)
93        ORDER BY name
94        FOR XML PATH(''), TYPE
95).value('.[1]', 'NVARCHAR(MAX)') + CHAR(13) + CHAR(10)
96FROM [tables];
97
98SELECT @s = REPLACE(@s,' OR ' + CHAR(13),';' + CHAR(13));
99
100/*
101    make sure you use Results to Text and adjust Tools / Options / 
102    Query Results / SQL Server / Results to Text / Maximum number
103    of characters if you want a chance at trusting this output 
104    (the number of tables/columns will certainly have the ability
105    to exceed the output limitation)
106*/
107
108SELECT @s;
109-- EXEC sp_executeSQL @s;