Chinaunix首页 | 论坛 | 博客
  • 博客访问: 692201
  • 博文数量: 219
  • 博客积分: 4813
  • 博客等级: 上校
  • 技术积分: 2359
  • 用 户 组: 普通用户
  • 注册时间: 2005-10-31 21:54
个人简介

All right, waiting for you to die, I'll laugh ...

文章分类

全部博文(219)

文章存档

2016年(4)

2014年(4)

2013年(4)

2012年(4)

2011年(49)

2010年(126)

2009年(6)

2008年(16)

2007年(4)

2006年(1)

2005年(1)

分类: Sybase

2008-11-20 16:44:18

--
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 '+@dbname1+'..sysobjects where xtype=''PK'' and name in (
  SELECT name FROM '+@dbname1+'..sysindexes WHERE indid in(
   SELECT indid FROM '+@dbname1+'..sysindexkeys 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 '+@dbname1+'..syscolumns a
 left join '+@dbname1+'..systypes b on a.xtype=b.xusertype
 inner join '+@dbname1+'..sysobjects d on a.id=d.id  and d.xtype=''U'' and  d.name<>''dtproperties''
 left join '+@dbname1+'..syscomments e on a.cdefault=e.id
 left join '+@dbname1+'..sysproperties 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 '+@dbname2+'..sysobjects where xtype=''PK'' and name in (
  SELECT name FROM '+@dbname2+'..sysindexes WHERE indid in(
   SELECT indid FROM '+@dbname2+'..sysindexkeys 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 '+@dbname2+'..syscolumns a
 left join '+@dbname2+'..systypes b on a.xtype=b.xusertype
 inner join '+@dbname2+'..sysobjects d on a.id=d.id  and d.xtype=''U'' and  d.name<>''dtproperties''
 left join '+@dbname2+'..syscomments e on a.cdefault=e.id
 left join '+@dbname2+'..sysproperties 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)
阅读(2077) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~