May 5, 2015 at 11:59 am
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
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
That works.
Thank you, Shawn!
May 5, 2015 at 2:50 pm
Will read the entire OP moving forward.
-- Itzik Ben-Gan 2001
May 5, 2015 at 2:53 pm
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
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)
-- Itzik Ben-Gan 2001
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy