March 6, 2019 at 2:45 am
Hey people.
so i have this query :
EXEC master..sp_MSforeachdb '
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' <> "AdminConsole"
BEGIN
SELECT DB_NAME() AS DB FROM [?].dbo.tbl_WMSSettings WHERE WMSSettingsName = ''usesDocumentTypePermissions'' AND WMSSettingsValue = 0
END
this query return some databases.
is there any way to get those databases names as a list?
In general, i am trying to understand the use of "?".
Thank you.
March 6, 2019 at 5:58 am
Hi,
you are able to get your results as a text, not as a table:
Just press CTRL+T
But remember, MSforeachdb is not an offical way to do that, I think it is deprecated. Maybe you should work with cursor.
Kind regards,
Andreas
March 6, 2019 at 6:15 am
You could also look at this as a replacement from Aaron Bertrand;
But to answer your question, the ? is a placeholder for the next value, in this case the database name. If I recall the code has a replace in it. To replace the ? with the db name in the statement(s) you pass to the procedure. Hopefully that makes sense!
Rodders...
March 6, 2019 at 6:42 am
thank your for your replies guys. I managed to get the result i want doing that:
DECLARE @command varchar(1000)
EXEC master..sp_MSforeachdb @command
SELECT @command=
'
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' AND ''?'' <> ''Megaventory'' AND ''?'' <> ''ReportServer'' AND ''?'' <> ''ReportServerTempDB'' AND "?" <> "AdminConsole"
BEGIN
SELECT DB_NAME(DB_ID(''?'')) AS database_name FROM [?].dbo.tbl_WMSSettings WHERE WMSSettingsName = ''usesDocumentTypePermissions'' AND WMSSettingsValue = 0
END
'
DECLARE @DatabasesKst TABLE
(
database_name VARCHAR(50)
)
INSERT INTO @DatabasesKst
EXEC sp_MSforeachdb @command
select * from @DatabasesKst
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply