一些有用西查询语句（关于系统表的使用）

zhangyh123

--查找系统中所有的表（含表的行数）
SELECT USER_NAME(uid),
       O.name,
       rowcnt(doampg),
       S.name,
       creation = O.crdate,
       case sysstat2 & 57344
         when 32768 then 'datarows'
         when 16384 then 'datapages'
         else 'allpages' end
   FROM sysobjects O, sysindexes I, syssegments S
   WHERE O.type = 'U' AND
         O.id=I.id AND
         I.indid IN (0,1) AND
         I.segment=S.segment AND
         O.type!='S'  
   ORDER BY 1,2
   
--查找系统中所有的主键
SELECT USER_NAME(O.uid), OBJECT_NAME(I.id),I.name,S.name
  FROM sysindexes I,sysobjects O,syssegments S
  WHERE I.id=O.id AND I.status2 & 2 = 2 AND
        I.status & 2048 = 2048 AND
        I.indid>;0 AND I.segment=S.segment  
  ORDER BY USER_NAME(O.uid),OBJECT_NAME(I.id),I.name
  
--查找系统中所有的索引  
SELECT USER_NAME(O.uid),O.name,I.name,
       CASE WHEN ((I.status&16)=16 OR (I.status2&512)=512) THEN 'Clustered'
            WHEN (I.indid=255) THEN 'Text/Image'
            ELSE 'Non-Clustered' END,
       CASE WHEN ((I.status&2)=2) THEN 'Unique'
            ELSE 'Non-Unique' END, S.name
  FROM sysindexes I,syssegments S,sysobjects O
  WHERE I.indid>;0 AND I.indid<255 AND I.status2 & 2!=2 AND
        I.segment=S.segment AND O.id=I.id AND
        O.type='U' AND O.type!='S'  ORDER BY 1,2,3  
               
--查找系统中所有表的外键               
SELECT USER_NAME(O.uid),OBJECT_NAME(C.tableid),OBJECT_NAME(C.constrid)
   FROM sysconstraints C,sysobjects O
   WHERE C.constrid=O.id AND C.status=64  
   ORDER BY USER_NAME(O.uid),OBJECT_NAME(C.tableid),OBJECT_NAME(C.constrid)

八戒

不错，不错

Blackrose

不错不错
我来补充一下，争取弄成精华吧！


-- 获取某个设备的数据库使用情况
create proc p_getdevinfo(@dev_name varchar(30))
as
begin
select a.dbid,a.name,  sum(b.size)/512 as size, (select l.name from master.dbo.syslogins l where l.suid=a.suid) as creator
from master.dbo.sysdatabases a, master.dbo.sysusages b, master.dbo.sysdevices c
where (a.dbid=b.dbid) and (b.vstart<=c.high) and (b.vstart>;=c.low) and (c.name=@dev_name)
group by a.name
order by a.dbid
end
----------------------------------------------------------------------------------------------------
-- 获取某个数据库上的数据库设备使用情况
create proc p_getdbinfo
(@db_name varchar(30))
as
begin
select d.name as 'device name',u.size,
case u.segmap when 3 then 'data only' when 4 then 'log only' when 7 then 'data and log' else 'mix' end as purpose,
u.lstart, curunreservedpgs(dbid, lstart, unreservedpgs) as freepg
from master..sysusages u, master..sysdevices d
where d.low <= u.size + vstart
and d.high >;= u.size + vstart -1
and d.status &2 = 2
and dbid = db_id(@db_name)
order by segmap
end
----------------------------------------------------------------------------------------------------
--获取数据库 在 各个设备上的 段信息

create proc sp_viewseg
(@dbname varchar(20))
as
begin
select distinct DV.name,S.name from master.dbo.sysusages U,
master.dbo.sysdevices DV,
test.dbo.syssegments S
where U.dbid= db_id(@dbname)
and U.vstart between DV.low and DV.high
and U.segmap & S.status = S.status
and ((U.segmap/((S.segment&1)+1))/power(2,(S.segment&30)))&1 = 1
order by DV.name
end
----------------------------------------------------------------------------------------------------

zhangyh123

---呵呵 谢谢 blackrose 支持
--再补充一点：
--查看每个对象在某段上所占用的硬盘空间，每天查看，
--可以生成各表、索引的增长速度图表，看看有无异常 或做其他诊断
--可以改造为系统过程
--本例为 default

SELECT USER_NAME(O.uid),
       O.name,
       I.name,
       I.indid,
       STR(ROUND((RESERVED_PGS(I.id,doampg)+RESERVED_PGS(I.id,ioampg))*(2048/1024576.0),2),9,2)as Reserved,
       STR(ROUND(CONVERT(numeric(20,9),DATA_PGS(I.id,doampg))*(2048/1024576.0),2),9,2)as Data,
       STR(ROUND(CONVERT(numeric(20,9),DATA_PGS(I.id,ioampg))*(2048/1024576.0),2),9,2) as Index_1 ,
       STR(ROUND(CONVERT(numeric(20,9),((RESERVED_PGS(I.id,doampg)+RESERVED_PGS(I.id,ioampg))-(DATA_PGS(I.id,doampg)+DATA_PGS(I.id,ioampg))))*(2048/1024576.0),2),9,2) as Unused ,
       I.status,I.status2
  FROM syssegments S,sysindexes I,sysobjects O
  WHERE S.name='default' AND I.id!=8 AND
        I.segment=S.segment AND
        I.id=O.id
  ORDER BY I.indid

常笑

不错！太不错了！
当年，我问这个问题，可惜没人回答我！
建议列成精华！

小无赖

精华!精华!

zhangyh123

--这个程序比较完全，用于数据库空间查看（ZT）
--大家好好研究一下

dump tran sybsystemprocs with truncate_only
go
use sybsystemprocs
go
if exists (select * from sysobjects where name = "sp_showfrag" and type = 'P')
   drop proc sp_showfrag
go

create procedure sp_showfrag
@objname varchar(92) = null             /* the object we want size on */
as
declare @type   smallint                /* the object type */
declare @msg    varchar(250)            /* message output */
declare @dbname varchar(30)             /* database name */
declare @tabname varchar(30)            /* table name */
declare @length int


if @@trancount = 0
begin
        set transaction isolation level 1
        set chained off
end

/*
**  Check to see that the objname is local.
*/
if @objname is not null
begin
        /*
        ** Get the dbname and ensure that the object is in the
        ** current database. Also get the table name - this is later
        ** needed to see if information is being requested for syslogs.
        */
        execute sp_namecrack @objname,
                             @db = @dbname output,
                             @object = @tabname output
        if @dbname is not NULL
        begin
                /*
                ** 17460, "Object must be in the current database."
                */
                if (@dbname != db_name())
                begin
                        exec sp_getmessage 17460, @msg output
                        print @msg
                        return (1)
                end
        end

        /*
        **  Does the object exist?
        */
        if not exists (select *
                        from sysobjects
                                where id = object_id(@objname))
        begin
                /*
                ** 17461, "Object does not exist in this database."
                */
                exec sp_getmessage 17461, @msg output
                print @msg
                return (1)
        end

        /* Get the object type */
        select @type = sysstat & 7
                from sysobjects
                        where id = object_id(@objname)
        /*
        **  See if it's a space object.
        **  types are:
        **      0 - trigger
        **      1 - system table
        **      2 - view
        **      3 - user table
        **      4 - sproc
        **      6 - default
        **      7 - rule
        */
        if not exists (select *
                        from sysindexes
                                where id = object_id(@objname)
                                        and indid < 2)
        begin
                if @type in (0, 4, 6, 7)
                begin
                        /*
                        ** 17830, "Object is stored in 'sysprocedures' and
                        **         has no space allocated directly."
                        */
                        exec sp_getmessage 17830, @msg output
                        print @msg
                        return (1)
                end

                if @type = 2
                begin
                        /*
                        ** 17831, "Views don't have space allocated."
                        */
                        exec sp_getmessage 17831, @msg output
                        print @msg
                        return (1)
                end
        end

end

/*
**  First we want summary data.
*/
set nocount on

declare @slog_res_pgs numeric(20,9),    /* number of reserved pgs. in syslogs */
        @slog_dpgs numeric(20,9),       /* number of data pages in syslogs */
        @slog_unused numeric(20,9)      /* number of unused pages in syslogs */

/* Show the database name and size */
        
select distinct database_name = db_name(), database_size =
        ltrim(str(sum(size) / (1048576 / d.low), 10 ,1)) + " MB"
        from master.dbo.sysusages, master.dbo.spt_values d
                where dbid = db_id()
                        and d.number = 1
                        and d.type = "E"
                having dbid = db_id()
                        and d.number = 1
                        and d.type = "E"

/*
** Obtain the page count for syslogs table.
*/
select @slog_res_pgs = convert(numeric(20,9),reserved_pgs(id, doampg)),
       @slog_dpgs = convert(numeric(20,9),data_pgs(id, doampg)),
       @slog_unused = convert(numeric(20,9),((reserved_pgs(id, doampg)+
                   reserved_pgs(id, ioampg)) -
                   (data_pgs(id, doampg) + data_pgs(id, ioampg))))
from sysindexes where id = 8

/*
** Obtain the page count for all the objects in the current
** database; except for 'syslogs' (id = 8). Store the results
** in a temp. table (#pgcounts).
*/
select distinct
        sysindexes.name,
        res_pgs = (reserved_pgs(id, doampg) + reserved_pgs(id,ioampg)),
        low = d.low,
        dpgs = convert(numeric(20,9),data_pgs(id, doampg)),
        ipgs = convert(numeric(20,9),data_pgs(id, ioampg)),
        unused = convert(numeric(20,9),((reserved_pgs(id, doampg) +
                   reserved_pgs(id, ioampg)) -
                   (data_pgs(id, doampg) + data_pgs(id, ioampg))))
into #pgcounts
from sysindexes, master.dbo.spt_values d
        where sysindexes.id != 8
                and d.number = 1
                and d.type = "E"
        having d.number = 1
                and d.type = "E"
        
/*
** Compute the summary results by adding page counts from
** individual data objects. Add to the count the count of
** pages for 'syslogs'.  Convert the total pages to space
** used in Kilo bytes.
*/
select distinct reserved = convert(char(15), convert(varchar(11),
        convert(numeric(11,0),((sum(res_pgs) + @slog_res_pgs) /
                1024) * low)) + " " + "KB"),
        data = convert(char(15), convert(varchar(11),
                convert(numeric(11,0),((sum(dpgs) + @slog_dpgs) /
                1024) * low)) + " " + "KB"),
        index_size = convert(char(15), convert(varchar(11),
                convert(numeric(11,0), (sum(ipgs) / 1024) * low))
                + " " + "KB"),
        unused = convert(char(15), convert(varchar(11),
                convert(numeric(11,0),((sum(unused) + @slog_unused) /
                1024) * low)) + " " + "KB")
from #pgcounts

/* collect the object information into a temp table */

select name, id, type
into #objlist
from sysobjects where type in ('S', 'U')

/*
**  Now we want detail on all objects
*/

        if (@tabname = "syslogs") /* syslogs */
        begin
                /*
                ** 17832, "Not avail."
                */
                exec sp_getmessage 17832, @msg output

                select @length = max(datalength(o.name))
                    from sysobjects o, sysindexes i, #objlist
                        where i.id = #objlist.id
                                and o.id = #objlist.id

                if (@length >; 20)
                    select name = o.name,
                        rowtotal = convert(char(11), @msg),
                        reserved = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0), convert(numeric(20,9),
                                (reserved_pgs(i.id, i.doampg) / 1024)) *
                                d.low)) + " " + "KB"),
                        data = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0),convert(numeric(20,9),
                                data_pgs(i.id, i.doampg) / 1024) * d.low)) +
                                " " + "KB"),
                        index_size = convert(char(10), convert(varchar(11), 0)
                                + " " + "KB"),
                        unused = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0), convert(numeric(20,9),
                                ((reserved_pgs(i.id, i.doampg) +
                                reserved_pgs(i.id, i.ioampg)) -
                                (data_pgs(i.id, i.doampg) + data_pgs(i.id,
                                i.ioampg)))) / 1024 * d.low)) + " " + "KB")
                        from sysobjects o, sysindexes i,
                             master.dbo.spt_values d, #objlist
                                where i.id = #objlist.id
                                        and o.id = #objlist.id
                                        and d.number = 1
                                        and d.type = "E"
                else
                    select name = convert(char(20), o.name),
                        rowtotal = convert(char(11), @msg),
                        reserved = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0), convert(numeric(20,9),
                                (reserved_pgs(i.id, i.doampg) / 1024)) *
                                d.low)) + " " + "KB"),
                        data = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0),convert(numeric(20,9),
                                data_pgs(i.id, i.doampg) / 1024) * d.low)) +
                                " " + "KB"),
                        index_size = convert(char(10), convert(varchar(11), 0)
                                + " " + "KB"),
                        unused = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0), convert(numeric(20,9),
                                ((reserved_pgs(i.id, i.doampg) +
                                reserved_pgs(i.id, i.ioampg)) -
                                (data_pgs(i.id, i.doampg) + data_pgs(i.id,
                                i.ioampg)))) / 1024 * d.low)) + " " + "KB")
                        from sysobjects o, sysindexes i,
                             master.dbo.spt_values d, #objlist
                                where i.id = #objlist.id
                                        and o.id = #objlist.id
                                        and d.number = 1
                                        and d.type = "E"
        end
        else
        begin

                select name = o.name,
                        low = d.low,
                        rowtotal = rowcnt(i.doampg),
                        reserved = convert(numeric(20,9),
                                (reserved_pgs(i.id, i.doampg) +
                                reserved_pgs(i.id, i.ioampg))),
                        data = convert(numeric(20,9),data_pgs(i.id, i.doampg)),
                        index_size =  convert(numeric(20,9),
                                data_pgs(i.id, i.ioampg)),
                        unused = convert(numeric(20,9),
                                ((reserved_pgs(i.id, i.doampg) +
                                reserved_pgs(i.id, i.ioampg)) -
                                (data_pgs(i.id, i.doampg) +
                                data_pgs(i.id, i.ioampg))))
                into #pagecounts
                from sysobjects o, sysindexes i, master.dbo.spt_values d,
                        #objlist
                                where i.id = #objlist.id
                                        and o.id = #objlist.id
                                        and d.number = 1
                                        and d.type = "E"

            select @length = max(datalength(name))
                from #pagecounts

            if (@length >; 20)
                select distinct name,
                    rowtotal = convert(char(11), sum(rowtotal)),
                    reserved = convert(char(10), convert(varchar(11),
                               convert(numeric(11,0),sum(reserved) / 1024 *
                               low)) + " " + "KB"),
                    data = convert(char(10), convert(varchar(11),
                               convert(numeric(11,0),sum(data) / 1024 * low))
                               + " " + "KB"),
                    index_size = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0),sum(index_size) / 1024 *
                                low)) + " " + "KB"),
                    unused = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0), sum(unused) / 1024 *
                                low)) + " " + "KB")
                from #pagecounts
                group by name
            else
                select distinct name = convert(char(20), name),
                    rowtotal = convert(char(11), sum(rowtotal)),
                    reserved = convert(char(10), convert(varchar(11),
                               convert(numeric(11,0),sum(reserved) / 1024 *
                               low)) + " " + "KB"),
                    data = convert(char(10), convert(varchar(11),
                               convert(numeric(11,0),sum(data) / 1024 * low))
                               + " " + "KB"),
                    index_size = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0),sum(index_size) / 1024 *
                                low)) + " " + "KB"),
                    unused = convert(char(10), convert(varchar(11),
                                convert(numeric(11,0), sum(unused) / 1024 *
                                low)) + " " + "KB")
                from #pagecounts
                group by name
        end

return (0)
go

if object_id('sp_showfrag') is not null
begin
    print '<<< Created procedure dbo.sp_showfrag >;>;>;'
    grant execute on dbo.sp_showfrag to public
end
else
begin
    print '<<< Failed creating proc dbo.sp_showfrag >;>;>;'
end
go

zhangyh123

-- 获取系统中正在执行transaction 的进程(ZT)
dump tran sybsystemprocs with truncate_only
go

use sybsystemprocs
go

if exists (select * from sysobjects where name = "sp_opentran" and type = 'P')
   drop proc sp_opentran
go

create procedure sp_opentran
as
select spid, username=convert(varchar(12),suser_name(suid)),
tran_name=convert(varchar(25),tran_name),
dbname=convert(varchar(12),db_name(dbid))
from master..sysprocesses where tran_name >; ' '
go

if object_id('sp_opentran') is not null
begin
    print '<<< Created procedure dbo.sp_opentran >;>;>;'
    grant execute on dbo.sp_opentran to public
end
else
begin
    print '<<< Failed creating proc dbo.sp_opentran >;>;>;'
end
go

Blackrose

弓虽啊！

不过大家在生产系统使用的化 最好先坐坐充分的测试

存储过程也就是一种程序，难免有些bug哦

zhangyh123

这个帖子沉下去了，可惜