How to write use dbname in a loop

• Raghavender Chavva

  SSCrazy Eights

  Points: 8669

  More actions

  September 21, 2011 at 5:33 am

  #245239

  Hi All,

  I am trying to to execute below script:

  DECLARE @intFlag varchar(30)

  set @intFlag = 'test'

  exec( 'use '+@intFlag)

  select * from sysobjects where xtype='u'

  But its not working.

  Here I want to pass the database naem using a variable to change the database context like

  Use Dbname as USE @variable.

  How can we achieve it.

  Thank You.

  Regards,
  Raghavender Chavva

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 21, 2011 at 5:37 am

  #1384416

  You'd need to have both the use and the next select in the same dyamic sql call.

  What do you need to do exactly?

• asiaindian

  SSCrazy

  Points: 2493

  More actions

  September 21, 2011 at 5:46 am

  #1384421

  check this link....

  [/url]

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  September 21, 2011 at 6:04 am

  #1384427

  You can use sp_MsForEachDB or a better replacement[/url].

  Hope this helps

  Gianluca

  -- Gianluca Sartori

• Lowell

  SSC Guru

  Points: 323522

  More actions

  September 21, 2011 at 6:21 am

  #1384434

  you could also use three part naming, and not use the USE command at all:

  select * from test.dbo.sysobjects where xtype='u'

  --or

  select * from test.sys.tables

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!