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