Easy question-Dynamic SQL
-
I have a over one hundred fifty tables that share a column name. I want to query each of these tables for a specific value in that column. I'd be happy to load the table list into a temp table. My question is "How can I tell SQL to run the same query against each table in the list?"
Thanks
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
You can use sp_msforeachtable which is an undocumented proc in sql.
Do
exec sp_msforeachtable 'select * from [?] where colname = value'
will look at all tables and if they have that column will return the results with that value. If you have any tables without that column you will get an error thou.
-
This almost works:
Declare @tablename varchar(255)
Declare tablecursor CURSOR For
Select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'QTY_PO'
OPEN tablecursor
FETCH NEXT from tablecursor into @tablename
while @@fetch_status = 0
BEGIN
print @tablename
select QTY_PO from @tablename where PO_ID = '12345-000'
FETCH NEXT from tablecursor into @tablename
END
close tablecursor
deallocate tablecursor
==========================
It doesn't like the @tablename in the from clause.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
You will need to put the
select QTY_PO from @tablename where PO_ID = '12345-000'
into a variable and then run EXEC sp_executesql @var for the @var for tablename to work. I hope that in next versions coming out we won't have to do that...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Declare @tablename varchar(255)
Declare tablecursor CURSOR For
Select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'QTY_PO'
OPEN tablecursor
FETCH NEXT from tablecursor into @tablename
while @@fetch_status = 0
BEGIN
print @tablename
exec( 'select QTY_PO from ' + @tablename + ' where PO_ID = ''12345-000''' )
FETCH NEXT from tablecursor into @tablename
END
close tablecursor
deallocate tablecursor
/rockmoose
You must unlearn what You have learnt -
I do this by using sp_columns.
-
Thanks for all your help. Here is a version that works. I decided to count the rows that match. In a perfect world, it would only display results where the count is > 0. I don't have time to play.
========================================================
Declare @tablename varchar(255),
@query nvarchar(100)
Declare tablecursor CURSOR For
Select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'QTY_PO'
OPEN tablecursor
FETCH NEXT from tablecursor into @tablename
while @@fetch_status = 0
BEGIN
print @tablename
set @query = N'select count(QTY_PO) from ' + @tablename + ' where PO_ID = ''12345-000'''
execute sp_executesql @query
FETCH NEXT from tablecursor into @tablename
END
close tablecursor
deallocate tablecursor
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply