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.