Post reply

Using sp_MSForeachdb to create a SCHEMA

  • 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!

  • 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

  • May 5, 2015 at 1:46 pm

    #1795736

    That works.

    Thank you, Shawn!

  • 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

  • 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

  • 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply