SQL Server Select from a table returned by a query as list of table names

Question

If I have these tables below:

PLAYERS

ID    Name
==    ===========
1     Mick
2     Matt

COACHES

ID    Name
==    ===========
1     Bill
2     Don

And I have a script below to find all tables that has a column called "Name":

SELECT t.name AS table_name FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE 'Name'

Which returns the following:

table_name
===========
PLAYERS
COACHES

How can I select all the rows from both tables returned by the query above?

Answer 1

You will have to use dynamic sql, try something like this:

declare @t table( tablename varchar(50))
declare @sql varchar(max)
set @sql = ''

insert into @t
SELECT t.name AS table_name FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE 'Name'

select @sql = @sql + 'Select * From ' + tablename + ' union ' from @t

--remove the trailing 'union'
Select @sql = substring(@sql, 1, len(@sql) - 6)

exec (@sql)

The above script creates and executes the following sql

select * from coaches
union
select * from players

Since we are using union here, it is important that all your tables that have name as column is of same structure.

See more about dynamic sql from http://msdn.microsoft.com/en-us/library/ms188001.aspx