sql - SSMS 脚本选项

问题描述

使用SSMS 18.0 Preview 6编写函数脚本会生成以下脚本。

/****** 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

使用SSMS 2016编写相同函数的脚本会生成此脚本

/****** 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

问题 - 我需要在 SSMS 18 中更改哪些脚本选项，以便它不会在使用或生成的输出脚本中添加N'前缀和双引号''string literals''Right Click->Script Function as->CREATE TORight Click->Script Function as->ALTER TO

此选项有效，但我不希望删除该对象Right Click->Script Function as->DROP and CREATE TO

PS：我不记得我是否在 SSMS 2016 的脚本选项下更改了某些内容以实现这一点，或者它是否是默认设置。谢谢！

标签： sqlsql-serverssmsssms-2016ssms-18

您可以在以下情况下更改此行为：

SSMS->工具->选项->SQL Server 对象资源管理器->脚本->对象脚本选项

对于 SSMS 16，该属性Include IF NOT EXISTS clause应设置为False

^{图片来源：https ://social.microsoft.com/Forums/getfile/14300/}

对于 SSMS 18，该属性Check for object existence应设置为False

sql - SSMS 脚本选项

问题描述

解决方案

推荐阅读