存储过程查看表结构信息

create procedure help
@tablename varchar(50)
as

select  表名=(case when b.colorder=1 then a.name else '' end),

表说明=( case when b.colorder=1 then c.value else '' end),b.colid 序号,b.name 字段名,

标识=case when COLUMNPROPERTY( b.id,b.name,'IsIdentity')=1 then '√'else '' end,

主键=case when exists(select 1 from sys.sysobjects where xtype='PK' and parent_obj=b.id and name in(

select name from sys.sysindexes where  indid in(select indid from sys.sysindexkeys where id=b.id and colid=b.colid))) then '√' else '' end,

类型=d.name,

b.length 占用字节,

长度=COLUMNPROPERTY(b.id,b.name,'Precision'),

允许空=case when  COLUMNPROPERTY(b.id,b.name,'AllowsNull')=1 then '√' else '' end,

小数位数=isnull(COLUMNPROPERTY(b.id,b.name,'Scale'),0),

默认值=REPLACE(REPLACE(e.text,'((',''),'))',''),

字段说明=isnull(f.value,'')

 from sys.sysobjects a inner  join sys.syscolumns b on a.id=b.id and a.xtype='U' and    a.name<>'dtproperties'

left join sys.extended_properties c on a.id=c.major_id and c.minor_id=0

left join sys.systypes d on b.xusertype=d.xusertype

left join sys.syscomments e on e.id=b.cdefault

left join sys.extended_properties f on  b.id=f.major_id and b.colid=f.minor_id 

where a.name=@tablename  order by b.colid --a.name='DEPT'表示查看dept表的结构信息

 

create procedure sp_column
    @tableName sysname
as
declare
    @objID int,
    @columns varchar(8000),
    @query nvarchar(4000)
begin
    set @objID = object_id(@tableName)
    set @columns = ''
    set @query = 
'
    select @columns = @columns + Name + '', ''
    from sys.syscolumns where id = @objID order by colid
'
    exec sp_executesql @query, N'@objID int, @columns varchar(8000) output', @objID, @columns output

    print substring(@columns, 1, len(@columns)-1)
End

 

create procedure sp_TableTopN
    @Objname sysname,
    @TopN int = 10
As
declare @cmd varchar(8000)
begin
    set @cmd = 'Select top ' + convert(varchar(10), @TopN) + ' * From ' + @Objname + ' With (Nolock)'
    exec(@cmd)
    
--    set @cmd = 'Select convert(varchar(20), count_big(*)) + '' rows in this table '' from ' + @Objname + ' With (Nolock)'
--    exec(@cmd) 

    exec sp_executesql N'SELECT convert(varchar(20), sum(ddps.row_count)) + '' rows in this table''
    FROM sys.indexes AS i 
    INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
    INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.object_id AND i.index_id = ddps.index_id 
    WHERE o.name = @Objname and i.index_id < 2 AND o.is_ms_shipped = 0',
    N'@Objname sysname', @Objname
end

 

create procedure sp_find
 @body Varchar(2000)    
as    
Set @body = 'Select ''''?'''',b.name from ?.sys.sql_modules a with (Nolock),?.sys.objects b  with (Nolock) 
where db_name() not in (''''master'''',''''msdb'''') and a.object_id = b.object_id and definition like ''''%' + @body + '%'''''''
exec ( 'sp_msforeachdb ''' + @body )

 

create procedure sys.sp_helptext

@objname nvarchar(776)

,@columnname sysname = NULL

as



set nocount on



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 @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

sp_helptext

 

转载于:https://www.cnblogs.com/idoudou/archive/2012/11/01/4153148.html