Search code examples
sql-serversmo

SMO - How to script an object in a Dynamic SQL style? (IF NOT EXISTS)


When I use SMO to script a procedure with the IncludeIfNotExists set to true, it first creates the procedure and then alters it. Is there a way for it to just create the procedure in a dynamic SQL style? (like in SSMS)

This is what I get from the SMO:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_a]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[proc_a] AS' 
END
GO
ALTER PROCEDURE [dbo].[proc_a] 
AS
BEGIN 
-- etc..
END
GO

I want something like this instead:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_a]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[proc_a] 
AS
BEGIN
-- etc..

END
' 
END
GO

Solution

  • For anybody interested, using the SQL 2008 SMO assembly generates the code with Dynamic-SQL Style while using SQL 2017 generates the code with the ALTER statement.

    [System.Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
    [System.Reflection.Assembly]::Load("Microsoft.SqlServer.SmoExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
    
    [System.Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
    [System.Reflection.Assembly]::Load("Microsoft.SqlServer.SmoExtended, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")