looping through databases

• WonderPra

  SSCommitted

  Points: 1624

  More actions

  March 7, 2013 at 3:29 pm

  #269390

  I am working on a sql task where i have to loop through all the databases in the server and do operations on those databases.. For example: selecting db1 and executing some select statements, selecting db2 and executing some select statements so on..

  here is the code

  DECLARE @Loop int

  DECLARE @DBName varchar(max)

  declare @maxRow int

  set @Loop = 1;

  SET @DBName = ''

  SELECT @maxRow=max(database_id) FROM sys.databases

  WHILE (@Loop <= @maxRow)

  BEGIN

  SELECT TOP 1 @DBName = d.Name

  FROM master.sys.databases d

  WHERE d.Name > @DBName

  AND d.database_id not in (1, 2, 3, 4) and d.state_desc = 'ONLINE'

  ORDER BY d.Name

  set @Loop = @Loop+1;

  PRINT @DBNAME

  END

  But the result is

  db1

  db2

  db3

  db4

  db4

  db4

  I know the culprit is here

  SELECT @maxRow=max(database_id) FROM sys.databases

  WHILE (@Loop <= @maxRow)

  But I couldn't go further.. Confused how to approach

  --Pra:-):-)--------------------------------------------------------------------------------

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  March 7, 2013 at 4:15 pm

  #1594741

  This might be what you need.

  EXECUTE sp_msforeachdb 'USE ?

  IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')

  PRINT DB_NAME()'

  Returns:

  ReportServer

  ReportServerTempDB

  Test2008K

  Test2012

  SSISDB

  Scores

  TIPSNTRICKS

  VaTax

  TestPartition

  AdventureWorks2012

  QOD100

  To learn more about this undocumented stored procedure:

  http://www.techrepublic.com/blog/datacenter/take-advantage-of-undocumented-sql-server-iteration-procedures/395

  Hope this assists you in doing what needs to be done.

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• WonderPra

  SSCommitted

  Points: 1624

  More actions

  March 7, 2013 at 5:02 pm

  #1594752

  Thanks for the reply

  my requirement is using a while loop because there are some 300 lines of code is involved after selecting each dbname in the while loop..

  I wonder if i can use the stored procedure and then from variable declaration.....so on

  Any suggestion is appreciated

  --Pra:-):-)--------------------------------------------------------------------------------

• arnipetursson

  SSCertifiable

  Points: 6557

  More actions

  March 7, 2013 at 5:39 pm

  #1594756

  You are mis-specifying @maxRow

  Should be:

  SELECT @maxRow = count(*)

  FROM master.sys.databases d

  WHERE d.database_id not in (1, 2, 3, 4) and d.state_desc = 'ONLINE'

• WonderPra

  SSCommitted

  Points: 1624

  More actions

  March 7, 2013 at 5:45 pm

  #1594758

  Donno How I missed this simple detail... Thanks a ton.. u saved my day

  --Pra:-):-)--------------------------------------------------------------------------------