How can I retrieve a set of table names which hold certain value?

Question

For an example I have 100 tables (table1,table2,table3,table4 and table5.....) in my DB, table1 with the coloumns col1, col2...col7 like wise every table has some columns. The column col7 have the value 'BOLD'. Here I want to retrieve the tables which holds the value 'BOLD'.

You can try [this](http://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/) as well. — Nilesh, Aug 17 '13 at 02:21

score 0 · Answer 1 · edited May 23 '17 at 12:28

maybe this question has what you're looking for.

Script taken from the accepted answer:

SELECT 'SELECT * FROM [' + tables.TABLE_NAME + '] WHERE ['
       + Columns.Column_Name + '] = ' + CONVERT(varchar(50),@COLUMNVALUE)
FROM INFORMATION_SCHEMA.Columns as Columns
INNER JOIN INFORMATION_SCHEMA.TABLES as tables 
    On Columns.TABLE_NAME = tables.TABLE_NAME
WHERE Columns.DATA_TYPE = @COLUMNTYPE

score 0 · Answer 2 · answered Aug 17 '13 at 03:20

Based on Felipe's answer, you can use the SELECTs generated, and execute them in a cycle:

SELECT 'SELECT TOP 1 1 FROM [' + tables.TABLE_NAME + '] WHERE [' + Columns.Column_Name + '] LIKE ''%' + CONVERT(varchar(50),'BOLD') + '%''' As Qry, 
    IDENTITY(int,1,1) As Id,
    tables.TABLE_NAME As TableName,
    Cast(null As Bit) As ContainsText
INTO #Queries
FROM INFORMATION_SCHEMA.Columns as Columns
INNER JOIN INFORMATION_SCHEMA.TABLES as tables 
    On Columns.TABLE_NAME = tables.TABLE_NAME
WHERE Columns.DATA_TYPE in ('varchar','nvarchar','text')

DECLARE @TableName NVARCHAR(MAX), @Qry NVARCHAR(MAX), @ContainsText BIT, @id INT
WHILE EXISTS (SELECT * FROM #Queries WHERE ContainsText IS NULL)
BEGIN
    SELECT TOP 1 @id = id, @TableName = TableName, @Qry = Qry FROM #Queries WHERE ContainsText IS NULL
    EXEC (@Qry)
    If (@@ROWCOUNT > 0)
        Set @ContainsText = 1
    Else
        Set @ContainsText = 0
    UPDATE #Queries SET ContainsText = @ContainsText WHERE id = @id
END

SELECT * FROM #Queries Where ContainsText = 1

This script will get all the tables that contains columns of textual types (nvarchar, varchar or text), and then will execute one by one a SELECT to evaluate is there is any row containing the given text ('BOLD').

How can I retrieve a set of table names which hold certain value?

2 Answers2