Get databases names as a list

• giannisfld

  Grasshopper

  Points: 17

  More actions

  March 6, 2019 at 2:45 am

  #377147

  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.

• andreas.kreuzberg

  SSCertifiable

  Points: 6202

  More actions

  March 6, 2019 at 5:58 am

  #2023648

  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

• rodjkidd

  SSCoach

  Points: 15921

  More actions

  March 6, 2019 at 6:15 am

  #2023650

  You could also look at this as a replacement from Aaron Bertrand;

  A more reliable ForEachDb
  

  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...

• giannisfld

  Grasshopper

  Points: 17

  More actions

  March 6, 2019 at 6:42 am

  #2023655

  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