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.

  • That value you show in the question is typically referred to as a GUID, or Globally Unique Identifier (uniqueidentifier in SQL Server parlance). Are all the values you looking for a GUID?
    – Hannah Vernon
    Sep 9 '14 at 18:05
  • It certainly won't be fast, but see #1 here: blogs.sqlsentry.com/aaronbertrand/… Sep 9 '14 at 18:09
  • @MaxVernon No, most of them are likely VARCHAR, like 'HB194' or 'Immediately'. Sep 9 '14 at 18:14
  • @AaronBertrand That looks promising. I'm running it for a known value now while I try to understand more than generally how it works, but it's got the essence of what I want to do -- search every string column on every table for a particular value. I don't think such brute-forcing could be done faster. Sep 9 '14 at 18:31
  • You can even limit it to string columns that are large enough to actually contain your value. Sep 9 '14 at 18:33

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

  @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]
        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]) + '
 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
).value(N'.[1]', N'nvarchar(max)'),1,6,'') + ';


-- EXEC sp_executesql @s;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.