Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Loop through all DBs in an Instance? Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 10:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:58 PM
Points: 84, Visits: 164
Hi,

Can anyone provide a method to loop through a DB's in an instance WITHOUT using the proc sp_MSforeachdb

I have tried the following, and the "EXEC(@String)" does not error, but I always stay in the context of the DB I run it from.

Very frustrating, there must be a way to do this?!?!?!?

------------------------------------------

DECLARE @DatabaseName nvarchar(50)
DECLARE @String nvarchar(50)

SELECT name
INTO #nameOfAllDbsInTheInstance
FROM sys.sysdatabases

DECLARE DBName CURSOR
FOR SELECT D.[name]
FROM #nameOfAllDbsInTheInstance AS D
OPEN DBName
FETCH NEXT FROM DBName
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @String = 'USE ' + @DatabaseName
PRINT @String

EXEC(@String)
SELECT DB_NAME()

FETCH NEXT FROM DBName
INTO @DatabaseName

END

DROP TABLE #nameOfAllDbsInTheInstance
CLOSE DBName
DEALLOCATE DBName

------------------------------------------

Post #1410900
Posted Wednesday, January 23, 2013 10:53 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:36 AM
Points: 2,840, Visits: 3,970
YOur cursor doesnt do here anything , simply "USe database"
What actaully you are trying to achieve here ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1410902
Posted Wednesday, January 23, 2013 11:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:58 PM
Points: 84, Visits: 164
It is just an example, the cursor here just gets the DBName and I try and change to the context of that DB.

I know it's not actually do anything as it is just and example, the whole point is how to change DB context dynamically?
Post #1410905
Posted Wednesday, January 23, 2013 11:55 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:36 AM
Points: 2,840, Visits: 3,970
tom.moore.777 89426 (1/23/2013)
the whole point is how to change DB context dynamically?

yes it can be done with the same way as you did but need additional code too like :



set @lstr = 'USe ' + @databasename + '; create table Test (id int ) ;'
exec (@lstr)

this will create test table in all the databases passed by @databasename from cursor


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1410919
Posted Wednesday, January 23, 2013 11:57 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:34 AM
Points: 2,666, Visits: 4,736
tom.moore.777 89426 (1/23/2013)

Can anyone provide a method to loop through a DB's in an instance WITHOUT using the proc sp_MSforeachdb


Any particular reason for avoiding the use of sp_MSforeachdb?



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1410921
Posted Thursday, January 24, 2013 12:17 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 17,811, Visits: 15,735
Kingston Dhasian (1/23/2013)
tom.moore.777 89426 (1/23/2013)

Can anyone provide a method to loop through a DB's in an instance WITHOUT using the proc sp_MSforeachdb


Any particular reason for avoiding the use of sp_MSforeachdb?


msForeachdb has a few bugs and limits.

iirc there was a character limitation. There is also inconsistent behavior such as it skipping databases.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1410928
Posted Thursday, January 24, 2013 12:19 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 17,811, Visits: 15,735
Here are two alternatives to sp_MSforeachdb

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx

And then this one that I have been using:
http://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/

With a few samples of usage here
http://jasonbrimhall.info/2012/07/17/a-trio-of-eachdb/




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1410929
Posted Thursday, January 24, 2013 1:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
If you want to do the same thing in each database, then this would be another alternative solution

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL =
REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
--PUT WHAT YOU WANT TO DO IN EACH DATABASE IN THIS BLOCK
----
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
----
FROM
sys.databases
FOR XML PATH('')
) AS NVARCHAR(MAX)
),
'&#x 0D;',CHAR(13) + CHAR(10)
)
--SELECT @SQL
EXECUTE sp_executesql @SQL

Just remove the space between the x and the 0 in the following string in the script '&#x 0D;'

The above example loops through all the databaes and gets their file usage, free space, used space, total space etc, so I can track DB growth.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1410985
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse