create procedure help@tablename varchar(50)asselect  表名=(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=0left join sys.systypes d on b.xusertype=d.xusertypeleft join sys.syscomments e on e.id=b.cdefaultleft 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 sysnameasdeclare    @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 = 10Asdeclare @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', @Objnameend

 

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 = NULLasset nocount ondeclare @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 = 255select @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    endelse 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    endelse    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 = 2select @LineId = 1OPEN ms_crs_syscomFETCH NEXT from ms_crs_syscom into @SyscomTextWHILE @@fetch_status >= 0begin    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 @SyscomTextendIF @Line is NOT NULL    INSERT #CommentText VALUES( @LineId, @Line )select Text from #CommentText order by LineIdCLOSE  ms_crs_syscomDEALLOCATE     ms_crs_syscomDROP TABLE     #CommentTextreturn (0) -- sp_helptext