--查找系统中所有的表（含表的行数）
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)
返回页首 1楼
-- 获取某个设备的数据库使用情况
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
----------------------------------------------------------------------------------------------------
--再补充一点：
--查看每个对象在某段上所占用的硬盘空间，每天查看，
--可以生成各表、索引的增长速度图表，看看有无异常 或做其他诊断
--可以改造为系统过程
--本例为 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-6-18 16:57 [引用] [投诉] [快速回复]
--这个程序比较完全，用于数据库空间查看（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
-- 获取系统中正在执行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
阅读(958) | 评论(0) | 转发(0) |
