Sql-server – Finding the Table and Column Based on a Known Value

sql serversql-server-2012

I've inherited a rather large SQL Server 2012 database — about 2TB, tens of millions of rows in some tables, and about 350 different tables. I have basically no references nor documentation.

There's a type of file I need to generate which contains information stored somewhere in this database. I have one example of such file, so I have concrete values, but no idea as to where each of its fields came from.

For instance, I have an entry in the output file labeled UUID which has a value of a03dc6109c6f53ce93203f1b85c7d31d. After much uncomfortable digging, I found a column in one of the tables called "custom_value_1" with the value a03dc610-9c6f-53c-e932-03f1b85c7d31d, which I'm pretty sure semantically corresponds to what I wanted. But I have many such values to find, and it seems pretty unlikely that I'll be able to stumble across them all.

So how can I search all the columns across all the tables in a database for a particular value? I'd love to be able to say something like:

SELECT * FROM * WHERE * = 'HB194';

and have it search every column (skipping those of inapplicable types; no timestamps if I'm searching for a string) of every table for the value 'HB194'. Obviously something more complex will need to be employed, but I don't think I have the time (nor the fortitude) to manually craft a specific select query for each of the 350 tables for each of the dozen or so values I'm looking for.

Best Answer

From my post about this issue here (see #1):

DECLARE 
  @Search1 NVARCHAR(4000) = N'%a03dc6109c6f53ce93203f1b85c7d31d%',
  @Search2 NVARCHAR(4000) = N'%a03dc610-9c6f-53c-e932-03f1b85c7d31d%',
  @s NVARCHAR(MAX) = N'';

;WITH t AS (
    SELECT t.[object_id], [table] = t.name, [schema] = s.name
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id]
    WHERE EXISTS (
        SELECT 1 FROM sys.columns
          WHERE [object_id] = t.[object_id]
          AND (system_type_id IN (35,99) -- text, ntext
          OR (system_type_id IN (167,175,231,239) -- (n)(var)(char)
            AND (max_length = -1 OR max_length >=32) -- max or >= LEN(@Search1)
             )))
)
SELECT @s = @s + N'SELECT N''' 
    + REPLACE([schema],'''','''''') + '.' 
    + REPLACE([table], '''','''''') + ''',* 
 FROM ' + QUOTENAME([schema]) + '.' + QUOTENAME([table]) + '
 WHERE ' + STUFF((SELECT '
 OR ' + QUOTENAME(name) + ' LIKE ' + CASE 
      WHEN system_type_id IN (99,231,239) 
      THEN 'N' ELSE '' END
      + '''' + @Search1 + '''' -- run again with @Search2
    FROM sys.columns
    WHERE [object_id] = t.[object_id]
    AND system_type_id IN (35,99,167,175,231,239)
    ORDER BY name
    FOR XML PATH(''), TYPE
).value(N'.[1]', N'nvarchar(max)'),1,6,'') + ';

'
FROM t;

PRINT @s;
-- EXEC sp_executesql @s;
Related Question