Search For A String In Any Column Of All Tables

For example this is my ID: 07E485 I need to find this ID in all tables wherever it is found All columns, which might carry this value, are sort of string-type... Something like: se

Solution 1:

The following query will return all tables in the database yourDBName whose name contains 07E485.

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE'AND
      TABLE_CATALOG ='yourDBName'AND
      TABLE_NAME LIKE'%07E485%'

If I misread your requirement, and you instead wanted to find all tables precisely named 07E485 in any database, then you can use the following query:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE'AND
      TABLE_NAME ='07E485'

Solution 2:

In each database you have a view called INFORMATION_SCHEMA.COLUMNS, you can use this view to query through all of your tables.

This is the way I'ld do it, if anyone know a better way, feel free.. :)

SET NOCOUNT ONDECLARE@Tablevarchar(255), @Schemavarchar(255), @SQLvarchar(MAX)

DECLARE table_cursor CURSORFORSELECT TABLE_NAME, TABLE_SCHEMA
    FROM INFORMATION_SCHEMA.COLUMNS -- This is a system view where you can see all columns of a database.WHEREUPPER(COLUMN_NAME) ='ID'-- This makes sure you don't loop through any tables that don't have a Column called 'ID'.OPEN table_cursor
    FETCH NEXT FROM table_cursor INTO@Table, @Schema
    WHILE @@FETCH_STATUS =0BEGIN-- This part creates your queries.SET@SQL='SELECT * FROM '+@Schema+'.'+@Table+'
        WHERE CAST(ID as varchar) = ''07E485'''-- Casting ID to varchar to avoid data type errors.-- This executes the query.EXEC(@SQL)

        -- If a result is found, i.e. ID is equal to '07E485' somewhere in the table, Table name is printed on the "Messages" tab.
        IF @@ROWCOUNT>0 PRINT @TableFETCH NEXT FROM table_cursor INTO@Table, @SchemaENDCLOSE table_cursor
    DEALLOCATE table_cursor

To see which tables contain id = '07E485', go to "Messages" and you will have a list of them.

Solution 3:

UPDATE My answer completely re-written

Try it like this: This dynamic SQL will check all string-type columns if they are equal to the given search string. You might want to add more data types to the output to get a better look onto the table's row. But one cannot simply put SELECT * as there are data types not allowed in XML without extra effort.

Secondly, by using QUOTENAME, I avoid syntax errors due to column or table names with blanks...

DECLARE@SearchVARCHAR(10)='07E485';

DECLARE@cmdVARCHAR(MAX);
WITH TableNames AS
(
    SELECT t.*
          ,t.TABLE_CATALOG +'.'+ t.TABLE_SCHEMA +'.'+ t.TABLE_NAME AS FullTblName
          ,QUOTENAME(t.TABLE_CATALOG)+'.'+ QUOTENAME(t.TABLE_SCHEMA) +'.'+ QUOTENAME(t.TABLE_NAME) AS FullTblNameQuoted
          , 
          STUFF(
          ( 
            SELECT'OR '+ QUOTENAME(c.COLUMN_NAME) +'='''+@Search+''' 'FROM INFORMATION_SCHEMA.COLUMNS AS c
            WHERE c.TABLE_CATALOG=t.TABLE_CATALOG AND c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
              AND DATA_TYPE LIKE'%char%'--add more types if neededFOR XML PATH('')      
          ),1,3,'') AS WhereFilter

    FROM INFORMATION_SCHEMA.TABLES AS t
    WHERE TABLE_TYPE='BASE TABLE'
)
SELECT@cmd= STUFF(
(
    SELECTDISTINCT'UNION ALL SELECT (SELECT '+ (SELECT STUFF((SELECT','+ QUOTENAME(COLUMN_NAME) 
                                                                 FROM INFORMATION_SCHEMA.COLUMNS AS c 
                                                                 WHERE c.TABLE_CATALOG=TableNames.TABLE_CATALOG 
                                                                   AND c.TABLE_NAME =TableNames.TABLE_NAME 
                                                                   AND c.DATA_TYPE LIKE'%char%'FOR XML PATH('')),1,1,'')) +' FROM '+ FullTblNameQuoted 
                    +' WHERE '+ WhereFilter
                    +' FOR XML PATH(''row''),ROOT('''+  REPLACE(REPLACE(FullTblName,'.','_'),' ','') +'''),TYPE) AS XmlData 'FROM TableNames
    WHERE WhereFilter ISNOTNULLFOR XML PATH('')
),1,10,'')

SET@cmd='SELECT XmlData FROM('+@cmd+') AS tbl WHERE XmlData IS NOT NULL;'
PRINT LEN(@cmd)
EXEC(@cmd)