Nested Query

[egodette]

This query SELECT name FROM sysobjects where id in (select id from syscolumns where name like 'TICKER') produces the list of tables that contain a column called TICKER. Now I want to see all the data from each table. Select * from (the result from the above query)

Any ideas?

 

[fredericofonseca]

look at using dynamic sql

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[egodette]

can I get an example?

 

[TamarGranor]

I think this is what you're looking for. The result contains the field name and the table name for each field of every table that contains the specified field:

SELECT SysColumns.Name AS ColName, csrTables.Name AS TableName
  FROM SysColumns
    JOIN (SELECT id, name FROM sysobjects 
            where id in (select id from syscolumns where name like 'TICKER')) csrTables
	  ON SysColumns.id = csrTables.id

Tamar

 

[egodette]

I didn't give a very good example.
SELECT name FROM sysobjects where id in (select id from syscolumns where name like 'TICKER')
results in (to show only 3 of the 64 there are)

CASHFLOWTICK
CDAEMON
CDJOURN

now I want

select * from CASHFLOWTICK
select * from CDAEMON
select * from CDJOURN

 

[gmmastros]

SELECT 'Select * From ' + name FROM sysobjects where id in (select id from syscolumns where name like 'TICKER')

Be aware, this could return more than just tables. It would also include views and table valued function.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[egodette]

The correct answer is.... (Thanks everyone for your input)

DECLARE @sql varchar(MAX);
SET @sql = '';
SELECT @sql += 'SELECT * FROM ' + QUOTENAME(o.name) + '; '
FROM sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
WHERE c.name = 'TICKER';
EXEC (@sql);