CODEWITHSUNDEEP
sqlsql-server
smolo
smolo

Reputation: 879

Dynamic union of table if a certain field exists

I'm trying to build a dynamic union over tables that have certain fields (in my example field1 and field2). The union already works but over any table. Now I need to include only the ones that have field1 and field2. 

DECLARE @SQL VARCHAR(max)

SET @SQL = ''

SELECT @SQL = @SQL + CASE Len(@SQL) WHEN 0 THEN '' ELSE ' UNION ALL ' END
+ ' SELECT [field1], [field2] FROM dbo.['
+ NAME + ']'
FROM   sys.tables
WHERE  NAME LIKE 'CUST_TABLE%'

EXEC (@SQL)

I guess I need to combine this query somehow: 

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME like 'CUST_TABLE%'
and COLUMN_NAME='field1'

Upvotes: 0

Views: 54

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You are close. Query the view INFORMATION_SCHEMA.COLUMNS. Aggregate per table name and make sure both columns exist for the table by counting them in the HAVING clause.

DECLARE @SQL VARCHAR(max)

SET @SQL = ''

SELECT @SQL = @SQL + CASE Len(@SQL) WHEN 0 THEN '' ELSE ' UNION ALL ' END
                   + ' SELECT [field1], [field2] FROM dbo.[' + table_name + ']'
FROM   information_schema.columns
WHERE  table_name LIKE 'CUST_TABLE%'
GROUP BY table_name
HAVING COUNT(CASE WHEN COLUMN_NAME = 'FIELD1' THEN 1 END) > 0
   AND COUNT(CASE WHEN COLUMN_NAME = 'FIELD2' THEN 1 END) > 0

EXEC (@SQL)

Upvotes: 1

Related Questions