The worst query I ever wrote

I work in an unusual environment---not everything is documented. I know, that sounds too unlikely to be true but it is. There was data in the database that needed to be reported on but not through the supplied UI. In this case, the specifics was the data was stored in Tfs_DefaultCollection and somewhat reportable through Visual Studio Team Explorer but the users wanted queries and reports that weren't supported through the API.

Enter my big mouth saying it's a database I can find a way to make it work

After randomly picking tables and scrolling through values, I needed a smarter approach. Smartness for me meant using the system meta data to find any column that could hold my data (right data type and minimum length). I then build and execute a query looking for an example of the search key in that column. Below is an example of a search for the word change. What I like about this query, is it ought to be fairly portable/database agnostic as I'm using the INFORMATION_SCHEMA views to identify the candidate schemas, tables and columns.

SET NOCOUNT ON;

DECLARE
    -- actual query 
    @query nvarchar(max)
    -- templated query
,   @template nvarchar(max)
    -- string value we wish to find
    -- Length of 50 is arbitrary
,   @searchKey varchar(50);

SET
    @searchKey = 'change';

-- Return column & schema/table combo anywhere
-- there is data in the column that starts like the
-- search key.
SELECT
    @template = N'
    SELECT TOP 1 
        ''<COLUMN_NAME/>'' AS cname
    , ''<SCHEMA/>.<TABLE_NAME/>'' AS tname 
    FROM 
        <SCHEMA/>.<TABLE_NAME/> T 
    WHERE 
        T.<COLUMN_NAME/> LIKE ''<TARGET/>%''';

SELECT
    @template = REPLACE(@template, '<TARGET/>', @searchKey);

DECLARE 
    CSR CURSOR
FOR
-- Iterate through all the columns that are
-- character data types and are at least
-- as long as the search key
SELECT
    ISC.TABLE_SCHEMA
,   ISC.TABLE_NAME
,   ISC.COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS ISC
WHERE
    -- filter out tables/views I know I can skip
    ISC.TABLE_NAME not like 'x%'
    AND
    (
        ISC.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar')
        AND ISC.CHARACTER_MAXIMUM_LENGTH >= LEN(@searchKey)
    );

-- Cursor variables for capturing candidate schemas, tables and columns
DECLARE
    @table_schema sysname
,   @table_name sysname
,   @column_name sysname;

DECLARE
    @RESULTS TABLE
(
    column_name sysname
,   table_schema nvarchar(500) NOT NULL
);

OPEN
    CSR;

FETCH NEXT
FROM
    CSR
INTO
    @table_schema
,   @table_name
,   @column_name;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    -- stub in actual names, make 'em safe via quotename function
    SET @query = REPLACE(@template, '<SCHEMA/>', quotename(@table_schema));
    SET @query = REPLACE(@query, '<TABLE_NAME/>', quotename(@table_name));
    SET @query = REPLACE(@query, '<COLUMN_NAME/>', quotename(@column_name));

    BEGIN TRY
        --PRINT @query
        
        -- Dump results into a table variable
        INSERT INTO
            @RESULTS
        EXECUTE(@query);
        
    END TRY
    BEGIN CATCH
        -- print failing query
        PRINT @query;
        
    END CATCH
    FETCH NEXT
    FROM
        CSR
    INTO
        @table_schema
    ,   @table_name
    ,   @column_name;
END
CLOSE CSR;
DEALLOCATE CSR;

-- Show all the columns and fully qualified tables
-- that contained the value
SELECT 
    R.column_name
,   R.table_schema
,   'SELECT T.* FROM ' + R.table_schema + ' T WHERE T.' + R.column_name + ' LIKE  ''' + @searchKey + '%''' AS explore_query
FROM 
    @results R;

I can run the above against Tfs_DefaultCollection.

column_name table_schema explore_query
[ChildItem] [dbo].[tbl_Version] SELECT T.* FROM [dbo].[tbl_Version] T WHERE T.[ChildItem] LIKE  'change%'
[ProjectName] [dbo].[tbl_Project] SELECT T.* FROM [dbo].[tbl_Project] T WHERE T.[ProjectName] LIKE  'change%'
[ChildItem] [dbo].[tbl_VersionedItem] SELECT T.* FROM [dbo].[tbl_VersionedItem] T WHERE T.[ChildItem] LIKE  'change%'

And easily switch over to Tfs_Warehouse to try and track those values over there

column_name table_schema explore_query
[ChangesetTitle] [dbo].[DimChangeset] SELECT T.* FROM [dbo].[DimChangeset] T WHERE T.[ChangesetTitle] LIKE  'change%'
[FileName] [dbo].[DimFile] SELECT T.* FROM [dbo].[DimFile] T WHERE T.[FileName] LIKE  'change%'
[ProjectNodeName] [dbo].[DimTeamProject] SELECT T.* FROM [dbo].[DimTeamProject] T WHERE T.[ProjectNodeName] LIKE  'change%'

Warnings, caveats, etc

While I'd think it's blindingly obvious, never run code from the interblag. Beyond that, this code is not going to be fast by any stretch of the imagination. It could lock a table for the read. It might bog down your disk infrastructure, especially if you're querying against a very large table. And it's going to do it over and over again so buyer beware.