lllenxue
副版主
帖子
701
精华
3
无忧币 2820
积分 1089
阅读权限 140
|
发表于:2008-5-5 13:43
在sybase central 中的sybsystemprocs 数据库中，存储过程里可以看到所有的系统存储过程，选中要查看的存储过程右键‘生成DDL’，即可查看存储过程的源代码
print 'sp_helpdb'
SETUSER 'dbo'
go
/* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
/* 4.8 1.1 06/14/90 sproc/src/help */
/*
** Messages for "sp_helpdb" 17590
**
** 17111, "log only"
** 17590, "The specified database does not exist."
** 17591, "no options set"
** 17592, " -- unused by any segments --"
** 17714, "not applicable"
*/
create procedure sp_helpdb
@dbname varchar(30) = NULL /* database name to change */
as
declare @showdev int
declare @allopts int
declare @all2opts int
declare @all3opts int
declare @all4opts int
declare @thisopt int
declare @optmask int
declare @pagekb int
declare @msg varchar(1024)
declare @sptlang int
declare @na_phrase varchar(30) /* length of German */
declare @len1 int, @len2 int, @len3 int
if @@trancount = 0
begin
set chained off
end
set transaction isolation level 1
select @sptlang = @@langid
if @@langid != 0
begin
if not exists (
select * from master.dbo.sysmessages where error
between 17050 and 17069
and langid = @@langid)
select @sptlang = 0
else
if not exists (
select * from master.dbo.sysmessages where error
between 17110 and 17119
and langid = @@langid)
select @sptlang = 0
end
set nocount on
/*
** If no database name given, get 'em all. Otherwise, count how many
** databases match the specified name.
*/
if @dbname is null
select @dbname = "%",
@showdev = count(*) from master.dbo.sysdatabases
else
select @showdev = count(*)
from master.dbo.sysdatabases
where name like @dbname
/*
** Sure the database exists
*/
if @showdev = 0
begin
/* 17590, "The specified database does not exist." */
raiserror 17590
return (1)
end
/*
** Set allopts to be the sum of all possible user-settable database status
** bits. (Note that there are 2 groups of such bits.) If we can't get
** the option mask from spt_values, guess at the correct value.
*/
select @allopts = number
from master.dbo.spt_values
where type = "D"
and name = "ALL SETTABLE OPTIONS"
if (@allopts is NULL)
select @allopts = 4 | 8 | 16 | 512 | 1024 | 2048 | 4096 | 8192
select @all2opts = number
from master.dbo.spt_values
where type = "D2"
and name = "ALL SETTABLE OPTIONS"
if (@all2opts is NULL)
select @all2opts = 1 | 2 | 4 | 8 | 64
select @all3opts = number
from master.dbo.spt_values
where type = "D3"
and name = "ALL SETTABLE OPTIONS"
if (@all3opts is NULL)
select @all3opts = 0
select @all4opts = number
from master.dbo.spt_values
where type = "D4"
and name = "ALL SETTABLE OPTIONS"
if (@all4opts is NULL)
select @all4opts = 0
/*
** @allopts (sysdatabases.status options) should also contain some
** NON-settable options that we want to check for:
** 32 = "don't recover"
** 256 = "not recovered"
*/
select @allopts = @allopts | 32 | 256
/*
** @all2opts (sysdatabases.status2 options) should also contain a
** NON-settable option that we want to check for:
** 16 = "offline"
** 128 = "has suspect objects"
** 1024 = "online for standby access"
*/
select @all2opts = @all2opts | 16 | 128 | 1024
/*
** @all3opts (sysdatabases.statu3 options) should also contain
** NON-settable options that we want to check for:
**
** 0128 = "quiesce database"
*/
select @all3opts = @all3opts | 128
/*
** Since we examine the status bits in sysdatabase and turn them
** into english, we need a temporary table to build the descriptions.
*/
create table #spdbdesc
(
dbid smallint null,
dbdesc varchar(102) null
)
/*
** Initialize #spdbdesc from sysdatabases
*/
insert into #spdbdesc (dbid)
select dbid
from master.dbo.sysdatabases
where name like @dbname
/*
** Now for each dbid in #spdbdesc, build the database status
** description.
*/
declare @curdbid smallint /* the one we're currently working on */
declare @dbdesc varchar(102) /* the total description for the db */
declare @bitdesc varchar(30) /* the bit description for the db */
/*
** Set @curdbid to the first dbid.
*/
select @curdbid = min(dbid)
from #spdbdesc
while @curdbid is not NULL
begin
/*
** Initialize @dbdesc.
*/
select @dbdesc = ""
/*
** Check status options (spt_values.type = "D")
*/
select @thisopt = 1
select @optmask = @allopts /* status options */
while (@optmask != 0) /* until all set options noted ... */
begin
/*
** If this option is user-settable, check for it
*/
if (@optmask & @thisopt = @thisopt)
begin
select @bitdesc = null
select @bitdesc = m.description
from master.dbo.spt_values v,
master.dbo.sysdatabases d,
master.dbo.sysmessages m
where d.dbid = @curdbid
and v.type = "D"
and d.status & v.number = @thisopt
and v.number = @thisopt
and v.msgnum = m.error
and isnull(m.langid, 0) = @sptlang
if @bitdesc is not null
begin
if @dbdesc != ""
select @dbdesc = @dbdesc + ", " + @bitdesc
else select @dbdesc = @bitdesc
end
/* Turn off this status bit in the options mask */
select @optmask = @optmask & ~(@thisopt)
end
/*
** Get the next option bit. Check for integer overflow for
** bit 31 (0x80000000).
*/
if (@thisopt < 1073741824)
select @thisopt = @thisopt * 2
else
select @thisopt = -2147483648
end
/*
** Check status2 options (spt_values.type = "D2")
*/
select @thisopt = 1
select @optmask = @all2opts /* status2 options */
while (@optmask != 0) /* until all set options noted ... */
begin
/*
** If this option is user-settable, check for it
*/
if (@optmask & @thisopt = @thisopt)
begin
select @bitdesc = null
select @bitdesc = m.description
from master.dbo.spt_values v,
master.dbo.sysdatabases d,
master.dbo.sysmessages m
where d.dbid = @curdbid
and v.type = "D2"
and d.status2 & v.number = @thisopt
and v.number = @thisopt
and v.msgnum = m.error
and isnull(m.langid, 0) = @sptlang
if @bitdesc is not null
begin
if @dbdesc != ""
select @dbdesc = @dbdesc + ", " + @bitdesc
else select @dbdesc = @bitdesc
end
/* Turn off this status bit in the options mask */
select @optmask = @optmask & ~(@thisopt)
end
/*
** Get the next option bit. Check for integer overflow for
** bit 31 (0x80000000).
*/
if (@thisopt < 1073741824)
select @thisopt = @thisopt * 2
else
select @thisopt = -2147483648
end
/*
** Check status3 options (spt_values.type = "D3")
*/
select @thisopt = 1
select @optmask = @all3opts /* status3 options */
while (@optmask != 0) /* until all set options noted ... */
begin
/*
** If this option is user-settable, check for it
*/
if (@optmask & @thisopt = @thisopt)
begin
select @bitdesc = null
select @bitdesc = m.description
from master.dbo.spt_values v,
master.dbo.sysdatabases d,
master.dbo.sysmessages m
where d.dbid = @curdbid
and v.type = "D3"
and d.status3 & v.number = @thisopt
and v.number = @thisopt
and v.msgnum = m.error
and isnull(m.langid, 0) = @sptlang
if @bitdesc is not null
begin
if @dbdesc != ""
select @dbdesc = @dbdesc + ", " + @bitdesc
else select @dbdesc = @bitdesc
end
/* Turn off this status bit in the options mask */
select @optmask = @optmask & ~(@thisopt)
end
/*
** Get the next option bit. Check for integer overflow for
** bit 31 (0x80000000).
*/
if (@thisopt < 1073741824)
select @thisopt = @thisopt * 2
else
select @thisopt = -2147483648
end
/*
** Check status4 options (spt_values.type = "D4")
*/
select @thisopt = 1
select @optmask = @all4opts /* status4 options */
while (@optmask != 0) /* until all set options noted ... */
begin
/*
** If this option is user-settable, check for it
*/
if (@optmask & @thisopt = @thisopt)
begin
select @bitdesc = null
select @bitdesc = m.description
from master.dbo.spt_values v,
master.dbo.sysdatabases d,
master.dbo.sysmessages m
where d.dbid = @curdbid
and v.type = "D4"
and d.status4 & v.number = @thisopt
and v.number = @thisopt
and v.msgnum = m.error
and isnull(m.langid, 0) = @sptlang
if @bitdesc is not null
begin
if @dbdesc != ""
select @dbdesc = @dbdesc + ", " + @bitdesc
else select @dbdesc = @bitdesc
end
/* Turn off this status bit in the options mask */
select @optmask = @optmask & ~(@thisopt)
end
/*
** Get the next option bit. Check for integer overflow for
** bit 31 (0x80000000).
*/
if (@thisopt < 1073741824)
select @thisopt = @thisopt * 2
else
select @thisopt = -2147483648
end
/*
** If no flags are set, say so.
*/
if @dbdesc = ""
begin
/* 17591, "no options set" */
exec sp_getmessage 17591, @dbdesc out
end
/*
** Save the description.
*/
update #spdbdesc
set dbdesc = @dbdesc
from #spdbdesc
where dbid = @curdbid
/*
** Now get the next, if any dbid.
*/
select @curdbid = min(dbid)
from #spdbdesc
where dbid > @curdbid
end
/*
** Get the rows of interest from sysusages into a temp table. This is to
** avoid deadlocking with create table, which could happen if we directly
** join sysdatabases and sysusages.
*/
select u.dbid, u.segmap, u.lstart, u.size, u.vstart, u.unreservedpgs, u.crdate
into #spdbusages
from #spdbdesc, master.dbo.sysusages u
where #spdbdesc.dbid = u.dbid
/*
** Compute number of Pages in a Megabyte.
*/
declare @numpgsmb float /* Number of Pages per Megabyte */
select @numpgsmb = (1048576. / v.low)
from master.dbo.spt_values v
where v.number = 1
and v.type = "E"
/*
** Now #spdbdesc is complete so we can print out the db info
*/
select distinct @len1 = max(datalength(d.name)),
@len2 = max(datalength(l.name))
from master.dbo.sysdatabases d, master.dbo.syslogins l,
#spdbusages u, #spdbdesc
where d.dbid = #spdbdesc.dbid
and d.suid = l.suid
and #spdbdesc.dbid = u.dbid
if (@len1 > 24 or @len2 > 24)
select distinct name = d.name,
db_size = str(sum(u.size) / @numpgsmb, 10, 1)
+ " MB",
owner = l.name,
dbid = d.dbid,
created = convert(char(14), d.crdate, 107),
status = #spdbdesc.dbdesc
from master.dbo.sysdatabases d, master.dbo.syslogins l,
#spdbusages u, #spdbdesc
where d.dbid = #spdbdesc.dbid
and d.suid = l.suid
and #spdbdesc.dbid = u.dbid
group by #spdbdesc.dbid
having d.dbid = #spdbdesc.dbid
and d.suid = l.suid
and #spdbdesc.dbid = u.dbid
order by d.name
else
select distinct name = convert(char(24), d.name),
db_size = str(sum(u.size) / @numpgsmb, 10, 1)
+ " MB",
owner = convert(char(24), l.name),
dbid = d.dbid,
created = convert(char(14), d.crdate, 107),
status = #spdbdesc.dbdesc
from master.dbo.sysdatabases d, master.dbo.syslogins l,
#spdbusages u, #spdbdesc
where d.dbid = #spdbdesc.dbid
and d.suid = l.suid
and #spdbdesc.dbid = u.dbid
group by #spdbdesc.dbid
having d.dbid = #spdbdesc.dbid
and d.suid = l.suid
and #spdbdesc.dbid = u.dbid
order by d.name
/*
** Print sysattributes data if there is any. The join with multiple
** instances of sysattributes is to get the string descriptions for
** the class (master..sysattributes cn) and the attribute
** (master..sysattributes an). These should never be longer than
** 30 characters, so it's okay to truncate them.
*/
select name = db.name, attribute_class = convert(char(30),cn.char_value),
attribute = convert(char(30),an.char_value), a.int_value,
a.char_value, a.comments
into #spdbattr
from master.dbo.sysdatabases db, #spdbdesc d,
master.dbo.sysattributes a, master.dbo.sysattributes an,
master.dbo.sysattributes cn
where db.dbid = d.dbid
and a.class = cn.object
and a.attribute = an.object_info1
and a.class = an.object
and a.object_type = "D"
and a.object = d.dbid
and cn.class = 0
and cn.attribute = 0
and an.class = 0
and an.attribute = 1
and a.object = db.dbid
if exists (select * from #spdbattr)
begin
select name, attribute_class, attribute, int_value, char_value,
comments
from #spdbattr
end
/*
** If we are looking at one database, show its device allocation.
*/
if @showdev = 1
begin
select @curdbid = dbid /* database ID */
from master.dbo.sysdatabases
where name like @dbname
select @pagekb = (low / 1024) /* kbytes per page */
from master.dbo.spt_values
where number = 1
and type = 'E'
/* 17714, "not applicable" */
select @na_phrase = description
from master.dbo.sysmessages
where error = 17714
and isnull(langid, 0) = @sptlang
/* Check the length of the usage column */
select distinct @len3 = max(datalength(m.description))
from master.dbo.sysdatabases d, #spdbusages u, master.dbo.sysdevices v, master.dbo.spt_values a,
master.dbo.spt_values b, master.dbo.sysmessages m
where d.dbid = u.dbid
and v.low <= u.size + vstart
and v.high >= u.size + vstart - 1
and v.status & 2 = 2
and d.name = @dbname
and a.type = "E"
and a.number = 1
and b.type = "S"
and u.segmap & 7 = b.number
and b.msgnum = m.error
and isnull(m.langid, 0) = @sptlang
if (@len3 > 20)
select device_fragments = v.name, size =
str(size / @numpgsmb, 10, 1) + " MB",
usage = m.description,
created = convert(char(19), u.crdate, 100),
case
when u.segmap = 4 then @na_phrase
else
str((curunreservedpgs(@curdbid, u.lstart,
u.unreservedpgs) * @pagekb), 16)
end "free kbytes"
from master.dbo.sysdatabases d,
#spdbusages u,
master.dbo.sysdevices v,
master.dbo.spt_values a,
master.dbo.spt_values b,
master.dbo.sysmessages m
where d.dbid = u.dbid
and v.low <= u.size + vstart
and v.high >= u.size + vstart - 1
and v.status & 2 = 2
and d.name = @dbname
and a.type = "E"
and a.number = 1
and b.type = "S"
and u.segmap & 7 = b.number
and b.msgnum = m.error
and isnull(m.langid, 0) = @sptlang
order by 1
else
select device_fragments = v.name, size =
convert(varchar(10),
round(
(a.low * convert(float, u.size))
/ 1048576, 1)) + " " + "MB",
usage = convert(char(20), m.description),
created = convert(char(19), u.crdate, 100),
case
when u.segmap = 4 then @na_phrase
else
str((curunreservedpgs(@curdbid, u.lstart,
u.unreservedpgs) * @pagekb), 16)
end "free kbytes"
from master.dbo.sysdatabases d,
#spdbusages u,
master.dbo.sysdevices v,
master.dbo.spt_values a,
master.dbo.spt_values b,
master.dbo.sysmessages m
where d.dbid = u.dbid
and v.low <= u.size + vstart
and v.high >= u.size + vstart - 1
and v.status & 2 = 2
and d.name = @dbname
and a.type = "E"
and a.number = 1
and b.type = "S"
and u.segmap & 7 = b.number
and b.msgnum = m.error
and isnull(m.langid, 0) = @sptlang
order by 1
/* If log segment free space wasn't selected above, select it now. */
if exists (select *
from master.dbo.sysdatabases d, master.dbo.sysusages u
where d.name = @dbname
and d.dbid = u.dbid
and u.segmap = 4)
begin
/* 17111, "log only". Length 17 is for French, the longest */
select substring((select description
from master.dbo.sysmessages
where error = 17111
and isnull(langid, 0) = @sptlang), 1, 17)
+ " " + "free kbytes" + " = "
+ convert (char, lct_admin("logsegment_freepages", @curdbid) * @pagekb)
end
/*
** If there is only one database and we are in it, show the
** segments.
*/
if exists (select *
from #spdbdesc
where db_id() = dbid)
begin
declare @curdevice varchar(30),
@curseg smallint,
@segbit int
delete #spdbdesc
select @curdevice = min(d.name)
from #spdbusages u, master.dbo.sysdevices d
where u.dbid = db_id()
and d.low <= size + vstart
and d.high >= size + vstart - 1
and d.status & 2 = 2
while (@curdevice is not null)
begin
/*
** We need an inner loop here to go through
** all the possible segment.
*/
select @curseg = min(segment)
from syssegments
while (@curseg is not null)
begin
if (@curseg < 31)
select @segbit = power(2, @curseg)
else select @segbit = low
from master.dbo.spt_values
where type = "E"
and number = 2
insert into #spdbdesc
select @curseg, @curdevice
from #spdbusages u,
master.dbo.sysdevices d,
master.dbo.spt_values v
where u.segmap & @segbit = @segbit
and d.low <= u.size + u.vstart
and d.high >= u.size + u.vstart - 1
and u.dbid = db_id()
and d.status & 2 = 2
and v.number = 1
and v.type = "E"
and d.name = @curdevice
select @curseg = min(segment)
from syssegments
where segment > @curseg
end
select @curdevice = min(d.name)
from #spdbusages u,
master.dbo.sysdevices d
where u.dbid = db_id()
and d.low <= size + vstart
and d.high >= size + vstart - 1
and d.status & 2 = 2
and d.name > @curdevice
end
/*
** One last check for any devices that have no segments.
*/
insert into #spdbdesc
select null, d.name
from #spdbusages u,
master.dbo.sysdevices d
where u.segmap = 0
and d.low <= u.size + u.vstart
and d.high >= u.size + u.vstart - 1
and u.dbid = db_id()
and d.status & 2 = 2
/* 17592, " -- unused by any segments --" */
exec sp_getmessage 17592, @msg out
select distinct @len1 = max(datalength(dbdesc))
from #spdbdesc, syssegments
where dbid *= segment
if (@len1 > 30)
select distinct device = dbdesc,
segment = isnull(name, @msg)
from #spdbdesc, syssegments
where dbid *= segment
order by 1, 2
else
select distinct device = convert(char(30), dbdesc),
segment = isnull(name, @msg)
from #spdbdesc, syssegments
where dbid *= segment
order by 1, 2
end
/*
** OMNI: Display the default location for remote tables
** if one exists.
*/
if exists (select *
from master.dbo.sysdatabases
where name like @dbname
and def_remote_loc is not null)
begin
select "remote location" = substring(def_remote_loc, 1, 77)
from master.dbo.sysdatabases
where name like @dbname
end
end
drop table #spdbdesc
drop table #spdbattr
return (0)
go
SETUSER
go
grant Execute on sp_helpdb to public
go
|
千里之外，传递你对震灾人民的关怀 |
|
| 诚征版主 | 版主堂 | 意见建议 | 大史记 | 论坛地图