--查找系统中所有的表（含表的行数）
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)
八戒 回复于：2003-06-17 14:51:06
不错，不错
Blackrose 回复于：2003-06-17 15:05:18
不错不错
我来补充一下，争取弄成精华吧！
:)
-- 获取某个设备的数据库使用情况
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 回复于：2003-06-17 18:05:13
---呵呵 谢谢 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
常笑 回复于：2003-06-17 19:40:58
不错！太不错了！
当年，我问这个问题，可惜没人回答我！
建议列成精华！
小无赖 回复于：2003-06-18 10:17:02
精华!精华!
zhangyh123 回复于：2003-06-18 16:57:05
--这个程序比较完全，用于数据库空间查看（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&nbs[1] [2] [3] [4] [5] [6] 下一页 |
