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

SP_msforeachdb Expand / Collapse
Author
Message
Posted Tuesday, April 6, 2010 8:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 8, 2012 7:37 PM
Points: 1, Visits: 30
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
Post #897601
Posted Tuesday, April 6, 2010 9:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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 for
select 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
Post #897647
Posted Tuesday, April 6, 2010 2:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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) Berzukov

Author 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.
Post #897921
Posted Wednesday, October 17, 2012 8:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 19, 2013 5:06 PM
Points: 7, Visits: 46
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
Post #1373858
Posted Thursday, October 18, 2012 5:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:44 AM
Points: 1,001, Visits: 3,091
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





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1374273
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse