January 23, 2013 at 10:47 pm
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
------------------------------------------
January 23, 2013 at 10:53 pm
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;-)
January 23, 2013 at 11:01 pm
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?
January 23, 2013 at 11:55 pm
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;-)
January 23, 2013 at 11:57 pm
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?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 24, 2013 at 12:17 am
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_MSforeachdbAny 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[/url]
Learn Extended Events
January 24, 2013 at 12:19 am
Here are two alternatives to sp_MSforeachdb
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[/url]
Learn Extended Events
January 24, 2013 at 1:51 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply