• 存储过程--自定义辅助过程--查看数据库对象(过程,表), 查找存储过程


    -- 查看数据库对象定义: 

    比如存储过程:

    /*----------------------------------------------------------*/
    /*    [sp_TextDef]                                          */
    /*----------------------------------------------------------*/
    IF EXISTS ( SELECT 1 FROM sys.objects o WHERE object_id = object_id( N'[sp_TextDef]' ) AND OBJECTPROPERTY( object_id, N'IsProcedure') = 1 )
    DROP PROCEDURE [sp_TextDef]
    GO
    CREATE PROC [dbo].[sp_TextDef]
    (
    @ObjectName nvarchar(256) = NULL 
    )
    AS
    /*
    2009-07-10 19:20
    */
    BEGIN
       SET NOCOUNT ON
       DECLARE @default_schema_name nvarchar(128)
       
       SELECT @default_schema_name = default_schema_name 
         FROM sys.database_principals 
        WHERE name  = user_name() 
       
       SELECT @ObjectName = RTRIM( LTRIM( @ObjectName ) )
       IF( LEFT( @ObjectName, 1 ) = '[' AND RIGHT( @ObjectName, 1 ) = ']' )
       BEGIN
          SELECT @ObjectName = REPLACE( REPLACE( @ObjectName, '[', '' ), ']', '' )
       END
       
       DECLARE @id INT
       DECLARE @schemaname nvarchar(256)
       DECLARE @Text nvarchar(MAX)
       DECLARE @name nvarchar(256)
       DECLARE @Type nvarchar(256)
       --DECLARE @temp nvarchar(4000)
       DECLARE @pos INT, @pos1 INT
       DECLARE name_cursor CURSOR FOR 
         SELECT schema_name( o.schema_id ), 
                o.object_id, 
                o.name, 
                o.type 
           FROM sys.all_objects o 
          WHERE ( o.type IN ( 'P', 'FN', 'IF', 'TF', 'TR', 'V' ) ) AND 
                ( o.is_ms_shipped = 0 ) AND /*
                ( LEFT( o.name, 2 ) Like 'sp' OR 
                  LEFT( o.name, 4 ) Like 'tmpA[0-9]' OR 
                  LEFT( o.name, 2 ) Like 'A[0-9]' OR 
                  LEFT( o.name, 2 ) Like 'P[0-9]' OR 
                  LEFT( o.name, 2 ) Like 'F[0-9]' OR 
                  LEFT( o.name, 2 ) Like 'T[0-9]' OR 
                  LEFT( o.name, 2 ) Like 'V[0-9]' ) AND */
                ( o.name LIKE @ObjectName OR @ObjectName IS NULL ) 
       ORDER BY SCHEMA_NAME( o.schema_id ), 
                CASE o.type WHEN 'V' THEN 2
                            WHEN 'FN' THEN 3
                            WHEN 'IF' THEN 4
                            WHEN 'TF' THEN 5
                            WHEN 'P' THEN 6
                            WHEN 'TR' THEN 7 
                            ELSE 9 END, 
                o.name 
       OPEN name_cursor 
       FETCH NEXT FROM name_cursor 
       INTO @schemaname, @id, @name, @Type 
       DECLARE @C_F INT, 
               @C_P INT, 
               @C_TR INT, 
               @C_V INT 
       SELECT @C_F = 0, 
              @C_P = 0, 
              @C_TR = 0, 
              @C_V = 0 
       DECLARE @drop_name nvarchar(256), 
               @Is_name nvarchar(256) 
       WHILE @@FETCH_STATUS = 0
       BEGIN
          IF( @Type = 'P' )
          BEGIN
             SELECT @C_P = @C_P + 1, 
                    @Is_name = 'OBJECTPROPERTY( object_id, N''IsProcedure'') = 1', 
                    @drop_name = 'PROCEDURE'
          END
          ELSE IF( @type = 'FN' OR @type = 'IF' OR @type = 'TF' )
          BEGIN
             SELECT @C_F = @C_F + 1, 
                    @Is_name = 'OBJECTPROPERTY( object_id, N''IsTableFunction'') IS NOT NULL', 
                    @drop_name = 'FUNCTION'
          END
          ELSE IF( @type = 'TR' )
          BEGIN
             SELECT @C_TR = @C_TR + 1, 
                    @Is_name = 'OBJECTPROPERTY( object_id, N''IsTrigger'') = 1', 
                    @drop_name = 'TRIGGER'
          END
          ELSE IF( @type = 'V' )
          BEGIN
             SELECT @C_V = @C_V + 1, 
                    @Is_name = 'OBJECTPROPERTY( object_id, N''IsView'') = 1', 
                    @drop_name = 'VIEW'
          END
          
          IF( @Type <> 'U' ) 
          BEGIN
             SELECT @schemaname = CASE @schemaname WHEN @default_schema_name THEN '' ELSE '[' + @schemaname + '].' END
             
             PRINT '/*----------------------------------------------------------*/'
             PRINT '/*    ' + @schemaname + '[' + @name + ']' + space( 54 - 2 - LEN( @name ) ) + '*/'
             PRINT '/*----------------------------------------------------------*/'
             
             PRINT 'IF EXISTS ( SELECT 1 FROM sys.objects o WHERE object_id = object_id( N''' + @schemaname + '[' + @name + ']'' ) AND ' + @Is_name + ' )'
             PRINT 'DROP ' + @drop_name + ' ' + @schemaname + '[' + @name + ']' 
             PRINT 'GO'
             --DECLARE @FirstLine nvarchar( 4000 ), 
             --        @MidLine nvarchar( 4000 ),
             --        @ENDLine nvarchar( 4000 )
             --SELECT @temp = '', 
             --       @FirstLine = '', 
             --       @MidLine = '', 
             --       @ENDLine = '' 
             
             SELECT @Text = [definition] 
               FROM sys.sql_modules 
              WHERE object_id = @id 
             
             WHILE( CHARINDEX( ' ' + CHAR(13) + CHAR(10) + 'CREATE PROC', @Text ) > 0 )
             BEGIN
                SELECT @Text = REPLACE( @Text, ' ' + CHAR(13) + CHAR(10) + 'CREATE PROC', 'CREATE PROC' ) 
             END
             WHILE( CHARINDEX( ' ' + CHAR(13) + CHAR(10) + 'CREATE TRIGGER', @Text ) > 0 )
             BEGIN
                SELECT @Text = REPLACE( @Text, ' ' + CHAR(13) + CHAR(10) + 'CREATE TRIGGER', 'CREATE TRIGGER' )
             END
             WHILE( CHARINDEX( ' ' + CHAR(13) + CHAR(10) + 'CREATE FUNC', @Text ) > 0 )
             BEGIN
                SELECT @Text = REPLACE( @Text, ' ' + CHAR(13) + CHAR(10) + 'CREATE FUNC', 'CREATE FUNC' ) 
             END
             WHILE( CHARINDEX( ' ' + CHAR(13) + CHAR(10) + 'CREATE VIEW', @Text ) > 0 )
             BEGIN
                SELECT @Text = REPLACE( @Text, ' ' + CHAR(13) + CHAR(10) + 'CREATE VIEW', 'CREATE VIEW' ) 
             END
             
             declare @i int
             SELECT @pos = 1, 
                    @pos1 = 0, 
                    @i = 0 
             while( len( @Text ) > ( @i + 1 ) * 2000 ) 
             begin
                select @pos1 = charindex( char(13) + char(10), @Text, ( @i + 1 ) * 2000 ) 
                if( @pos > 1 )
                begin
                   select @pos = @pos + 1 
                end
                print substring( @Text, @pos, @pos1 - @pos ) 
                select @i = @i + 1 
                --select @i, @pos , @pos1 
                select @pos = @pos1 + 1
             end
             --select @i, @pos , @pos1 
             print substring( @Text, @pos, 2000 ) 
             PRINT 'GO'
          END
          FETCH NEXT FROM name_cursor 
          INTO @schemaname, @id, @name, @type 
       END
       CLOSE name_cursor
       DEALLOCATE name_cursor 
       
       SELECT @C_F AS [FUNC], 
              @C_P AS [PROC], 
              @C_TR AS [TRIG], 
              @C_V AS [VIEW] 
    
       
         SELECT schema_name( o.schema_id ), 
                o.object_id, 
                o.name, 
                o.type, 
                CASE o.type WHEN 'V' THEN 2
                            WHEN 'FN' THEN 3
                            WHEN 'IF' THEN 4
                            WHEN 'TF' THEN 5
                            WHEN 'P' THEN 6
                            WHEN 'TR' THEN 7 END as ListOrder
           FROM sys.all_objects o 
          WHERE ( o.type IN ( 'P', 'FN', 'IF', 'TF', 'TR', 'V' ) ) AND 
                ( o.is_ms_shipped = 0 ) AND /*
                ( LEFT( o.name, 2 ) Like 'sp' OR 
                  LEFT( o.name, 4 ) Like 'tmpA[0-9]' OR 
                  LEFT( o.name, 2 ) Like 'A[0-9]' OR 
                  LEFT( o.name, 2 ) Like 'P[0-9]' OR 
                  LEFT( o.name, 2 ) Like 'F[0-9]' OR 
                  LEFT( o.name, 2 ) Like 'T[0-9]' OR 
                  LEFT( o.name, 2 ) Like 'V[0-9]' ) AND */
                ( o.name LIKE @ObjectName OR @ObjectName IS NULL ) 
       ORDER BY SCHEMA_NAME( o.schema_id ), 
                CASE o.type WHEN 'V' THEN 2
                            WHEN 'FN' THEN 3
                            WHEN 'IF' THEN 4
                            WHEN 'TF' THEN 5
                            WHEN 'P' THEN 6
                            WHEN 'TR' THEN 7 
                            ELSE 9 END, 
                o.name 
       
       SET NOCOUNT OFF
    END
    GO
    

    -- 查找存储过程等关联内容:  使用方式;  

    EXEC dbo.sp_TextFind  @Text = N'%W1Wage%' -- nvarchar(512)

    /*----------------------------------------------------------*/
    /*    [sp_TextFind]                                         */
    /*----------------------------------------------------------*/
    IF EXISTS ( SELECT 1 FROM sys.objects o WHERE object_id = object_id( N'[sp_TextFind]' ) AND OBJECTPROPERTY( object_id, N'IsProcedure') = 1 )
    DROP PROCEDURE [sp_TextFind]
    GO
    CREATE PROC [dbo].[sp_TextFind] 
    (
    @Text nvarchar(512)
    )
    AS
    /*
    功能:查找包含字符的数据库对象
    参数:@Text nvarchar(512) 要查找的字符串
    返回:数据库对象列表
    */
    BEGIN
       SET NOCOUNT ON
         SELECT sys.sysobjects.id, 
                sys.sysusers.name, 
                sys.sysobjects.name, 
                sys.sysobjects.type, 
                'EXEC sp_TextGet ''' + sys.sysobjects.name + '''' AS SQL, 
                sys.sysobjects.crdate, 
                sys.sysobjects.refdate 
           FROM sys.sysobjects, 
                sys.sysusers 
          WHERE sys.sysobjects.uid = sys.sysusers.uid and 
                ( sys.sysobjects.type = 'U'  and sys.sysobjects.id in ( SELECT id FROM sys.syscolumns WHERE sys.syscolumns.name like '%' + @Text + '%' ) or 
                  sys.sysobjects.type = 'P'  and sys.sysobjects.id in ( SELECT id FROM sys.syscomments WHERE CHARINDEX( @Text, sys.syscomments.text ) > 0 ) or 
                  sys.sysobjects.type = 'IF' and sys.sysobjects.id in ( SELECT id FROM sys.syscomments WHERE CHARINDEX( @Text, sys.syscomments.text ) > 0 ) or 
                  sys.sysobjects.type = 'FN' and sys.sysobjects.id in ( SELECT id FROM sys.syscomments WHERE CHARINDEX( @Text, sys.syscomments.text ) > 0 ) or 
                  sys.sysobjects.type = 'TF' and sys.sysobjects.id in ( SELECT id FROM sys.syscomments WHERE CHARINDEX( @Text, sys.syscomments.text ) > 0 ) or 
                  sys.sysobjects.type = 'TR' and sys.sysobjects.id in ( SELECT id FROM sys.syscomments WHERE CHARINDEX( @Text, sys.syscomments.text ) > 0 ) or 
                  sys.sysobjects.type = 'V'  and sys.sysobjects.id in ( SELECT id FROM sys.syscomments WHERE CHARINDEX( @Text, sys.syscomments.text ) > 0 ) ) 
       ORDER BY sys.sysobjects.name, 
                sys.sysobjects.type, 
                sys.sysobjects.refdate, 
                sys.sysobjects.crdate 
                
       SET NOCOUNT OFF
    END
    GO
    


    查看表结构: 


     

    /*----------------------------------------------------------*/
    /*    [V0DataDict]                                          */
    /*----------------------------------------------------------*/
    IF EXISTS ( SELECT 1 FROM sys.objects o WHERE object_id = object_id( N'[V0DataDict]' ) AND OBJECTPROPERTY( object_id, N'IsView') = 1 )
    DROP VIEW [V0DataDict]
    GO
    CREATE VIEW [V0DataDict]
    AS
      SELECT TOP 100 PERCENT * 
      FROM ( SELECT ISNULL( CAST( UserTable.value AS VARCHAR(256) ), '' ) AS 表名, 
             REPLACE( REPLACE( REPLACE( CAST( UserTableColumn.value AS VARCHAR( 256) ), ' ', '' ), CHAR(13), '' ), CHAR( 10 ), '' ) AS 列名, 
             UserTableColumn.colname AS 列编码, 
             CAST( UserTableColumn.colid AS VARCHAR(10) ) AS 列序, 
             UserTableColumn.IsPKey AS 主键, 
             UserTableColumn.typename AS 数据类型, 
             CAST( UserTableColumn.length AS VARCHAR(10) ) AS 宽度, 
             CASE WHEN UserTableColumn.typename NOT IN ( 'decimal', 'numeric', 'money', 'smallmoney', 'float', 'real' ) THEN '' ELSE CAST( ISNULL( UserTableColumn.scale, '' ) AS VARCHAR(10) ) END AS 小数位, 
             CASE WHEN UserTableColumn.typename NOT IN ( 'decimal', 'numeric', 'money', 'smallmoney', 'float', 'real' ) THEN '' ELSE CAST( ISNULL( UserTableColumn.prec, '' ) AS VARCHAR(10) ) END AS 精度, 
             ISNULL( CONVERT(VARCHAR(256), UserTableColumn.text), '' ) AS 默认值, 
             ISNULL( CASE CONVERT( BIT, ( UserTableColumn.status & 8 ) ) WHEN 1 THEN '是' ELSE '' END, '' ) AS 可空, 
             ISNULL( CASE CONVERT( BIT, ( UserTableColumn.status & 0x80 ) ) WHEN 1 THEN '是' ELSE '' END, '' ) AS 自增长, 
             UserTable.name, 
             3 AS ListOrder, 
             UserTableColumn.colid 
        FROM (  select sys.sysobjects.id, 
                       sys.sysobjects.name, 
                       sys.extended_properties.value 
                  from sys.sysobjects 
                       left outer join sys.extended_properties on sys.extended_properties.major_id = sys.sysobjects.id and 
                                                                  sys.extended_properties.minor_id = 0 
                 WHERE sys.sysobjects.type = 'U' /*and 
                       convert( varchar(10), sys.sysobjects.crdate, 121 ) = convert( varchar(10), getdate(), 121 )*/ ) UserTable 
             inner join (  select sys.syscolumns.id, 
                                  sys.syscolumns.colid, 
                                  sys.syscolumns.name as colname, 
                                  sys.extended_properties.value, 
                                  sys.systypes.name as typename , 
                                  sys.syscolumns.length, 
                                  sys.syscolumns.scale, 
                                  sys.syscolumns.prec, 
                                  sys.syscomments.text, 
                                  sys.syscolumns.status, PKeyColumns.object_id, PKeyColumns.column_id, 
                                  CASE WHEN PKeyColumns.column_id IS NULL THEN '' ELSE '是' END AS IsPKey 
                             from sys.syscolumns 
                                  LEFT OUTER JOIN sys.syscomments ON sys.syscolumns.cdefault = sys.syscomments.id 
                                  LEFT OUTER JOIN sys.systypes ON ( sys.syscolumns.usertype = sys.systypes.usertype ) and  
                                                                  ( sys.syscolumns.xusertype = sys.systypes.xusertype ) 
                                  LEFT OUTER JOIN sys.extended_properties on sys.extended_properties.major_id = sys.syscolumns.id and 
                                                                             sys.extended_properties.minor_id = sys.syscolumns.colid 
                                  LEFT OUTER JOIN (  SELECT sys.index_columns.object_id, 
                                                            sys.index_columns.column_id 
                                                       FROM sys.index_columns
                                                            inner join sys.indexes on sys.indexes.index_id = sys.index_columns.index_id and 
                                                                                      sys.indexes.object_id = sys.index_columns.object_id 
                                                      WHERE sys.indexes.is_primary_key = 1 ) PKeyColumns ON PKeyColumns.object_id = sys.syscolumns.id and 
                                                                                                            PKeyColumns.column_id = sys.syscolumns.colid ) UserTableColumn
                        ON UserTableColumn.id = UserTable.id 
       UNION ALL 
         SELECT TOP 100 PERCENT '' AS 表名, 
             ISNULL( CAST( sys.extended_properties.value AS VARCHAR(256) ), '' ) AS 列名, 
             sys.sysobjects.name AS 列编码, 
             '' AS 列序, 
             '' AS 主键, 
             '' AS 数据类型, 
             '' AS 宽度, 
             '' AS 小数位, 
             '' AS 精度, 
             '' AS 默认值, 
             '' AS 可空, 
             '' AS 自增长, 
             sys.sysobjects.name, 
             1 AS ListOrder, 
             0 as colid
                  from sys.sysobjects 
                       left outer join sys.extended_properties on sys.extended_properties.major_id = sys.sysobjects.id and 
                                                                  sys.extended_properties.minor_id = 0 
                 WHERE sys.sysobjects.type = 'U' --and 
                       --convert( varchar(10), sys.sysobjects.crdate, 121 ) = convert( varchar(10), getdate(), 121 ) 
       union all 
         select TOP 100 PERCENT '表名' AS 表名, 
             '列名' AS 列名, 
             '列编码' AS 列编码, 
                '列序' AS 列序, 
               '主键' AS 主键, 
             '数据类型' AS 数据类型, 
             '宽度' AS 宽度, 
             '小数位' AS 小数位, 
             '精度' AS 精度, 
             '默认值' AS 默认值, 
             '可空' AS 可空, 
             '自增长' AS 自增长, 
             sys.sysobjects.name, 
             2 AS ListOrder, 
             0 as colid  
                  from sys.sysobjects 
                       left outer join sys.extended_properties on sys.extended_properties.major_id = sys.sysobjects.id and 
                                                                  sys.extended_properties.minor_id = 0 
                 WHERE sys.sysobjects.type = 'U' --and 
                       --convert( varchar(10), sys.sysobjects.crdate, 121 ) = convert( varchar(10), getdate(), 121 ) 
         UNION ALL 
         SELECT TOP 100 PERCENT '' AS 表名, 
             '' AS 列名, 
             '' AS 列编码, 
                '' AS 列序, 
    
               '' AS 主键, 
             '' AS 数据类型, 
             '' AS 宽度, 
             '' AS 小数位, 
             '' AS 精度, 
             '' AS 默认值, 
             '' AS 可空, 
             '' AS 自增长, 
             sys.sysobjects.name, 
             4 AS ListOrder, 
             0 as colid
        FROM sys.sysobjects 
             left outer join sys.extended_properties on sys.extended_properties.major_id = sys.sysobjects.id and 
                                                        sys.extended_properties.minor_id = 0 
       WHERE sys.sysobjects.type = 'U' /*and 
             convert( varchar(10), sys.sysobjects.crdate, 121 ) = convert( varchar(10), getdate(), 121 ) */) A 
       WHERE A.name LIKE '[A-Z][0-9]%'
    ORDER BY A.name ASC, 
             A.ListOrder ASC, 
             A.colid ASC 
    GO
    
  • 相关阅读:
    左旋一个字符串和手摇反转法
    LCS
    游戏服务器学习_1
    面试题_带答案_2
    面试题_带答案
    安卓_13
    安卓_12activity
    安卓_12
    多盟_1
    安卓没删干净导致报错
  • 原文地址:https://www.cnblogs.com/hijushen/p/10559161.html
Copyright © 2020-2023  润新知