Find a specific column entry in an unknown table in a database?

Question

I'm aware of this topic ( Find a specific column in an unknown table in a database? ) and my problem is quite similar. The query I need is quite similar to this one (I think):

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE '%watcher%'

But what I need is a query where the column name is unknown, but I know what the content will be and I want to find out what the name of table/column is. (I know this sounds strange :-/ ). I this possible?

Answer 1

Try using ApexSQL Search – it searches for both objects and data and it’s a free tool similar to SQL Search from Red Gate.

Another option if you don’t want to deal with third party tools is query that will use cursors to iterate through all columns in all tables but I’m afraid that it would turn out too complex and performance intensive.

Answer 2

Ok, I think that for your problem, you are gonna need dynamic sql, so first take a look at this link. If that weren't enough, the only solution that came to mind involves cursors, so I advise you to keep looking for others implementation of your problem. That said, you can try the following code (but you should test it on small tables first).

DECLARE @Query NVARCHAR(MAX), @Column NVARCHAR(100), @Table NVARCHAR(100)
DECLARE @Search NVARCHAR(100)
SET @Search = 'Your string'

CREATE TABLE #Results(Table_Name VARCHAR(100), Column_Name VARCHAR(100))

DECLARE Col CURSOR FOR
SELECT Table_Name, Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME IS NOT NULL
ORDER BY TABLE_NAME, ORDINAL_POSITION

OPEN Col
FETCH NEXT FROM Col INTO @Table, @Column
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Query = 'IF EXISTS (SELECT * FROM '+QUOTENAME(@Table)+' WHERE '+QUOTENAME(@Column)+'='''+@Search+''')
                    SELECT '''+@Table+''','''+@Column+''''

    INSERT INTO #Results
    EXEC sp_executesql @Query
    FETCH NEXT FROM Col INTO @Table, @Column
END
CLOSE Col
DEALLOCATE Col

SELECT * FROM #Results