August 21, 2004 at 5:32 am
Hi,
I want to use DB names dynamically.
e.g. SELECT * FROM MYDB..MYTABLE. Here MYDB and MYTABLE are parameters.
I can use EXEC or sp_executesql for this.
But I just want to know is there any way of doing it without constructing the full query as NVARCHAR and then executing it?
Thanks
Niranjan
August 23, 2004 at 12:49 pm
Normally if someone is looking to write this kind of dynamic code it can be rewritten so that the column names/database names are actually contained within the rows.
e.g. I've seen loads of the following:
10 tables named Counter1, counter2, counter3 etc
10 tables named salesAmerica, salesEurope etc etc
These table names should be data values within columns. So the above should be:
Counter - with an additional column called counternumber
Sales - with an additional column called Region
If you can't redesign like this, then you'll have to use dynamic SQL - you can't parameterise any of the structure of the database.
Dave Hilditch
August 23, 2004 at 2:24 pm
There is an undocumented stored procedure called sp_msforeachdb. Basically it will execute up to 3 commands for each database.
EXEC sp_msforeachdb 'DBCC CHECKDB(?)'
EXEC sp_msforeachdb @command1 = 'Print "Listing ?"', @command2='USE ?;EXEC sp_dir'
The ? is a placeholder that SQL will replace with the name of each database.
-From The Guru's Guide to Transact-SQL by Ken Henderson
You can use Enterprise Manager, drill to the Master database, expand down to stored procedures and this one (and sp_msforeachtable) is listed. Right click on it and select properties to see the actual command.
-SQLBill
August 23, 2004 at 2:26 pm
I also suggest doing a search for msforeachdb on this site and maybe google'ing it for more information.
-SQLBill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply