CODEWITHSUNDEEP
sqlsql-serverselect
Chris Wijaya
Chris Wijaya

Reputation: 1286

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

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?

Upvotes: 5

Views: 11699

Answers (2)

unlimit
unlimit

Reputation: 3752

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

Upvotes: 9

sentil kumar
sentil kumar

Reputation: 95

SELECT p.Id,p.Name,c.Id,c.Name
FROM Players p JOIN Coaches c
ON p.Id=c.Id

May be this can help you.

Upvotes: -2

Related Questions