List of all tables that contains specific data in column

Question

With this query, I get all tables that contains column named "Status_ID"

SELECT
   c.name AS 'ColumnName'
  ,t.name AS 'TableName'
FROM sys.columns c
   JOIN sys.tables  t ON c.object_id = t.object_id
WHERE c.name LIKE 'Status_ID'

Data in Status_ID may only have values from 1 to 6. What I want is to get a list of all tables, where Status_ID = 2 at least once. (Exclude all tables from the code above, that do not contain data with Status_ID = 2)

You can use dynamic sql and query data from tables you've found with first query — Andrey Korneyev, Jun 20 '17 at 12:16

score 0 · Answer 1 · edited Jun 20 '17 at 12:37

Solution 1: Run the below query, which will give you a select query with all the tables containing status_id column. then copy and execute the select query to find the data.

SELECT 'select *  from ' + TABLE_NAME + '  where Status_ID = ''2'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Status_ID'

Solution 2: You may need to use the following solution to find the data in all tables Find a string by searching all tables in SQL Server Management Studio 2008

S.Karras · Answer 2 · 2017-12-05T10:42:32.107

This should do the trick:

DECLARE @sql NVARCHAR(MAX) = 'DECLARE @tables NVARCHAR(MAX) = '''' ;' ;
DECLARE @tables NVARCHAR(MAX) = '';
SELECT   @SQL += 'IF EXISTS (SELECT ''X''  FROM ' + QUOTENAME(t.name)
         + 'WHERE STATUS_ID =2) SET @tables+= ' + '''' + + ',' + 
QUOTENAME(t.name) + ''''
         + ';'
FROM     sys.columns c
         JOIN sys.tables t ON c.object_id = t.object_id
WHERE    c.name LIKE 'STATUS_ID'


SET @sql += 'SELECT SUBSTRING(@TABLES,2,LEN(@TABLES));'
EXEC(@SQL);

List of all tables that contains specific data in column

2 Answers2