CODEWITHSUNDEEP
sqlsql-server
oden
oden

Reputation: 3651

View that displays all rows from tables returned by separate query

Need to display all rows from multiple tables. The tables to be included are to come from a separate query to ensure. So we know where the data has originated from, a new column must be added that contains the table name.

The tables to be included in this must be dynamic, to reduce maintenance, so newly created tables are automatically included. To select the tables I have create this query:

select TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'contact_info_type' 
and TABLE_NAME NOT LIKE '%test%' 
and TABLE_NAME NOT LIKE '%_STAGING'

The testing of the 'contact_info_type' column is to ensure the table is of the expected structure. All of these tables are exactly the same.

But then how do you pass these results into a new select statement?

I attempts this, which produced duplicate results and would not stop processing. Its also missing the extra column that adds in the table name

declare @tableNames nvarchar(max)
select  @tableNames = COALESCE(@tableNames + ', ', '')  + Cast(TABLE_NAME as varchar) from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'contact_info_type' and TABLE_NAME not like '%_STAGING' and TABLE_NAME like '%test%'
select @tableNames
declare @sqlText nvarchar(max)
set @sqlText = ''
select @sqlText = @sqlText + 'SELECT * from ' + @tableNames where person = 'Joe'
select @sqlText

Each table will contain a few thousand rows but the condition (name = 'Joe') will limit the results to around 100.

Running Server 2008 R2 SP3 (10.50).

Upvotes: 2

Views: 238

Answers (1)

Naveen
Naveen

Reputation: 144

May be this query would help you,

But you need to add the additional columns as what you need in your @results table. 

DECLARE @tables TABLE  (tableName VARCHAR(1000), nmbr INT IDENTITY(1,1))
DECLARE @results TABLE (person varchar(500), tablename varchar(1000))
DECLARE @i INT,
    @tableName varchar(1000),
    @sql varchar(500)

INSERT INTO @tables
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'contact_info_type' 
    AND TABLE_NAME NOT LIKE '%test%' 
    AND TABLE_NAME NOT LIKE '%_STAGING'

SELECT @i = MAX(nmbr)
FROM @tables AS t

WHILE (@i > 0)
BEGIN

    SELECT @tableName = tablename
    FROM @tables
    WHERE nmbr = @i

    SET @sql = 'SELECT person, '''+@tablename +''' as tablename
                FROM '+ @tableName +'
                WHERE person = ''joe'''

    INSERT INTO @results
    EXEC (@sql)

    SET @i = @i - 1

END

SELECT * FROM @results

Upvotes: 1

Related Questions