Sybase ASE中实现类似oracle的sqlplus中desc命令来简要显示表结构的存储过程
在oracle的sqlplus中有一个命令:desc,能够简要的显示基表、视图等的结构。desc的显示结果简单明了,比较实用。虽然在Sybase ASE中的存储过程sp_help也能实现查看表结构的目的;但是,显示结果很多尤其换行混乱,让人看得眼花缭乱。显示的存储过程:sp_desc能够实现oracle的sqlplus中desc相同的功能,仅仅多了一列:列序号。
效果图如下:
存储过程sp_desc的语法如下:
------=================================================================================
use sybsystemprocs
go
if exists(select 1 from sybsystemprocs.dbo.sysobjects where type="P" and name="sp_desc")
drop proc sp_desc
go
create procedure sp_desc
@tablename varchar(128)
as
declare @table_id int
declare @char_bin_types varchar(30)
set nocount on
begin
select @char_bin_types = char(47)+char(39)+char(45)+char(37)+char(35)+char(34)
select @table_id = object_id(@tablename)
select
"序号"=convert(varchar(3),colid),
"列名"=convert(varchar(30),column_name),
"是否为空?"=convert(varchar(7),nullable),
"类型"=convert(varchar(30),
(case scale
when NULL then
case
when charindex(type_name,",ubigint,tinyint,usmallint,uintn,floatn,smallmoneyn,daten,timen,real,bit,sysname,text,image,smalldatetime,real,longsysname,timestamp,datetimn") > 0 then type_name
else
type_name+"("+convert(varchar,precision)+")"
end
when 0 then
case
when charindex(type_name,",ubigint,tinyint,usmallint,uintn,floatn,smallmoneyn,daten,timen,real,bit,sysname,text,image,smalldatetime,real,longsysname,timestamp,datetimn") > 0 then type_name
else
type_name+"("+convert(varchar,precision)+","+convert(varchar,scale)+")"
end
else
case
when charindex(type_name,",ubigint,tinyint,usmallint,uintn,floatn,smallmoneyn,daten,timen,real,bit,sysname,text,image,smalldatetime,real,longsysname,timestamp,datetimn") > 0 then type_name
else
type_name+"("+convert(varchar,precision)+","+convert(varchar,scale)+")"
end
end
)
)
from
(
SELECT /* INTn, FLOATn, DATETIMEn and MONEYn types */
colid = c.colid,
column_name = c.name,
nullable = /* set nullability from status flag */
(case convert(smallint, convert(bit, c.status&8)) when 0 then "NOT NULL" else "" end),
type_name = rtrim(substring(isnull(stuff(d.type_name,
(c.status&128)/128,
char_length(d.type_name),
"numeric identity"), d.type_name),
1+isnull(d.aux,
ascii(substring("III<<<MMMI<<A<A",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60), 18)),
"precision" = isnull(convert(int, c.prec),
isnull(convert(int, d.data_precision),
convert(int,c.length)))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
scale = isnull(convert(smallint, c.scale),
convert(smallint, d.numeric_scale))
+convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60))
FROM
syscolumns c,
sysobjects o,
sybsystemprocs.dbo.spt_datatype_info d,
systypes t
WHERE
o.id = @table_id
AND c.id = o.id
AND c.usertype = t.usertype
AND t.type = d.ss_dtype
AND d.ss_dtype IN (111, 109, 38, 110, 43) /* Just *N types */
AND c.usertype < 100 /* No user defined types */
UNION
SELECT /* All other types including user data types */
colid = c.colid,
column_name = c.name,
nullable = /* set nullability from status flag */
(case convert(smallint, convert(bit, c.status&8)) when 0 then "NOT NULL" else "" end),
type_name = rtrim(substring(isnull(stuff(d.type_name,
(c.status&128)/128,
char_length(d.type_name),
"numeric identity"), d.type_name),
1+isnull(d.aux,
ascii(substring("III<<<MMMI<<A<A",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60), 18)),
"precision" = isnull(convert(int, c.prec),
isnull(convert(int, d.data_precision),
convert(int,c.length)))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
scale = isnull(convert(smallint, c.scale),
convert(smallint, d.numeric_scale)) +
convert(smallint, isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60))
FROM
syscolumns c,
sysobjects o,
sybsystemprocs.dbo.spt_datatype_info d,
systypes t
WHERE
o.id = @table_id
AND c.id = o.id
AND c.usertype = t.usertype
AND t.type = d.ss_dtype
AND (d.ss_dtype NOT IN (111, 109, 38, 110, 43) /* No *N types */
OR c.usertype >= 100) /* User defined types */
) a
ORDER BY colid
end
go
------=================================================================================
存储过程sp_desc的语法请 :下载