SSMS Scripting Options

Question

Scripting a function using SSMS 18.0 Preview 6 produces the following script.

/****** Object:  UserDefinedFunction [dbo].[sfn_Proper_Case]    Script Date: 7/13/2019 9:57:36 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sfn_Proper_Case]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[sfn_Proper_Case]
                (
                @input    NVARCHAR(4000)
                )
RETURNS NVARCHAR(4000)
AS
    BEGIN

        SET @input = REPLACE(REPLACE(REPLACE(@input,'' '',''<>''),''><'',''''),''<>'','' '');

        DECLARE @output    NVARCHAR(512);
        DECLARE @current_position    INT = 1;

        IF DATALENGTH(@input) > 0
        BEGIN
            SET @output = '''';
        END;

        WHILE @current_position <= DATALENGTH(@input)
        BEGIN
            DECLARE @check_character     NVARCHAR(1);
            DECLARE @previous_character  NVARCHAR(1);
            DECLARE @output_character    NVARCHAR(1);

            SELECT @check_character = LOWER(SUBSTRING(@input,@current_position,1));
            SELECT @previous_character = SUBSTRING(@input,@current_position
                                                          - 1,1);

            IF @previous_character NOT LIKE ''[a-z]''
               AND @check_character LIKE ''[a-z]''
            BEGIN
                SELECT @output_character = UPPER(@check_character);
            END;
                ELSE
            BEGIN
                SELECT @output_character = @check_character;
            END;

            SET @output = @output
                          + @output_character;
            SET @current_position = @current_position
                                    + 1;
        END;

        RETURN @output;

    END;

' 
END
GO

Scripting the same function using SSMS 2016 produces this script

/****** Object:  UserDefinedFunction [dbo].[sfn_Proper_Case]    Script Date: 7/13/2019 9:55:43 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[sfn_Proper_Case]
                (
                @input    NVARCHAR(4000)
                )
RETURNS NVARCHAR(4000)
AS
    BEGIN

        SET @input = REPLACE(REPLACE(REPLACE(@input,' ','<>'),'><',''),'<>',' ');

        DECLARE @output    NVARCHAR(512);
        DECLARE @current_position    INT = 1;

        IF DATALENGTH(@input) > 0
        BEGIN
            SET @output = '';
        END;

        WHILE @current_position <= DATALENGTH(@input)
        BEGIN
            DECLARE @check_character     NVARCHAR(1);
            DECLARE @previous_character  NVARCHAR(1);
            DECLARE @output_character    NVARCHAR(1);

            SELECT @check_character = LOWER(SUBSTRING(@input,@current_position,1));
            SELECT @previous_character = SUBSTRING(@input,@current_position
                                                          - 1,1);

            IF @previous_character NOT LIKE '[a-z]'
               AND @check_character LIKE '[a-z]'
            BEGIN
                SELECT @output_character = UPPER(@check_character);
            END;
                ELSE
            BEGIN
                SELECT @output_character = @check_character;
            END;

            SET @output = @output
                          + @output_character;
            SET @current_position = @current_position
                                    + 1;
        END;

        RETURN @output;

    END;


GO

Question - What scripting option(s) do I need to change in SSMS 18 so it does not add the N' prefix and double-quote ''string literals'' in the output script generated using Right Click->Script Function as->CREATE TO or Right Click->Script Function as->ALTER TO

This option works but I do not wish to drop the object Right Click->Script Function as->DROP and CREATE TO

PS: I can't remember if I had changed something under scripting options in SSMS 2016 to make this happen or if it was the default setting. Thanks!

Answer 1

You could change this behavior under:

SSMS->Tools->Options->SQL Server Object Explorer->Scripting->Object scripting options

For SSMS 16, the property Include IF NOT EXISTS clause should be set to False

^{Image source: https://social.microsoft.com/Forums/getfile/14300/}

For SSMS 18, the property Check for object existence should be set to False