--
CREATE PROCEDURES
create proc p_comparestructure
@dbname1 varchar(250), --要比较的数据库名1
@dbname2 varchar(250) --要比较的数据库名2
as
create table #tb1(表名1 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250),
占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 varchar(500),字段说明 varchar(500))
create table #tb2(表名2 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250),
占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 varchar(500),字段说明 varchar(500))
--得到数据库1的结构
exec('insert into #tb1 SELECT
表名=d.name,字段名=a.name,序号=a.colid,
标识=case when a.status=0x80 then 1 else 0 end,
主键=case when exists(SELECT 1 FROM where xtype=''PK'' and name in (
SELECT name FROM WHERE indid in(
SELECT indid FROM WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,
默认值=isnull(e.text,''''''),字段说明=isnull(g.[value],'''''')
FROM a
left join b on a.xtype=b.xusertype
inner join d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties''
left join e on a.cdefault=e.id
left join g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')
--得到数据库2的结构
exec('insert into #tb2 SELECT
表名=d.name,字段名=a.name,序号=a.colid,
标识=case when a.status=0x80 then 1 else 0 end,
主键=case when exists(SELECT 1 FROM where xtype=''PK'' and name in (
SELECT name FROM WHERE indid in(
SELECT indid FROM WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,
默认值=isnull(e.text,''''''),字段说明=isnull(g.[value],'''''')
FROM a
left join b on a.xtype=b.xusertype
inner join d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties''
left join e on a.cdefault=e.id
left join g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')
--and not exists(select 1 from #tb2 where 表名2=a.表名1)
select 比较结果=case when a.表名1 is null and b.序号=1 then '库1缺少表：'+b.表名2
when b.表名2 is null and a.序号=1 then '库2缺少表:'+a.表名1
when a.字段名 is null and exists(select 1 from #tb1 where 表名1=b.表名2) then '库1 ['+b.表名2+'] 缺少字段：'+b.字段名
when b.字段名 is null and exists(select 1 from #tb2 where 表名2=a.表名1) then '库2 ['+a.表名1+'] 缺少字段：'+a.字段名
when a.标识<>b.标识 then '标识不同'
when a.主键<>b.主键 then '主键设置不同'
when a.类型<>b.类型 then '字段类型不同'
when a.占用字节数<>b.占用字节数 then '占用字节数'
when a.长度<>b.长度 then '长度不同'
when a.小数位数<>b.小数位数 then '小数位数不同'
when a.允许空<>b.允许空 then '是否允许空不同'
when a.默认值<>b.默认值 then '默认值不同'
when a.字段说明<>b.字段说明 then '字段说明不同'
else '' end,
a.表名1, a.字段名, a.序号, a.标识, a.主键, a.类型, a.占用字节数, a.长度, a.小数位数, a.允许空, a.默认值, a.字段说明, b.表名2, b.字段名, b.序号, b.标识, b.主键, b.类型, b.占用字节数, b.长度, b.小数位数, b.允许空, b.默认值, b.字段说明
from #tb1 a
join #tb2 b on a.表名1=b.表名2 and a.字段名=b.字段名
where a.表名1 is null or a.字段名 is null or b.表名2 is null or b.字段名 is null
or a.标识<>b.标识 or a.主键<>b.主键 or a.类型<>b.类型
or a.占用字节数<>b.占用字节数 or a.长度<>b.长度 or a.小数位数<>b.小数位数
or a.允许空<>b.允许空 or a.默认值<>b.默认值 or a.字段说明<>b.字段说明
order by isnull(a.表名1,b.表名2),isnull(a.序号,b.序号)
--isnull(a.字段名,b.字段名)
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
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
用于数据库空间查看
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
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
--查看每个对象在某段上所占用的硬盘空间，每天查看，
--可以生成各表、索引的增长速度图表，看看有无异常 或做其他诊断
--可以改造为系统过程
--本例为 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
-- 获取某个设备的数据库使用情况
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
----------------------------------------------------------------------------------------------------
--查找系统中所有的表（含表的行数）
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)
