Sybase查询表结构的方法（类似于Oracle的Desc）
浏览数：45 /
时间：2015年06月12日
经过我多次验证，终于找到了Sybase中查询数据库结构的方法了。
在此之前，我就很不解这Sybase数据库怎么就没有和Oracle、MySql一样简便的方法呢
desc 表名;
在网上查询之后，得到一个结果。
sp_help 表名;
但是这样查出了多个结果。虽然里面有我们想要的结果，但我们只取一个，而且还在中间。
怎么办呢？看存储过程源码，把我们想要的结果截取出来吧。
sp_help存储过程在sybsystemprocs库中，
源码：
/* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
/* 4.8 1.1 06/14/90 sproc/src/help */
/*
** Messages for "sp_help" 17570
**
** 17460, "Object must be in the current database."
** 17461, "Object does not exist in this database."
** 17570, "Operating System File"
** 17571, "---------------------"
** 17573, "Object is Remote/External"
** 17574, "-------------------------"
** 17575, "Object existed prior to Omni"
** 17576, "Lock scheme is Allpages"
** 17577, "Lock scheme is Datapages"
** 17578, "Lock scheme is Datarows"
** 17579, "Lock scheme Unknown or Corrupted"
** 17581, "Trigger is disabled."
** 17582, "Trigger is enabled."
** 18571, "The attribute '%1!' is not applicable to tables with allpages lock scheme."
** 17589, "computed column"
** 19456, "Object is a computed column in table '%1!'."
** 19457, "Object is a function-based index key in table '%1!'."
*/
/*
** IMPORTANT NOTE:
** This stored procedure uses the built-in function object_id() in the
** where clause of a select query. If you intend to change this query
** or use the object_id() or db_id() builtin in this procedure, please read the
** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
** pertaining to object-id's and db-id's outlined there, are followed.
*/
CREATE PROCEDURE dbo.sp_help
@objname varchar(767) = NULL /* object name we're after */
as
declare @typeid int /* type of object in systypes */
declare @basetypeid int /* base type in systypes */
declare @lenfactor int /* length factor */
declare @sysstat smallint /* the type of the object */
declare @OS_file varchar(255) /* physical file for ext tab */
declare @msg varchar(1024)
declare @sptlang int
declare @len1 int, @len2 int, @len3 int, @len4 int, @len5 int, @len6 int, @sysstat2 int,
@valstat2 int
declare @sqltext varchar(1024) /* SQL to execute using execute
** immediate. */
declare @and_access int /* cache bits in OBJECT.sysstat2 */
declare @or_access int /* cache bits in OBJECT.sysstat2 */
declare @sqlj_proc int /* indicates a sqlj proc */
declare @opt_ind_status int /* status of optimistic index lock */
declare @opt_ind_value int /* user input value of optimistic index lock */
declare @opt_ind_lock int /* Server constant for optimistic index lock */
declare @opt_text_dealloc int
declare @opt_text_value int
declare @opt_ind2_ascinserts int /* Server constant for ascinserts */
declare @encrypted_col int /* indicates encrypted col */
, @decrypt_def int /* indicates decrypt default col */
, @inrowlob int /* status2: whether column is LOB 'in row' */
, @thiskey varchar(30) /* index key column of
** virtually hashed table.
*/
declare @notruncate int /* indicates no truncation for varbinary columns */
declare @indid int
declare @new_char_value varchar(255) /* hash factors of virtually
** hashed table.
*/
declare @num_keys int /* #index keys columns. */
declare @key_count int,
@msgnum int,
@valstat3 int,
@sysstat3 int,
@sep varchar(2),
@sysopt_name varchar(100)
declare @tab_lob_cmplvl tinyint /* LOB compression level for table */
declare @objtype char(2) /* object type in sysobjects */
declare @db_stat4 int
declare @db_dealloc_ftp int /* Dealloc FTP after NULL update */
declare @tab_keep_ftp int /* Keep FTP after NULL update */
declare @tab_dealloc_ftp int /* Dealloc FTP after NULL update */
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 17100 and 17109
and langid = @@langid)
select @sptlang = 0
end
set nocount on
/*
** If no @objname given, give a little info about all objects.
** Note: 0x80f is the mask for sysstats (=2063decimal).
** 800 is used by Stratus for external tables.
*/
select @sqlj_proc = hextoint("0x2000000")
if @objname is NULL
begin -- {
/*
** Instead of Triggers are sub_types of trigger
** type; so first check for sysstat to be 8, then build the
** prefix of "instead of" for the trigger if needed.
*/
select Name = o.name,
Owner = user_name(uid),
Object_type = (case
when ((o.sysstat & 15) = 8)
then (case
when (o.type = "IT")
then "instead of "
else null
end)
else null
end
)
+ (m.description + x.name)
into #sphelp1rs
from sysobjects o, master.dbo.spt_values v,
master.dbo.spt_values x, master.dbo.sysmessages m
where o.sysstat & 2063 = v.number
and ((v.type = "O" and
(o.type != "XP" and ((o.sysstat2 & @sqlj_proc) = 0
or o.type = "U" or o.type = "S")) and
(o.type != "RS")) /* precomputed result set */
or (v.type = "O1" and o.type = "XP") or
(v.type = "O2" and (o.sysstat2 & @sqlj_proc) != 0
and o.type != "U" and o.type != "S")
or (v.type = "O1" and o.type ="RS") /* precomputed result set */
or (v.type = "EK" and o.type = "EK"))
and v.msgnum = m.error
and isnull(m.langid, 0) = @sptlang
and ((m.error between 17100 and 17109) or
(m.error between 17587 and 17589) or
(m.error between 18903 and 18904) or
(m.error = 17588 or m.error = 17139
or m.error = 17015))
and x.type = "R"
and o.userstat & -32768 = x.number
exec sp_autoformat @fulltabname = #sphelp1rs,
@orderby = "order by 3 desc, 1 asc"
drop table #sphelp1rs
select User_type = s.name,
Storage_type = st.name,
Length = s.length,
Nulls = s.allownulls,
Default_name = object_name(s.tdefault),
Rule_name = object_name(s.domain),
Access_Rule_name = object_name(s.accessrule)
into #sphelp2rs
from systypes s, systypes st
where s.type = st.type
and s.usertype > 99
and st.name not in ("sysname", "longsysname", "nchar", "nvarchar")
and st.usertype < 100
exec sp_autoformat @fulltabname = #sphelp2rs,
@orderby = "order by 1"
drop table #sphelp2rs
/* Display list of Java classes installed in this database */
print ""
select Class_name = x.xtname,
Jar_name = isnull(j.jname, '')
into #sphelp3rs
from sysxtypes x, sysjars j
where x.xtcontainer *= j.jid
exec sp_autoformat @fulltabname = #sphelp3rs,
@orderby = "order by 1"
drop table #sphelp3rs
/* Display list of Java JARs installed in this database */
print ""
exec sp_autoformat @fulltabname = sysjars,
@selectlist = "'Jar_name' = jname",
@orderby = "order by 1"
print ""
return (0)
end -- }
/*
** If this is a 4-part object name, mangle the name appropriately.
** [Note: this must be run in tempdb for successful results.]
*/
if @objname like "%.%.%.%"
begin
select @objname = str_replace(@objname, '.', '_')
end
/*
** Make sure the @objname is local to the current database.
*/
if @objname like "%.%.%" and
substring(@objname, 1, charindex(".", @objname) - 1) != db_name()
begin
/* 17460, "Object must be in the current database." */
raiserror 17460
return (1)
end
/*
** Now check to see if the @objname is in sysobjects. It has to be either
** in sysobjects or systypes.
*/
if not exists (select *
from sysobjects
where id = object_id(@objname))
/*
** It wasn't in sysobjects so we'll check in systypes.
*/
begin -- {
select @typeid = usertype, @basetypeid = type
from systypes
where name = @objname
/*
** Time to give up -- @objname is not in sysobjects or systypes.
*/
if @typeid is NULL
begin
/* 17461, "Object does not exist in this database." */
raiserror 17461
return (1)
end
/*
** Get length factor: types based on unichar/univarchar
** are 2 bytes per character.
*/
if (@basetypeid in (select type from systypes where
name in ('unichar', 'univarchar')))
select @lenfactor = @@unicharsize
else
select @lenfactor = 1
/*
** Print help about a data type
*/
select Type_name = s.name,
Storage_type = st.name,
Length = s.length/ @lenfactor,
Nulls = s.allownulls,
Prec = s.prec,
Scale = s.scale,
Default_name = object_name(s.tdefault),
Rule_name = object_name(s.domain),
Access_Rule_name = object_name(s.accessrule),
Ident = s.ident
into #sphelp4rs
from systypes s, systypes st
where s.usertype = @typeid
and s.type = st.type
and st.name not in ("timestamp", "sysname", "longsysname", "nchar", "nvarchar")
and st.usertype < 100
exec sp_autoformat @fulltabname = #sphelp4rs,
@selectlist = "Type_name, Storage_type,Length,Prec,Scale,Nulls,Default_name,Rule_name,Access_Rule_name,'Identity' = Ident"
drop table #sphelp4rs
return (0)
end -- }
/*
** It's in sysobjects so print out the information.
*/
/*
** Define status bit values for O2_ACCESS_CONTROL
** and O2_OR_ACCESS_CONTROL.
*/
select @and_access = hextoint("0x1000000"),
@or_access = hextoint("0x4000000")
select Name = o.name,
Owner = user_name(uid),
/*
** Decode object type, and figure out if its an access rule
** type. Access rules are sub-types of the rule type; so
** first check for sysstat to be 7. Build the prefix of
** 'AND access', or 'OR access', or '' strings. The prefix
** will be concatenated to the string 'rule' as obtained
** from the other tables.
** Meanwhile, Instead of Triggers are sub_types of trigger
** type; so first check for sysstat to be 8, then build the
** prefix of "instead of" for the trigger if needed.
*/
Object_type = (case
when ((o.sysstat & 15) = 7)
then (case
when ( ((o.sysstat2 & @and_access) > 0)
and ((o.sysstat2 & @or_access) > 0))
then "OR access"
when ((o.sysstat2 & @and_access) > 0)
then "AND access"
else null
end
)
when ((o.sysstat & 15) = 8)
then (case
when (o.type = "IT")
then "instead of "
else null
end)
else null
end
)
+ (m.description + x.name)
, Object_status = convert(varchar(255), ' ')
, Create_date = o.crdate
into #sphelp5rs
from sysobjects o, master.dbo.spt_values v,
master.dbo.spt_values x, master.dbo.sysmessages m
where o.id = object_id(@objname)
and o.sysstat & 2063 = v.number
and ((v.type = "O" and
(o.type != "XP" and ((o.sysstat2 & @sqlj_proc) = 0
or o.type = "U" or o.type = "S"))
and (o.type != "RS")) or /* precomputed result set */
(v.type = "O1" and o.type = "XP") or
(v.type = "O2" and (o.sysstat2 & @sqlj_proc) != 0
and o.type != "U" and o.type != "S") or
(v.type = "O1" and o.type = "RS") or /* precomputed result set */
(v.type = "EK" and o.type = "EK"))
and v.msgnum = m.error
and isnull(m.langid, 0) = @sptlang
and ((m.error between 17100 and 17199) or
(m.error between 17587 and 17589) or
(m.error between 18903 and 18904) or
(m.error = 17588 or m.error = 17015))
and x.type = "R"
and o.userstat & -32768 = x.number
/*
** Set Object_status based on sysstat2 to show any special status the object may have.
** Currently only object status 'deallocate first text page' or 'keep first text page'
** would be displayed based on both table level (by sp_chgattribute) and db level (by
** sp_dboption) settings as below.
**
** 'deallocate first text page' will be displayed when
** o column sysstat2 of sysobjects entry has value 536870912
** o or column sysstat2 of sysobjects entry neither has value 536870912 nor
** -2147483648, but column status4 of sysdatabases entry (for current
** database) has value -2147483648.
**
** otherwise, 'keep first text page' will be displayed.
**
** Table level setting for option dealloc_first_txtpg (sysstat2 in sysobjects)
** 0x2000000 ---- 536870912, deallocate first text page after NULL update
** 0x8000000 ---- -2147483648, keep first text page after NULL update
**
** DB level setting for option 'deallocate first text page' (status4 in sysdatabases)
** 0x8000000 ---- -2147483648, deallocate first text page after NULL update
*/
select @db_dealloc_ftp = hextoint('0x80000000')
select @tab_dealloc_ftp = hextoint('0x20000000')
select @tab_keep_ftp = hextoint('0x80000000')
select @sysstat2 = sysstat2, @objtype = type
from sysobjects
where id = object_id(@objname)
/*
** Only check user tables
*/
if (@objtype = 'U')
begin
select @db_stat4 = status4 from master.dbo.sysdatabases
where dbid = db_id()
if ((@sysstat2 & @tab_dealloc_ftp != 0) or
((@sysstat2 & @tab_keep_ftp = 0) and (@db_stat4 & @db_dealloc_ftp != 0)))
begin
select @valstat2 = @tab_dealloc_ftp
end
else
begin
select @valstat2 = @tab_keep_ftp
end
select @msgnum = msgnum, @sysopt_name = name, @sep = ''
from master.dbo.spt_values
where type = 'O2' and number = @valstat2
if (@msgnum != 17119)
begin
select @sysopt_name = isnull(description, @sysopt_name)
from master.dbo.sysmessages
where error = @msgnum
and isnull(langid, 0) = @sptlang
update #sphelp5rs
set Object_status = ltrim(Object_status + @sep + @sysopt_name)
from #sphelp5rs a, sysobjects o
where a.Name = o.name
and o.sysstat & 15 = 3
and o.id = object_id(@objname)
end
end -- }
/* Set Object_status based on sysstat3 to show any special status the object may have */
select @sysstat3 = sysstat3, @objtype = type
from sysobjects
where id = object_id(@objname)
select @valstat3 = min(number), @sep = ''
from master.dbo.spt_values
where type = 'O3'
and number > 0
and number & @sysstat3 = number
while (@valstat3 is not null)
begin -- {
select @msgnum = msgnum, @sysopt_name = name
from master.dbo.spt_values
where type = 'O3' and number = @valstat3
/* We do not allow PRS objects to be defined on other PRS
** objects. Even if we plan to support this in future, this
** will be a major design change and sp_help will have to be
** updated accordingly to allow that. The bit 0x40 in sysstat3
** is currently overridden to check if PRS objects are defined
** on any object.
*/
if not (@objtype = 'RS' and @msgnum = 17119)
begin
select @sysopt_name = isnull(description, @sysopt_name)
from master.dbo.sysmessages
where error = @msgnum
and isnull(langid, 0) = @sptlang
update #sphelp5rs
set Object_status = ltrim(Object_status + @sep + @sysopt_name)
from #sphelp5rs a, sysobjects o
where a.Name = o.name
and o.sysstat & 15 in (3, 4)
and o.sysstat3 & @valstat3 = @valstat3
end
select @valstat3 = min(number), @sep = ', '
from master.dbo.spt_values
where type = 'O3'
and number > @valstat3
and number & @sysstat3 = number
end -- }
/*
** If the object type is precomputed result set then set Object_status
** information with the following properties:
** a. Refresh Policy: immediate/manual
** b. State: enabled/disabled
** c. QRW State: enabled/disabled for QRW
*/
if @objtype = 'RS'
begin -- {
/* #sphelp5rs will only have 1 row corresponding to a PRS */
update #sphelp5rs
set
Object_status =
/* Save existing Object_status information */
(case
when (Object_status != "") then Object_status + ", "
else null
end) +
/* refresh policy */
(case
when ((@sysstat3 & 8) = 8) then "manual"
when ((@sysstat3 & 16) = 16) then "immediate"
end) + ", " +
/* state */
(case
when ((@sysstat3 & 32) = 32) then "disabled"
else "enabled"
end) + ", " +
/* query rewrite state */
(case
when ((@sysstat3 & 64) = 64) then "disabled for QRW"
else "enabled for QRW"
end)
end -- }
update #sphelp5rs
set Object_status = (select description
from master.dbo.sysmessages
where error = 17661
and isnull(langid, 0) = @sptlang)
where datalength(Object_status) < 2
exec sp_autoformat @fulltabname = #sphelp5rs
drop table #sphelp5rs
/*
** Objects have the following value for sysstat & 15:
** 0 - any/illegal object
** 1 - system table
** 2 - view
** 3 - user table
** 4 - sproc
** 5 - predicate
** 6 - default
** 7 - rule
** 8 - trigger
** 9 - referential constraint
** 10 - sql function
** 11 - extended type
** 12 - sqlj function
** 13 - computed column
** 14 - partition condition
** 15 - encryption key
**
**
** If the object is a system table, view, or user table, we want to check
** out the objects columns here.
*/
select @sysstat = sysstat, @sysstat2 = sysstat2
from sysobjects
where id = object_id(@objname)
/*
** Fix of bug 91669:
** For the current design, a view having nameless column may be created,
** e.g. create view view1 as select sum(column1) from table1.
** In this case, c.name is NULL. Using builtin function isnull() to
** make the following query work.
*/
select @encrypted_col = hextoint("0x00000080")
, @decrypt_def = hextoint("0x00001000")
, @inrowlob = hextoint("0x00040000")
, @notruncate = hextoint("0x00200000")
-- Report on the following "table" objects:
-- 1: System tables
-- 2: Views
-- 3: User tables
--
if (@sysstat & 15) in (1, 2, 3)
begin
select Column_name = isnull(c.name, 'NULL'),
Col_order = colid,
Type = isnull(convert(char(30), x.xtname),
isnull(convert(char(30),
get_xtypename(c.xtype, c.xdbid)),
t.name)),
Length = c.length,
In_row_Len = c.inrowlen,
Prec = c.prec,
Scale = c.scale,
Nulls = convert(bit, (c.status & 8)),
Not_compressed = convert(bit, (isnull(c.status2, 0) & 131072)),
Lob_compression_level = c.lobcomp_lvl,
Default_name = object_name(c.cdefault),
Rule_name = object_name(c.domain),
Access_Rule_name = object_name(c.accessrule),
Computed_Column_object =
case when (c.status3 & 1) = 1
then object_name(c.computedcol)+
" (functional index key)"
when (c.status2 & 32) = 32
then object_name(c.computedcol)+" (materialized)"
when (c.status2 & 16) = 16
then object_name(c.computedcol)+" (virtual)"
else object_name(c.computedcol)
end,
rtype = t.type, utype = t.usertype, xtype = c.xtype,
Ident = convert(bit, (c.status & 0x80)),
Encrypted =
case when (c.status2 is null) then 0
when (c.status2 & @encrypted_col) > 0 then 1
end,
Decrypt_Default_name =
case when (c.status2 is null) then NULL
when (c.status2 & @decrypt_def) > 0 then
(select object_name(a.object)
from sysattributes a
where a.class = 25
and a.attribute = 1
and a.object_info1 = c.id
and a.object_info2 = c.colid)
else NULL
end,
Object_storage =
case
when ((isnull(c.status2,0) & @inrowlob) != 0)
then "in row"
when (c.xstatus is null) then NULL
when (c.xstatus & 1) = 1 then "off row"
else "in row "
end,
Varbinary_is_truncated =
case when (c.status2 is null) then NULL
when (c.status2 & @notruncate) > 0 then 0
else 1
end
into #helptype
from syscolumns c, systypes t, sysxtypes x
where c.id = object_id(@objname)
and c.usertype *= t.usertype
and c.xtype *= x.xtid
/*
** We truncate extended type names >30 characters to 30,
** and print them with a trailing "+" character.
*/
update #helptype
set Type = substring(Type, 1, 29) + "+"
where xtype is not null
and substring(Type, 29, 1) != " "
/* Handle National Characters */
update #helptype
set Length = Length / @@ncharsize
where (rtype = 47 and utype = 24)
or (rtype = 39 and utype = 25)
/* Handle unichar/univarchar */
update #helptype
set Length = Length / @@unicharsize
where rtype in (select type from systypes
where name in ('unichar', 'univarchar'))
/* Handle unsigned types by outputing user syntax */
update #helptype
set Type = "unsigned " +
substring(Type, charindex("u", Type) + 1, 30)
where utype in (44, 45, 46)
/*
** Construct the SQL query against #helptype. Be careful not to
** exceed 255 characters, or the string will get truncated.
*/
select @sqltext = "Column_name,Type, Length"
/* Display in-row length only if there are any in-row LOBs */
if exists (select 1 from #helptype
where In_row_Len is not null)
begin
/* Convert bytes to unichars for length of unitext */
update #helptype
set In_row_Len = In_row_Len / @@unicharsize
where rtype in (select type from systypes
where name = 'unitext')
select @sqltext = @sqltext + ",In_row_Len "
end
select @sqltext = @sqltext
+ ", Prec,Scale,Nulls,Not_compressed,Default_name,Rule_name,Access_Rule_name,Computed_Column_object,'Identity' = Ident"
/* Display the Object_storage only if there are object columns. */
if exists (select * from #helptype
where Object_storage is not null)
begin
select @sqltext = @sqltext + ", Object_storage "
end
/*
** Display the Encrypted column status only if there are encrypted
** columns.
*/
if exists (select * from #helptype
where Encrypted != 0)
begin
select @sqltext = @sqltext + ", Encrypted "
end
/*
** Display the Decrypt_Default_name only if there
** are encrypted columns that have decrypt default values
*/
if exists (select * from #helptype
where Decrypt_Default_name is not null)
begin
select @sqltext = @sqltext + ", Decrypt_Default_name"
end
/*
** Display Lob compression level only if there are compressed LOB
** columns.
*/
if exists (select * from #helptype
where Lob_compression_level is not null)
begin
select @sqltext = @sqltext + ", Lob_compression_level"
end
/*
** Display the varbinary truncation only if there
** is any varbinary column.
*/
if exists (select * from #helptype where Type = "varbinary" or (Type = "binary" and Nulls = 1))
begin
select @sqltext = @sqltext + ", Varbinary_is_truncated"
/* Update the field non-varbinary types */
update #helptype set Varbinary_is_truncated = NULL
where (Type != "varbinary" and (Type != "binary" or Nulls = 0))
or Computed_Column_object is not null
end
exec sp_autoformat @fulltabname = #helptype,
@selectlist = @sqltext,
@orderby = "order by Col_order asc"
drop table #helptype
end
/*
** If this is a table object that has computed columns, display the
** computed column information.
*/
if (@sysstat & 15) in (1, 3)
begin
if exists (select 1 from syscolumns where id = object_id(@objname)
and computedcol is not null and (status3 & 1) !=1)
begin
print ""
execute dbo.sp_helpcomputedcolumn @objname, 0
end
end
/*
** For procedures and sqlj functions, the parameters of the procedures
** are stored in syscolumns.
*/
if @sysstat & 15 in (4, 10, 12)
begin
exec sp_help_params @objname
end
/*
** If the object is an external table, show which OS file it's using.
*/
if @sysstat & 2063 = 2051
begin
select @OS_file = name from sysindexes
where id = object_id(@objname)
and indid in (0,1)
/*
** 17570, "Operating System File"
** 17571, "---------------------"
*/
print ""
exec sp_getmessage 17570, @msg out
print @msg
exec sp_getmessage 17571, @msg out
print @msg
print @OS_file
print ""
end
/*
** If the object is an Omni-managed table, show its storage location.
*/
if (@sysstat2 & 1024 = 1024)
begin
declare @dbname varchar(255),
@site varchar(255),
@owner varchar(255),
@tabname varchar(255),
@retcode int
exec @retcode = sp_namecrack @objname, @site output, @dbname output,
@owner output, @tabname output
select @OS_file = char_value from sysattributes
where class = 9 and attribute = 1 and
object_cinfo = @tabname
/*
** 17573, "Object is Remote/External"
** 17574, "-------------------------"
*/
print ""
exec sp_getmessage 17573, @msg out
print @msg
exec sp_getmessage 17574, @msg out
print @msg
print @OS_file
print ""
if (@sysstat2 & 2048 = 2048)
begin
/*
** 17575, "Object existed prior to Omni"
*/
exec sp_getmessage 17575, @msg out
print @msg
print ""
end
end
/*
** If the object is a table, display sysattributes information
** if there is any. It could be in the current database under
** type "T".
*/
if @sysstat & 15 in (1, 3)
begin
/* Create temporary table for sysattributes data */
create table #sphelpattr
(
class varchar(255),
class_id smallint,
attribute varchar(255),
attribute_id smallint,
int_value int NULL,
char_value varchar(255) NULL,
comments varchar(255) NULL
)
/*
** The join with master..sysattributes here is to
** get the string descriptions for the class and attribute.
** These should never be more than 30 chars, so it's okay to
** truncate them.
*/
insert #sphelpattr (class, class_id, attribute, attribute_id, int_value,
char_value, comments)
select c.char_value,
t.class,
a.char_value,
t.attribute,
t.int_value, t.char_value, t.comments
from sysattributes t, master.dbo.sysattributes c,
master.dbo.sysattributes a
where t.object_type = "T"
and t.object = object_id(@objname)
and c.class = 0 and c.attribute = 0
and a.class = 0 and a.attribute = 1
and t.class = c.object
and t.class = a.object
and t.attribute = a.object_info1
/*
** If the table is virtually hashed, then print the list of index key
** columns and their correspondig hash factors as char_value.
*/
if(exists(select attribute from #sphelpattr
where attribute = 'hash key factors'))
begin
/* The table is Virtually Hashed */
exec sp_getmessage 19586, @msg out
print @msg
print ""
select @new_char_value = ""
select @num_keys = keycnt from sysindexes
where id = object_id(@objname)
select @key_count = 1
select @indid = min(indid)
from sysindexes
where id = object_id(@objname)
and indid > 0
and indid < 255
set nocount on
/*
** Get the list of index key columns.
*/
while (@key_count <= @num_keys)
begin
select @thiskey =
index_col(@objname, @indid, @key_count)
if (@thiskey is NULL)
begin
break
end
if(@key_count > 1)
begin
select @new_char_value = @new_char_value + ", "
end
select @new_char_value = @new_char_value + @thiskey
/*
** In current 'char_value', we have an array of hash
** factors of type 'double' that was written to
** 'char_value' as byte string.
** Hence to get the array of hash factors back:
**
** 1. Divide 'char_value' into disjoint substrings,
** each of length 8 (sizeof double).
** 2. Convert the substring into binary type.
** 3. Convert the binary value into 'double'.
**
** Once obtained the value, convert it to character
** type and append to @new_char_value
*/
select @new_char_value = @new_char_value
+ ":" +
convert(varchar(255),
convert(double precision,
convert(binary,
substring(char_value, @key_count * 8 - 7, 8))))
from #sphelpattr
where attribute = 'hash key factors'
select @key_count = @key_count + 1
end
/*
** Append the 'max_hash_key' to @new_char_value
*/
select @new_char_value = @new_char_value + ", max_hash_key"
select @new_char_value = @new_char_value +
"=" +
convert(varchar(255),
convert(double precision,
convert(binary,
substring(char_value, @key_count * 8 - 7, 8))))
from #sphelpattr
where attribute = 'hash key factors'
update #sphelpattr set char_value = @new_char_value
where attribute = 'hash key factors'
end
/*
** It's possible a cache is deleted without doing an unbind first. After
** a server reboot the binding is marked 'invalid' (int_value = 0).
** If we have such an invalid binding, don't show it in the output.
*/
delete from #sphelpattr
where class_id = 3
and attribute_id = 0
and int_value = 0
if exists (select * from #sphelpattr)
begin
exec sp_autoformat @fulltabname = #sphelpattr,
@selectlist = "'attribute_class' = class, attribute, int_value,char_value, comments"
end
drop table #sphelpattr
end
/*
** If the object is a procedure, display sysattributes information
** if there is any.
*/
if @sysstat & 15 = 4
begin
if exists (select * from sysattributes
where object_type = "P"
and object = object_id(@objname))
begin
/*
** The join with master..sysattributes here is to
** get the string descriptions for the class
** (master.dbo.sysattributes c) and attribute
** (master.dbo.sysattributes a).
*/
select attribute_class =
convert(varchar(512), c.char_value),
attribute = convert(varchar(512), a.char_value),
t.int_value,
char_value = convert(varchar(512), t.char_value),
t.comments
into #sphelp6rs
from sysattributes t, master.dbo.sysattributes c,
master.dbo.sysattributes a
where t.object_type = "P"
and t.object = object_id(@objname)
and c.class = 0 and c.attribute = 0
and a.class = 0 and a.attribute = 1
and t.class = c.object
and t.class = a.object
and t.attribute = a.object_info1
exec sp_autoformat @fulltabname = #sphelp6rs
drop table #sphelp6rs
end
end
/*
** If the object is a table, check out the indexes.
*/
if @sysstat & 15 in (1, 3)
execute dbo.sp_helpindex @objname
/*
** If the object is a table or view, check out the keys.
*/
if @sysstat & 15 in (1, 2, 3)
execute dbo.sp_helpkey @objname
/*
** If the object is a table, check out the slices/partitions
*/
if @sysstat & 15 in (1, 3)
execute dbo.sp_helpartition @objname
/*
** If the object is a trigger, it is either enabled or disabled
*/
if @sysstat & 15 in (0,8)
begin
/*
** 1048676 <==> 0x100000 <==> insert trigger disabled
** 2097152 <==> 0x200000 <==> delete trigger disabled
** 4194304 <==> 0x400000 <==> update trigger disabled
*/
if exists (select 1 from sysobjects trig, sysobjects tab
where trig.id = object_id(@objname)
and trig.deltrig = tab.id
and ((trig.id = tab.deltrig and tab.sysstat2 & 2097152 <> 0)
or (trig.id = tab.updtrig and tab.sysstat2 & 4194304 <> 0)
or (trig.id = tab.instrig and tab.sysstat2 & 1048576 <> 0)))
exec sp_getmessage 17581, @msg out
else
exec sp_getmessage 17582, @msg out
print @msg
end
/*
** If the object is a table, display the table level LOB compression level
*/
if @sysstat & 15 in (1, 3)
begin
select @tab_lob_cmplvl = lobcomp_lvl
from sysobjects
where id = object_id(@objname)
select @msg = "Table LOB compression level " + convert(varchar(3), @tab_lob_cmplvl)
print @msg
if exists (select 1 from sysattributes
where object_type="TI" and object_info1 & 4096 = 4096
and object = object_id(@objname))
begin
select @msg = "Table " + @objname + " has columns dropped by no datacopy method."
print @msg
end
end
/*
** Print the lock scheme information for the table objects
*/
if @sysstat & 15 in (1, 3)
begin
/*
** the bits 0x2000, 0x4000 & 0x8000 represents any
** explicit lock scheme bits that can be set, so
** get them out ( 0x2000 + 0x4000 + 0x8000 = 57344)
*/
select @sysstat2 = (sysstat2 & 57344)
from sysobjects
where id = object_id(@objname)
/*
** The value 0, refers that no lock scheme is
** specified (old style tables) so they support only
** allpages locking
*/
if (@sysstat2 in (0, 8192, 16384, 32768))
begin
if (@sysstat2 = 8192 or @sysstat2 = 0)
begin
/* 17576, "Lock scheme is Allpages" */
exec sp_getmessage 17576, @msg out
print @msg
/* 18571, "The attribute '%1!' is */
/* not applicable to tables with */
/* allpages lock scheme." */
exec sp_getmessage 18571, @msg out
print @msg, 'exp_row_size'
/* 18571, "The attribute '%1!' is */
/* not applicable to tables with */
/* allpages lock scheme." */
exec sp_getmessage 18571, @msg out
print @msg, 'concurrency_opt_threshold'
end
if ( @sysstat2 = 16384 )
begin
/* 17577, "Lock scheme is Datapages" */
exec sp_getmessage 17577, @msg out
print @msg
/* 18983, "The '%1!' attribute is not applicable
** to tables with datarow or datapage lock schemes.
*/
exec sp_getmessage 18983, @msg out
print @msg, 'ascinserts'
end
if (@sysstat2 = 32768)
begin
/* 17578, "Lock scheme is Datarows" */
exec sp_getmessage 17578, @msg out
print @msg
/* 18983, "The '%1!' attribute is not applicable
** to tables with datarow or datapage lock schemes.
*/
exec sp_getmessage 18983, @msg out
print @msg, 'ascinserts'
end
/* Server defined value for ascinserts
** if ascinserts is set, then master..sysindexes.status2=64
*/
select @opt_ind2_ascinserts = 64
print " "
select exp_rowsize "exp_row_size",
res_page_gap "reservepagegap",
fill_factor "fillfactor",
maxrowsperpage "max_rows_per_page",
isnull(identitygap, 0) "identity_gap",
(case when ((@opt_ind2_ascinserts & status2) > 0)
then 1
else 0
end
) "ascinserts"
from sysindexes
where id = object_id(@objname)
and indid <= 1
/* Server defined constant for optimistic index lock */
select @opt_ind_lock = 268435456
/* Server defined constant for dealloc first txtpg */
select @db_dealloc_ftp = hextoint('0x80000000')
select @tab_dealloc_ftp = hextoint("0x020000000")
select @tab_keep_ftp = hextoint('0x80000000')
/* Get db level status */
select @db_stat4 = status4 from master.dbo.sysdatabases
where dbid = db_id()
select @opt_ind_status = (sysstat2 & @opt_ind_lock),
@opt_text_dealloc = (sysstat2 & (@tab_dealloc_ftp | @tab_keep_ftp))
from sysobjects where id = object_id(@objname)
if (@opt_ind_status = 0)
select @opt_ind_value = 0
else
select @opt_ind_value = 1
if ((@opt_text_dealloc = @tab_dealloc_ftp) or
((@opt_text_dealloc = 0) and (@db_stat4 & @db_dealloc_ftp != 0)))
select @opt_text_value = 1
else if (@opt_text_dealloc = @tab_keep_ftp)
select @opt_text_value = 2
else
select @opt_text_value = 0
/*
** Currently, the following information will be same
** for all partitions, only need to print once for
** one of the partitions
*/
select distinct
conopt_thld "concurrency_opt_threshold",
@opt_ind_value "optimistic_index_lock",
@opt_text_value "dealloc_first_txtpg"
from systabstats
where id = object_id(@objname)
and indid <= 1
return (0)
end
else
begin
/* 17579, "Lock scheme Unknown or Corrupted" */
exec sp_getmessage 17579, @msg out
print @msg
return (1)
end
end
/*
** If the object is computed column, display
** computed column information.
** We just simply follow the way sp_helpcomputedcolumn works.
*/
if (@sysstat & 15) = 13
begin
declare @count int, @total int, @row_id int, @config_parm int, @mode int, @tab_name varchar(767)
select @tab_name = object_name(id), @mode = status3 & 1
from syscolumns
where computedcol = object_id(@objname)
/*
** Create temporary table to stored computed column info
*/
create table #helpcpc (colname varchar(255), computedcol int,
property varchar(15) null, row_id numeric identity)
/*
** Get info on computed columns
*/
if @mode != 1
begin
insert into #helpcpc(colname, computedcol, property)
select name,
computedcol,
case when (status2 & 32 = 32) then "materialized"
else "virtual" end
from syscolumns where
computedcol = object_id(@objname) and
status3 & 1 != 1
order by colid
end
/*
** Get Info on functional index keys
*/
else
begin
insert into #helpcpc(colname, computedcol)
select name, computedcol from syscolumns where
computedcol = object_id(@objname) and
status3 & 1 = 1
order by colid
end
/*
** Display the header
*/
if @mode != 1
begin
exec sp_getmessage 19456, @msg output
end
else
begin
exec sp_getmessage 19457, @msg output
end
print ""
print @msg, @tab_name
print ""
/*
** If the configuration parameter 'allow select on syscomments.text'
** is set to 0, then the user can access the text ONLY in the
** following cases
**
** 1. if the user has sa_role
** 2. if the object is owned by the user
**
*/
select @config_parm = value
from master.dbo.syscurconfigs
where config = 258
if @config_parm = 0 and user_id() != 1
begin
/*
** The object needs to be owned by the user
*/
if not exists (select name from sysobjects
where uid = user_id()
and id = object_id(@objname))
begin
/*
** Inform the user the text cannot be displayed and
** print the rest info before return
*/
if @mode != 1
begin
exec sp_getmessage 19334, @msg output
print @msg
exec sp_autoformat #helpcpc, "'Column_Name' = colname,
Property = property"
end
else
begin
exec sp_getmessage 19335, @msg output
print @msg
exec sp_autoformat #helpcpc,
"'Internal_Index_Key_Name' = colname"
end
drop table #helpcpc
return(0)
end
end
/*
** Now display the text
*/
create table #helptxt (text varchar(255), row_id numeric (10) identity)
create table #helpname (colname varchar(255), property varchar(15) null)
select @count = 1
select @total = max(row_id) + 1 from #helpcpc
while @count < @total
begin
insert into #helpname(colname, property)
select colname, property from #helpcpc where row_id = @count
if @mode != 1
begin
exec sp_autoformat #helpname, "'Column_Name' = colname,
Property = property"
end
else
begin
exec sp_autoformat #helpname,
"'Internal_Index_Key_Name' = colname"
end
/*
** See if the object is hidden (SYSCOM_TEXT_HIDDEN will be set)
*/
if exists (select 1 from syscomments m, #helpcpc h where
(m.status & 1 = 1) and
m.id = h.computedcol and
h.row_id = @count)
begin
exec sp_getmessage 19337, @msg output
print @msg
print ""
delete #helpname
select @count = @count + 1
continue
end
insert #helptxt (text) select text from syscomments, #helpcpc
where row_id = @count and id = computedcol order by colid2, colid
print ""
if @mode != 1
begin
exec sp_autoformat #helptxt, "Text = text"
end
else
begin
select @row_id = min(row_id) from #helptxt
update #helptxt set text = right(text, char_length(text)-4)
where row_id = @row_id
select @row_id = max(row_id) from #helptxt
update #helptxt set text = left(text, char_length(text)-13)
where row_id = @row_id
exec sp_autoformat #helptxt, "Expression = text"
end
print ""
select @count = @count + 1
delete #helpname
delete #helptxt
end
drop table #helpcpc, #helpname, #helptxt
end
return (0)
一行一行判断，找下去。
我们发现在源码的556行“if (@sysstat & 15) in (1, 2, 3)”有我们想要的结果，这段IF语句是将表结果查询之后插入一个临时表#helptype中，然后再进行查询出来的。
我们不要那么麻烦，只取里面的三列：字段名，字段类型，字段长度。
当然你想封装成存储过程也可以的，但不能使用desc就是。
select isnull(c.name, 'NULL') '字段名',
<span style="white-space:pre"> </span>t.name '字段类型',
<span style="white-space:pre"> </span>c.length '字段长度'
from syscolumns c, systypes t, sysxtypes x
where c.id = object_id('表名')
<span style="white-space:pre"> </span>and c.usertype *= t.usertype
<span style="white-space:pre"> </span>and c.xtype *= x.xtid;
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
