score:2

Accepted answer

This script will search all tables and all string columns for a specific string. You might be able to adapt this for your needs:

DECLARE @tableName sysname
DECLARE @columnName sysname
DECLARE @value varchar(100)
DECLARE @sql varchar(2000)
DECLARE @sqlPreamble varchar(100)

SET @value = 'EDUQ4' -- *** Set this to the value you're searching for *** --

SET @sqlPreamble = 'IF EXISTS (SELECT 1 FROM '

DECLARE theTableCursor CURSOR FAST_FORWARD FOR 
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' 
       AND TABLE_NAME NOT LIKE '%temp%' AND TABLE_NAME != 'dtproperties' AND TABLE_NAME != 'sysdiagrams'
     ORDER BY TABLE_NAME

OPEN theTableCursor
FETCH NEXT FROM theTableCursor INTO @tableName

WHILE @@FETCH_STATUS = 0 -- spin through Table entries
BEGIN
    DECLARE theColumnCursor CURSOR FAST_FORWARD FOR
        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_NAME = @tableName AND (DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar')
         ORDER BY ORDINAL_POSITION

    OPEN theColumnCursor
    FETCH NEXT FROM theColumnCursor INTO @columnName

    WHILE @@FETCH_STATUS = 0 -- spin through Column entries
    BEGIN
        SET @sql = @tableName + ' WHERE ' + @columnName + ' LIKE ''' + @value + 
                   ''') PRINT ''Value found in Table: ' + @tableName + ', Column: ' +  @columnName + ''''
        EXEC (@sqlPreamble + @sql)
        FETCH NEXT FROM theColumnCursor INTO @columnName
    END
    CLOSE theColumnCursor
    DEALLOCATE theColumnCursor

    FETCH NEXT FROM theTableCursor INTO @tableName
END
CLOSE theTableCursor
DEALLOCATE theTableCursor

Read More

score:0

If you have access to the files are RegEx will be must faster than performing a generic search in SQL.

If you are forced to use SQL @pmbAustin's answer is the way to go. Be warned, it won't run quickly.

score:1

One option you have is to be a little creative using XML in SQL Server.

Turn a row at a time into XML using cross apply and query for the nodes that has a certain value in a second cross apply.

Finally you output the distinct list of node names.

declare @Value nvarchar(max)

set @Value= 'b'

select distinct T3.X.value('local-name(.)', 'nvarchar(128)') as ColName
from YourTable as T1
  cross apply (select T1.* for xml path(''), type) as T2(X)
  cross apply T2.X.nodes('*[text() = sql:variable("@Value")]') as T3(X) 

SQL Fiddle


More questions

More questions with similar tag