SP_msforeachdb
-
I am trying to use SP_msforeachdb to query one table in every DB I have, but no system db's. here is the syntax im using. im not sure this is right since it is not a supported SP.
EXEC sp_msforeachdb @command1="select top 1 '?', versionname from ?.dbo.databaseversion order by versionID desc"
IF "?" <> "master" AND "?" <> "model" AND "?" <> "msdb" AND "?" <> "tempdb" AND "?" <> "reportserver" AND "?" <> "reportservertempdb"
Go
-
IF doesn't work that way. It tests a condition and executes or skips the statement after it based on that condition.
You'll be better of building a cursor and having it step through sys.databases and execute a dynamic SQL command for each.
declare DBs cursor local fast_forward forselect name
from sys.databases
where ID > 4; -- Or modify the Where clause to fit your scenario
open DBs;
declare @DB nvarchar(100), @sql nvarchar(max);
fetch next from DBs into @DB;
while @@fetch_status = 0
begin
select @sql = 'select top 1 ''' + @DB + ''', versionname from [' + @DB + '].dbo.databaseversion order by versionID desc;';
print @sql;
exec (@SQL)
fetch next from DBs into @DB;
end;
close DBs;
deallocate DBs;
Try something like that, see if it will do what you need.
I would suggest changing the simple select statement to an insert select into a temp table, then select from that at the end after the cursor is done. Usually ends up being easier to read/use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
jarrederoberts (4/6/2010)
I am trying to use SP_msforeachdb to query one table in every DB I have, but no system db's. here is the syntax im using. im not sure this is right since it is not a supported SP.EXEC sp_msforeachdb @command1="select top 1 '?', versionname from ?.dbo.databaseversion order by versionID desc"
IF "?" <> "master" AND "?" <> "model" AND "?" <> "msdb" AND "?" <> "tempdb" AND "?" <> "reportserver" AND "?" <> "reportservertempdb"
Go
Pretty close... check this
DECLARE @command varchar(1000)SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? EXEC(''select db_name()'') END'
EXEC sp_MSforeachdb @command
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
I love that command but for the life of me I can not figure out why it fails if you try to use it to set your RECOVERY model for all you user databases. Here's the script - any insight would solve one of my most puzzling mysteries...
EXEC sp_MSForEachDB 'if ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
use [?]
print ''?'' + '': SET RECOVERY SIMPLE WITH NO_WAIT''
ALTER DATABASE [?] SET RECOVERY SIMPLE WITH NO_WAIT
END
print ''?'' + '': SET RECOVERY SIMPLE WITH NO_WAIT - DONE'';
'
GO
RESULTS:
master: SET RECOVERY SIMPLE WITH NO_WAIT - DONE
Msg 5058, Level 16, State 1, Line 5
Option 'RECOVERY' cannot be set in database 'tempdb'.
model: SET RECOVERY SIMPLE WITH NO_WAIT - DONE
msdb: SET RECOVERY SIMPLE WITH NO_WAIT - DONE
-
Michael Rybicki (10/17/2012)
I love that command but for the life of me I can not figure out why it fails if you try to use it to set your RECOVERY model for all you user databases. Here's the script - any insight would solve one of my most puzzling mysteries...EXEC sp_MSForEachDB 'if ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
use [?]
print ''?'' + '': SET RECOVERY SIMPLE WITH NO_WAIT''
ALTER DATABASE [?] SET RECOVERY SIMPLE WITH NO_WAIT
END
print ''?'' + '': SET RECOVERY SIMPLE WITH NO_WAIT - DONE'';
'
GO
RESULTS:
master: SET RECOVERY SIMPLE WITH NO_WAIT - DONE
Msg 5058, Level 16, State 1, Line 5
Option 'RECOVERY' cannot be set in database 'tempdb'.
model: SET RECOVERY SIMPLE WITH NO_WAIT - DONE
msdb: SET RECOVERY SIMPLE WITH NO_WAIT - DONE
You can call the "ALTER DATABASE" using sp_executesql to avoid the error.
EXEC sp_MSForEachDB 'if ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')BEGIN
use [?]
print ''?'' + '': SET RECOVERY SIMPLE WITH NO_WAIT''
EXEC sp_executesql N''ALTER DATABASE [?] SET RECOVERY SIMPLE WITH NO_WAIT'';
END
print ''?'' + '': SET RECOVERY SIMPLE WITH NO_WAIT - DONE'';
'
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply