server查询可编程对象定义的方式对比以及整合

本文目录列表:

本文目录列表:

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

1、sql server查看可编制程序对象定义的点子相比较

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

2、整合完结全体可编制程序对象定义的查看功用的存放dbo.usp_helptext2

3、sys.sp_helptext和dbo.usp_helptext的限制以致缓和方案

3、dbo.helptext2的选拔性测量试验

4、总结语

4、总结语

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

5、仿照效法清单列表

 

 

1、sys.sp_helptext存款和储蓄的成效和机能

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

 

 

不久前在钻探sql server提供的现实可编制程序对象定义体的主意包含:sys.syscomments(视图)、sys.all_sql_modules(sys.sql_modules)(视图)、object_definition(函数)和sys.sp_helptext(存款和储蓄)。针对以上办法的例外今后有的时候光在写成博文。本文重要商讨了sys.sp_helptext的显得效果,认为有个别不太美好。先看该存储的求实际效果果与利益如下图:

上一篇博文重构sql server的sys.helptext存储中写了sys.helptext的限定和出口格式每行自带char(13)和char(10)那多少个字符。为了将可编制程序对象定义查询办法钻探透顶,以下表格列出了查询可编制程序对象定义的不相同措施的却别和对可编制程序对象定义查看的支撑程度。

图片 1

对象类型描述 对象类型简写
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
支持
支持
支持
支持
支持

图片 2

注意:

上海体育场所以后看未有啥的,那就将如下图的Text字段列内容复制放入单独的公文中再看其意义如下图:

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

图片 3

2、sys.sql_modules包蕴全部顾客定义的可编制程序对象的,当然也不协理总结列和服务器触发器的。

图片 4

3、sys.system_sql_modules不扶持系统定义的门类为C、D、Exige、T景逸SUV的指标。

上海图书馆作者浅奶油色矩形框标明的地点了啊,每个行后都加多了char(13)和char(10)那八个字符导致的如此的显得效果,如果依据这些结果为根基举办改换,就充实了可编制程序对象定义的长短(首若是char(13)和char(10))。

4、sys.all_sql_modules就是sys.sql_modules和sys.system_sql_modules那几个视图的并集合果的,当然也不援救系统定义的系列为C、D、宝马X5、T陆风X8类型的靶子。

 

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

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

6、object_definition函数协助上述表格中除服务器除触发器和计量列外的能够形成对象。

 

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代码如下:

图片 5

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

图片 6

 

以上显示并看不出和sys.sp_helptext的有啥不一致,继续讲Text内容复制归入单独为文件中成效如下图:

如上存款和储蓄dbo.usp_helptext2能够完全完结以上表格的有所可编制程序对象定义查看,不论是系统定义的照旧客户定义的,前提是以上表格中的可编制程序对象类型定义。当然也设有缺点便是可编制程序对象定义输出到SSMS顾客端抢先最大规模(SQL Server 二〇一二蒙受中的时43679双字节字符长度)就要出现截断,那几个毛病能够通过代码编制程序来周全化解这一个毛病。

图片 7

 

图片 8

**3、dbo.helptext2的选用性测验
**

上航海用教室水晶色矩形框就是呈现的功效,下一些是为了相比,这一部分能够运用如下代码荧屏效果:

 

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

3、sys.sp_helptext和dbo.usp_helptext的界定以致缓慢解决方案

图片 12

 

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

查阅了sys.sp_helptext的源码和其相应的联机扶助文书档案,发掘其出口的字段列Text每行最多251个双字节字符,其出口到客商端最终的高低是五千个双字节字符,这么些能够通过编码程序(举个例子VS程序读取获取等)突破那个范围。

图片 13

 

图片 14

其最大的弱项是每行255个,有望碰着一行中叁个相间符前部分属于前一个252个双字节字符,后一有个别属于后八个255双字节字符的前有的。

客户定义计算列测量试验:

切实的测量试验代码如下:

图片 15

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

图片 16

 

 

成效体现如下图:

数据库DDL触发器测量检验

图片 17

图片 18

图片 19

图片 20

上海体育场合显示的效果正是出新了截断。解决那一个限制那就使用函数object_definition(然则那些也会有劣势的,现在才单独批注输出可编制程序对象定义的剧情的界别)。

服务器触发器测量试验:

 

图片 21

4、总结语

图片 22

 

任何对象类型的测验不在全体罗列。

上学sql server提供的种类对象,开采她们写的代码真的很紧密的,比较多正经乃至特别管理等,确实学到了累累的,不过也意识有一点点sql server的里边限制是不写出来的,如表记录行最大8060字节的限量以致可边长长度溢出到row-overflow索引分配项指标数据页后也在原本的记录行中加进24字节的指针那样也可有能超过行最大8060字节的界定。恐怕自身看理论太少的案由吧。独有继续精进,代码编制程序依旧要三番五次的,有的时候候sql server顾客端输出的最大伍仟个双字节字符的范围能够因此编制程序的点子取得完本的化解。

 

 

4、总结语

前几日观察园中的一篇博文print、sp_helptext的界定与扩大经过POdysseyINT输出分批次打印超长的字符串,也会赶过有个别标志符截断的主题材料,因为P奥德赛INT每一次到打字与印刷到客商端总扩大了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 将本页面保存为书签,全面了解最新资讯,方便快捷。