Nested Query Nested Query

Question

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)

Example

Select * from POS   
Select * from SEC   
Select * from BROKER

Any ideas?

Answer 1

Set based\no cursors.

Final edit - a combo of both solutions

Declare @Sql NVarchar(Max) = N'';

Select @Sql += N'Select * From ' + QuoteName(Schema_Name(Schema_id)), '[') + N'.' + QuoteName(Tables.Name, '[') + N';'
From sys.tables
Join sys.Columns
On tables.object_id = Columns.object_id
Where columns.Name = 'Ticker';

Exec (@Sql);

Answer 2

You should use an INNER JOIN on the two tables, and you should also use the sys.objects and sys.columns system views instead of the deprecated sysobjects and syscolumns. Also, if you don't have a wild-card, you should use equals instead of 'like'.

The result is something like this:

SELECT * 
  FROM sys.objects o 
       INNER JOIN sys.columns c ON o.object_id = c.object_id
 WHERE c.name = 'TICKER'

Okay, you changed the question on me while I was answering it... so with your new requirements (and this is horribly dangerous... think about what would happen if it returned a thousand tables with a given column name), you can use dynamic SQL like this:

DECLARE @sql varchar(MAX);
DECLARE @tableName sysname;
DECLARE theCursor CURSOR FAST_FORWARD FOR
    SELECT o.name AS TableName 
      FROM sys.objects o 
           INNER JOIN sys.columns c ON o.object_id = c.object_id
     WHERE c.name = 'TICKER';

OPEN theCursor;
FETCH NEXT FROM theCursor INTO @tableName;

WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @sql = 'SELECT * FROM ' + QUOTENAME(@tableName);
    EXEC (@sql);
    FETCH NEXT FROM theCursor INTO @tableName;
END

CLOSE theCursor;
DEALLOCATE theCursor;

Or, if you want to avoid the cursor, do it like this, no temp tables required:

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);