How to find List Tables using columns value

I have 500+ tables in database. All tables have several columns. Among them Some tables have ‘CMDFLAG’ column and value of the columns may have ‘C’or’D’ or ‘M’.

My requirement is to find the list of tables where CMDFLAG is ‘C’or’D’ or ‘M’.

Table Name    Column Name   Value
----------     -----------   -----
Table_A         CMDFLAG      C
Table_A         CMDFLAG      D
Table_A         CMDFLAG      M
Table_B         CMDFLAG      C
Table_B         CMDFLAG      D
Table_C         CMDFLAG      M

so on …

I can find list of tables these have CMDFLAG column using INFORMATION_SCHEMA.COLUMNS. But I Want to find list of tables where CMDFLAG columns have value ‘C’or’D’ or ‘M’.

I have gone through several questions but can’t fulfill my requirement. Moreover I want to use simple query not procedure.

Answer

Try this. I have to use dynamic query and temp tables as EXEC does not work for common table expressions.

CREATE TABLE #t1
(
    tableName varchar(30),
    RN INT
)

CREATE TABLE #t2
(
    tableName varchar(30),
    columnName varchar(30),
    value char(1)
)

INSERT INTO #t1
SELECT C.TABLE_NAME, ROW_NUMBER() OVER(ORDER BY TABLE_NAME) AS RN
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.COLUMN_NAME = 'CMDFLAG'

DECLARE @COUNT AS INT = (SELECT COUNT(1) FROM #t1)
DECLARE @Iterator AS INT = 1
DECLARE @tableName AS VARCHAR(30)
DECLARE @script AS  VARCHAR(200)

WHILE @Iterator <= @COUNT
BEGIN
    SELECT @tableName = (SELECT tableName FROM #t1 WHERE RN = @Iterator)
    SELECT @script = 'SELECT '''+@tableName+''', ''CMDFLAG'', CMDFLAG FROM '+@tableName+' GROUP BY CMDFLAG'

    INSERT INTO #t2
    EXEC(@script)

    SELECT @Iterator = @Iterator + 1

END

SELECT * FROM #t2

DROP TABLE #t1
DROP TABLE #t2