server查询可编制程序对象定义的秘技对待以至构

本文目录列表:

本文目录列表:

1、sql server查看可编制程序对象定义的方法比较

1、sys.sp_helptext存款和储蓄的功力和成效

2、整合实现全部可编制程序对象定义的查阅功用的仓库储存dbo.usp_helptext2

2、重构sys.sp_helptext存储(命名为dbo.usp_helptext)提供直观的意义

3、dbo.helptext2的选拔性测验

3、sys.sp_helptext和dbo.usp_helptext的限量乃至减轻方案

4、总结语

4、总结语

5、仿照效法清单列表

5、参谋清单列表

 

 

1、sql server查看可编程对象定义的艺术比较

1、sys.sp_helptext存款和储蓄的作用和功能

 

 

上一篇博文重构sql server的sys.helptext存储中写了sys.helptext的限量和输出格式每行自带char(13)和char(10)这八个字符。为了将可编制程序对象定义查询艺术钻探透顶,以下表格列出了询问可编程对象定义的例外格局的却别和对可编制程序对象定义查看的帮忙程度。

近期在切磋sql server提供的具体可编制程序对象定义体的主意富含:sys.syscomments(视图)、sys.all_sql_modules(sys.sql_modules)(视图)、object_definition(函数)和sys.sp_helptext(存款和储蓄)。针对上述措施的例外今后有的时候间在写成博文。本文首要商量了sys.sp_helptext的来得效果,认为某个不太美好。先看该存款和储蓄的现实性意义如下图:

对象类型描述 对象类型简写
sys.sp_helptext
sys.sql_modules
sys.system_sql_modules
sys.all_sql_modules
object_definition
CHECK_CONSTRAINT
C
支持 不支持
不支持
不支持
支持
DEFAULT_CONSTRAINT(contraint,stand-alone)
D
支持 支持
不支持
支持
支持
SQL_SCALAR_FUNCTION
FN
支持
支持
支持
支持
支持
SQL_INLINE_TABLE_VALUED_FUNCTION
IF
支持
支持
支持
支持
支持
SQL_STORED_PROCEDURE
P
支持
支持 支持
支持
支持
RULE(old-style,stand-alone)
R
支持
支持
不支持
支持
支持
REPLICATION FILTER PROCEDURE
RF
支持
支持
支持
支持
支持
SQL_TABLE_VALUED_FUNCTION
TF
支持
支持
支持
支持
支持
SQL_TRIGGER
TR
支持(除数据库DDL触发器和服务器触发器外)
支持(除服务器触发器外)
不支持
支持(除服务器触发器外)
支持(除服务器触发器外)
USER_TABLE
U
computed_column
支持
不支持
不支持
不支持
不支持
VIEW
V
支持
支持
支持
支持
支持

图片 1

注意:

图片 2

1、带有_modules的是系统提供的目录视图类。

上海教室以后看未有何的,那就将如下图的Text字段列内容复制归入单独的公文中再看其职能如下图:

2、sys.sql_modules满含持有客户定义的可编制程序对象的,当然也不扶助总计列和服务器触发器的。

图片 3

3、sys.system_sql_modules不协助系统定义的项目为C、D、君越、TCR-V的对象。

图片 4

4、sys.all_sql_modules就是sys.sql_modules和sys.system_sql_modules这几个视图的并集合果的,当然也不帮助系统定义的种类为C、D、RAV4、T瑞鹰类型的指标。

上图作者白灰矩形框注解的地点了啊,每一个行后都增添了char(13)和char(10)那七个字符导致的那样的显得效果,倘使遵照这一个结果为根基进行改动,就扩展了可编制程序对象定义的尺寸(首假设char(13)和char(10))。

5、sys.sp_helptext尽管扶持上述表格中除数据库DDL触发器和服务器触发器之外的可编制程序对象,可是其出口格式有限制:1、每行最多225双字节字符号,那样有望引致输出将贰个标志符分割为上下两行的。

 

6、object_definition函数扶持上述表格中除服务器除触发器和总括列外的可以成为对象。

2、重构sys.sp_helptext存储(命名为dbo.usp_helptext)提供直观的作用

7、sys.sp_helpttext和object_definition有个效果与利益的限量:1、在SSMS顾客端中若是使用字符串类型变量接收重临的而结果,有相当的大概率受制于SSMS顾客端针对字符串变量的最大面积(sql server 二〇一二中的最大规模是43679双字节字符长度)不可能一体出口到客商端,那个毛病能够从通进程序编码实现获得完善体现。

 

 

发现了sys.sp_helptext的显得效果,小编要好感觉不太如意,那么就重构嘛。重构后的代码如下:

2、整合完结全数可编制程序对象定义的查看功效的存款和储蓄dbo.usp_helptext2

 

 

if object_id(N'dbo.usp_helptext', 'P') IS NOT NULL
begin
    drop procedure [dbo].[usp_helptext];
end
go

create procedure [dbo].[usp_helptext]
(
     @objname nvarchar(776)
    ,@columnname sysname = NULL
    ,@keeporiginal bit = NULL
)
as
begin
    set nocount on

    set @keeporiginal = ISNULL(@keeporiginal, 1);    

    declare @dbname sysname
    ,@objid    int
    ,@BlankSpaceAdded   int
    ,@BasePos       int
    ,@CurrentPos    int
    ,@TextLength    int
    ,@LineId        int
    ,@AddOnLen      int
    ,@LFCR          int --lengths of line feed carriage return
    ,@DefinedLength int

    /* NOTE: Length of @SyscomText is 4000 to replace the length of
    ** text column in syscomments.
    ** lengths on @Line, #CommentText Text column and
    ** value for @DefinedLength are all 255. These need to all have
    ** the same values. 255 was selected in order for the max length
    ** display using down level clients
    */
    ,@SyscomText    nvarchar(4000)
    ,@Line          nvarchar(255)

    select @DefinedLength = 255
    select @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores
                                    trailing blank spaces*/
    CREATE TABLE #CommentText
    (LineId    int
        ,Text  nvarchar(255) collate catalog_default)

    /*
    **  Make sure the @objname is local to the current database.
    */
    select @dbname = parsename(@objname,3)
    if @dbname is null
        select @dbname = db_name()
    else if @dbname <> db_name()
            begin
                    raiserror(15250,-1,-1)
                    return (1)
            end

    /*
    **  See if @objname exists.
    */
    select @objid = object_id(@objname)
    if (@objid is null)
            begin
            raiserror(15009,-1,-1,@objname,@dbname)
            return (1)
            end

    -- If second parameter was given.
    if ( @columnname is not null)
        begin
            -- Check if it is a table
            if (select count(*) from sys.objects where object_id = @objid and type in ('S ','U ','TF'))=0
                begin
                    raiserror(15218,-1,-1,@objname)
                    return(1)
                end
            -- check if it is a correct column name
            if ((select 'count'=count(*) from sys.columns where name = @columnname and object_id = @objid) =0)
                begin
                    raiserror(15645,-1,-1,@columnname)
                    return(1)
                end
        if (ColumnProperty(@objid, @columnname, 'IsComputed') = 0)
            begin
                raiserror(15646,-1,-1,@columnname)
                return(1)
            end

            declare ms_crs_syscom  CURSOR LOCAL
            FOR select text from syscomments where id = @objid and encrypted = 0 and number =
                            (select column_id from sys.columns where name = @columnname and object_id = @objid)
                            order by number,colid
            FOR READ ONLY

        end
    else if @objid < 0    -- Handle system-objects
        begin
            -- Check count of rows with text data
            if (select count(*) from master.sys.syscomments where id = @objid and text is not null) = 0
                begin
                    raiserror(15197,-1,-1,@objname)
                    return (1)
                end

            declare ms_crs_syscom CURSOR LOCAL FOR select text from master.sys.syscomments where id = @objid
                ORDER BY number, colid FOR READ ONLY
        end
    else
        begin
            /*
            **  Find out how many lines of text are coming back,
            **  and return if there are none.
            */
            if (select count(*) from syscomments c, sysobjects o where o.xtype not in ('S', 'U')
                and o.id = c.id and o.id = @objid) = 0
                    begin
                            raiserror(15197,-1,-1,@objname)
                            return (1)
                    end

            if (select count(*) from syscomments where id = @objid and encrypted = 0) = 0
                    begin
                            raiserror(15471,-1,-1,@objname)
                            return (0)
                    end

            declare ms_crs_syscom  CURSOR LOCAL
            FOR select text from syscomments where id = @objid and encrypted = 0
                    ORDER BY number, colid
            FOR READ ONLY

        end

    /*
    **  else get the text.
    */
    select @LFCR = 2
    select @LineId = 1

    open ms_crs_syscom

    fetch next from ms_crs_syscom into @SyscomText

    while @@fetch_status >= 0
    begin
        select  @BasePos    = 1
        select  @CurrentPos = 1
        select  @TextLength = LEN(@SyscomText)

        while @CurrentPos  != 0
        begin
            --Looking for end of line followed by carriage return
            select @CurrentPos =   CHARINDEX(char(13) char(10), @SyscomText, @BasePos)

            --If carriage return found
            IF @CurrentPos != 0
            begin
                /*If new value for @Lines length will be > then the
                **set length then insert current contents of @line
                **and proceed.
                */
                while (isnull(LEN(@Line),0)   @BlankSpaceAdded   @CurrentPos-@BasePos   @LFCR) > @DefinedLength
                begin
                    select @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0)   @BlankSpaceAdded)
                    insert #CommentText values
                    ( @LineId,
                        isnull(@Line, N'')   isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                    select @Line = NULL, @LineId = @LineId   1,
                            @BasePos = @BasePos   @AddOnLen, @BlankSpaceAdded = 0
                end

                -- 注释系统原来的使用如下修改
                --select @Line    = isnull(@Line, N'')   isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos   @LFCR), N'')
                select @Line    = isnull(@Line, N'')   isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos   (CASE @keeporiginal WHEN 1 THEN @LFCR ELSE 0 END)), N'')
                select @BasePos = @CurrentPos 2
                insert #CommentText values( @LineId, @Line )
                select @LineId = @LineId   1
                select @Line = NULL
            end
            else
            --else carriage return not found
            begin
                IF @BasePos <= @TextLength
                begin
                    /*If new value for @Lines length will be > then the
                    **defined length
                    */
                    while (isnull(LEN(@Line),0)   @BlankSpaceAdded   @TextLength-@BasePos 1 ) > @DefinedLength
                    begin
                        select @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0)   @BlankSpaceAdded)
                        INSERT #CommentText VALUES
                        ( @LineId,
                            isnull(@Line, N'')   isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                        select @Line = NULL, @LineId = @LineId   1,
                            @BasePos = @BasePos   @AddOnLen, @BlankSpaceAdded = 0
                    end
                    select @Line = isnull(@Line, N'')   isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos 1 ), N'')
                    if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength 1 ) > 0
                    begin
                        select @Line = @Line   ' ', @BlankSpaceAdded = 1
                    end
                end
            end
        end

        FETCH NEXT from ms_crs_syscom into @SyscomText
    end

    IF @Line is NOT NULL
        INSERT #CommentText VALUES( @LineId, @Line )

    select Text from #CommentText order by LineId

    CLOSE  ms_crs_syscom
    DEALLOCATE     ms_crs_syscom

    DROP TABLE     #CommentText    

    return (0) -- sp_helptext
end
go

通过以上两种方法的对待,大家得以看来那么些情势都不能够将以上表格中列出的目的类型的概念全部都满意,为了化解这几个不足,我们将结合这一个成效来封装在三个囤积(其名目为dbo.usp_helptext2)。需求当心的某个正是服务器触发器本来不是某些数据库中的对象的,服务器触发器和数据库中可编制程序对象分别越来越好的敞亮,也能大致些的,但是为了查询的便利性,我们此番封装的仓库储存达成服务器触发器定义查看。

 

 

以上修改之处作者早已标记了,别的的均来源sys.sp_helptext内容。

效率结合的存款和储蓄进程T-SQL代码如下:

那就看注重构后的作用,如下图:

IF OBJECT_ID(N'[dbo].[usp_helptext2]', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[usp_helptext2];
END
GO

--==================================
-- 功能: 查看可编程对象定义
-- 说明: 支持用户定义类型,可以运行于SQL Server 2005 
-- 创建: yyyy-MM-dd hh:mm-hh:mm XXX 创建内容描述
-- 修改: yyyy-MM-dd hh:mm-hh:mm XXX 修改内容描述
--==================================
CREATE PROCEDURE [dbo].[usp_helptext2]
(
     @nvcObjectName AS NVARCHAR(776)                    -- 对象名称,可以支持的对象类型为(C、D、FN、IF、P、R、RF、TF、TR、U、V)
    ,@nvcComputedColumnName AS NVARCHAR(128) = NULL        -- 计算列名称(如果@nvcObjectName的对象类型为U,则该参数表示计算列名称)
)
AS
BEGIN
    SET NOCOUNT ON;

    SET @nvcObjectName = ISNULL(@nvcObjectName, N'');
    IF (@nvcObjectName = N'')
    BEGIN
        RAISERROR(16902, -1, -1,N'usp_helptext2', N'@nvcObjectName');
        RETURN(1);
    END

    SET @nvcComputedColumnName = ISNULL(@nvcComputedColumnName, N'');

    DECLARE @tntRetVal AS TINYINT;
    SET @tntRetVal = 0;

    DECLARE @tblObjDef AS TABLE (
        [Text] NVARCHAR(1000) NULL
    );

    DECLARE
         @intObjectID AS INT
        ,@chaType AS CHAR(2)
        ,@nvcText AS NVARCHAR(MAX);
    SELECT
         @intObjectID = 0
        ,@chaType = ''
        ,@nvcText = N'';
    SELECT
         @intObjectID = [object_id]
        ,@chaType = [type]        
    FROM [sys].[all_objects]
    WHERE 
        [type] IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'U', 'V')
        AND [name] = PARSENAME(@nvcObjectName, 1);

    IF (@nvcComputedColumnName > N'')    -- 获取计算列定义
    BEGIN
        IF (@chaType NOT IN ('S', 'U', 'TF'))
        BEGIN
            RAISERROR(15218, -1, -1, @nvcObjectName);
            RETURN(1);
        END

        INSERT INTO @tblObjDef ([Text])        
        EXEC [sys].[sp_helptext]
             @objname = @nvcObjectName                -- nvarchar(776)
            ,@columnname = @nvcComputedColumnName    -- sysname

        IF(@@ROWCOUNT = 0)
        BEGIN
            SET @tntRetVal = 1;
        END

        SELECT 
            @nvcText = ISNULL([Text], N'')
        FROM @tblObjDef;            
    END
    ELSE IF (@intObjectID <> 0)    -- 获取除计算列和服务器触发器以外的所有对象类型的定义
    BEGIN
        SET @nvcText = OBJECT_DEFINITION(@intObjectID);

        IF(@@ROWCOUNT = 0)
        BEGIN
            SET @tntRetVal = 1;
        END
    END
    ELSE IF (@intObjectID = 0)    -- 尝试获取服务器触发器定义
    BEGIN
        SELECT 
            @nvcText = T1.[definition]
        FROM [sys].[server_sql_modules] AS T1
            INNER JOIN [sys].[server_triggers] AS T2
                ON [T1].[object_id] = [T2].[object_id]
        WHERE T2.[name] = @nvcObjectName;

        IF(@@ROWCOUNT = 0)
        BEGIN
            SET @tntRetVal = 1;
        END
    END

    SELECT 
        @nvcText AS [Text];

    RETURN(@tntRetVal);
END
GO

图片 5

 

图片 6

以上存储dbo.usp_helptext2能够完全达成上述表格的富有可编制程序对象定义查看,不论是系统定义的或许客户定义的,前提是上述表格中的可编制程序对象类型定义。当然也存在劣势正是可编制程序对象定义输出到SSMS客商端当先最大规模(SQL Server 二〇一二条件中的时43679双字节字符长度)将在出现截断,那一个毛病能够通过代码编制程序来完善消除那一个毛病。

如上呈现并看不出和sys.sp_helptext的有啥区别,继续讲Text内容复制放入单独为文件中效用如下图:

 

图片 7

**3、dbo.helptext2的采纳性测量检验
**

图片 8

 

上图黄褐矩形框正是体现的效果,下局部是为着相比较,那有的能够采用如下代码显示屏效果:

顾客定义检查约束测量试验:

EXEC [sys].[sp_helptext]
     @objname = N'sys.fn_get_sql'    -- nvarchar(776)
    ,@columnname = NULL -- sysname
GO

EXEC [dbo].[usp_helptext]
     @objname = N'sys.fn_get_sql'    -- nvarchar(776)
    ,@columnname = NULL -- sysname
    ,@keeporiginal = 1 -- bit
GO

图片 9

 

图片 10

 

客商定义约束测验:

注意:dbo.usp_helptext兼容了sys.sp_helptext的功能。

图片 11

 

图片 12

3、sys.sp_helptext和dbo.usp_helptext的范围以致施工方案

系统定义存款和储蓄测量试验:

 

图片 13

查阅了sys.sp_helptext的源码和其相应的联机援救文书档案,开掘其出口的字段列Text每行最多253个双字节字符,其出口到客商端最后的深浅是五千个双字节字符,这一个能够因此编码程序(举例VS程序读取获取等)突破那么些限制。

图片 14

 

顾客定义总计列测量检验:

其最大的劣势是每行251个,有极大只怕境遇一行中八个相隔符前有的属于前贰个2五十二个双字节字符,后一有的属于后叁个255双字节字符的前有些。

图片 15

现实的测量检验代码如下:

图片 16

IF OBJECT_ID(N'[dbo].[uvm_MyTestView]', 'V') IS NOT NULL
BEGIN
    DROP VIEW [dbo].[uvm_MyTestView];
END
GO    

CREATE VIEW [dbo].[uvm_MyTestView]
AS
    SELECT 
          1 AS N'Col_1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111', 2 AS [Col_2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222]
GO

 

 

数据库DDL触发器测量检验

效率体现如下图:

图片 17

图片 18

图片 19

图片 20

服务器触发器测量试验:

上海图书馆彰显的功能就是出新了截断。化解这一个范围那就动用函数object_definition(可是那么些也许有劣点的,现在才单独讲明输出可编制程序对象定义的内容的界别)。

图片 21

 

图片 22

4、总结语

其他对象类型的测验不在全部列举。

 

 

读书sql server提供的种类对象,发掘他们写的代码真的很严密的,比比较多行业内部以致特别管理等,确实学到了过多的,不过也开采有一点sql server的中间限制是不写出来的,如表记录行最大8060字节的限制以致可边长长度溢出到row-overflow索引分配项指标数据页后也在原先的记录行中加进24字节的指针那样也可有能超过行最大8060字节的范围。恐怕本身看理论太少的原由吧。独有继续精进,代码编制程序依然要继续的,不常候sql server顾客端输出的最大6000个双字节字符的限定能够经过编制程序的措施得到完本的缓慢解决。

4、总结语

 

 

明天旁观园中的一篇博文print、sp_helptext的限制与扩大经过PPAJEROINT输出分批次打字与印刷超长的字符串,也会际遇某些标志符截断的主题素材,因为PPAJEROINT每一次到打印到客户端总扩展了char(13)和char(10)那四个字符,那样就大概将二个标识符分割为上下三个批次。

在这里次的求学和切磋,sql server系统自带的视图以至存款和储蓄进度针对可编制程序对象的兑现很很周详的,然而叶分散在差异的地点,此番结合也正是将分流在分歧地点的集结在一同提供统一入口来拍卖。借使不想查看计算列和服务器触发器的概念以外的具有可编程对象类型的定义,提出采取object_definition函数,该函数大约提供了很圆满的机能。此次学习也发觉数据库DDL触发器在sys.object是力不能够支查看见的,必要在sys.triggers或sys.all_objects目录视图中查看见,这一个也在object_id函数做了限定的。由于服务器触发器自个儿属于服务器的,那几个sql server团队本身也是用了系统表sys.sysschobjs、sys.syspalnames 、sys.syspalvalues,固然sys.all_objects也是用了系统表sys.syscheobjs,可是却在sys.all_objects中不能够查询到的,也在object_id函数中做了限制,只好在sys.server_triggers查询到,那从逻辑上海展览中心开了分别,也合乎服务器触发器的着落性质。

 

 

5、参谋清单列表

期望以此组成的查阅可编制程序对象定义的积累,能够扶植到需求的人。继续精进,继续切磋sql server。

 

 

5、参照他事他说加以考察清单列表

  •  

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:server查询可编制程序对象定义的秘技对待以至构

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。