How to use DB names dynamically?

• Niranjan Shaiwale

  SSChasing Mays

  Points: 627

  More actions

  August 21, 2004 at 5:32 am

  #102167

  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

• dhilditch

  SSC-Addicted

  Points: 495

  More actions

  August 23, 2004 at 12:49 pm

  #520224

  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

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  August 23, 2004 at 2:24 pm

  #520241

  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

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  August 23, 2004 at 2:26 pm

  #520242

  I also suggest doing a search for msforeachdb on this site and maybe google'ing it for more information.

  -SQLBill