Using sp_MSForeachdb to create a SCHEMA

• rocky

  SSCrazy

  Points: 2120

  More actions

  May 5, 2015 at 11:59 am

  #296564

  What is wrong with this T-SQL?

  I'm receiving error:

  Msg 156, Level 15, State 1, Line 46

  Incorrect syntax near the keyword 'SCHEMA'.

  EXEC master..sp_MSForeachdb

  '

  USE [?]

  IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')

  BEGIN

  SELECT ''?''

  CREATE SCHEMA [T_schema]

  END

  '

  If I replace the CREATE SCHEMA statement with any other statement, such as CREATE USER or CREATE ROLE, it runs fine.

  Please no replies about how sp_MSForeachdb is unreliable.

  Thank you!

• Shawn Melton

  SSC-Insane

  Points: 24685

  More actions

  May 5, 2015 at 12:45 pm

  #1795720

  You will have to use sp_executesql to execute it:

  EXEC master.dbo.sp_MSForeachDb 'USE [?]

  IF "?" NOT IN (''master'',''model'',''msdb'',''tempdb'')

  BEGIN

  SELECT ''?''

  DECLARE @query nvarchar(50)

  SET @query = ''CREATE SCHEMA [T_schema]''

  EXEC sp_executesql @query

  END

  '

  Shawn Melton
  Twitter: @wsmelton
  Blog: wsmelton.github.com
  Github: wsmelton

• rocky

  SSCrazy

  Points: 2120

  More actions

  May 5, 2015 at 1:46 pm

  #1795736

  That works.

  Thank you, Shawn!

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  May 5, 2015 at 2:50 pm

  #1795758

  Will read the entire OP moving forward.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Shawn Melton

  SSC-Insane

  Points: 24685

  More actions

  May 5, 2015 at 2:53 pm

  #1795762

  Alan.B (5/5/2015)

  ---

  I'm not trying to belittle Shawn's solution as he was just fixing your code.

  It is worth noting, however, that sp_MSForEachDB is undocumented and you should never use undocumented code in production environments. You could use a Dynamic SQL and sys.databases to accomplish the same thing.

  I'm pretty sure the OP is aware of this as he stated: :hehe:

  Please no replies about how sp_MSForeachdb is unreliable.

  Thank you!

  Shawn Melton
  Twitter: @wsmelton
  Blog: wsmelton.github.com
  Github: wsmelton

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  May 5, 2015 at 3:08 pm

  #1795767

  Shawn Melton (5/5/2015)

  ---

  Alan.B (5/5/2015)

  ---

  I'm not trying to belittle Shawn's solution as he was just fixing your code.

  It is worth noting, however, that sp_MSForEachDB is undocumented and you should never use undocumented code in production environments. You could use a Dynamic SQL and sys.databases to accomplish the same thing.

  I'm pretty sure the OP is aware of this as he stated: :hehe:

  Please no replies about how sp_MSForeachdb is unreliable.

  Thank you!

  Yep. Duh (smacking own head)

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001