使用下面的存储过程：
如果输入表名则取出此表，如果不输入表名则选出全表结构，有问题请继续问我。
--此存储过程可以取出所输入表的字段信息，如果表名为空则列出当前数据库中的所有表的信息，
--如果@WITHVIEW参数不为0则列出视图的字段信息，为0则不列视图信息
CREATE PROCEDURE [dbo].[TABLE_INFO](@TABLENAME VARCHAR(50),@WITHVIEW BIT=0 )
AS
BEGIN
if NOT exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[DATADICT]') and OBJECTPROPERTY(id, N'IsUserTable')=1)
BEGIN
IF @TABLENAME IS NULL
BEGIN
IF @WITHVIEW=0
BEGIN
SELECT CASE O.TYPE WHEN 'V ' THEN '视图: ' + O.NAME ELSE '表: ' + O.NAME END AS 表名,
C.name AS 列名, T.name AS 类型, C.length AS 长度, C.prec AS 精度,
C.scale AS 小数位数,
CASE C.ISNULLABLE WHEN 0 THEN 'YES ' ELSE 'NO ' END AS 可否为空
FROM sys.syscolumns AS C INNER JOIN
sys.systypes AS T ON C.xusertype = T.xusertype INNER JOIN
sys.sysobjects AS O ON C.id = O.id
WHERE (O.type = 'U ') AND (O.name NOT IN ('SYSCONSTRAINTS ', 'SYSSEGMENTS '))
ORDER BY O.type, O.name, 列名
END
ELSE
BEGIN
SELECT 表名= CASE O.TYPE WHEN 'V ' THEN '视图: '+ O.NAME ELSE '表: '+ O.NAME END
,C.NAME AS 列名,T.NAME AS 类型,C.LENGTH AS 长度,C.PREC 精度,C.SCALE AS 小数位数 ,
可否为空=
CASE C.ISNULLABLE
WHEN 0
THEN 'YES '
ELSE 'NO '
END
FROM SYSCOLUMNS C,SYSOBJECTS O ,SYSTYPES T
WHERE T.xUSERTYPE=C.xUSERTYPE AND
O.ID=C.ID AND (O.TYPE= 'U ' OR O.TYPE= 'V ')
AND O.NAME NOT IN ( 'SYSCONSTRAINTS ', 'SYSSEGMENTS ')
ORDER BY O.TYPE,O.NAME,C.NAME
END
END
ELSE
BEGIN
SELECT C.name AS 列名, T.name AS 类型, C.length AS 长度, C.prec AS 精度,
C.scale AS 小数位数,
CASE C.ISNULLABLE WHEN 0 THEN 'YES ' ELSE 'NO ' END AS 可否为空
FROM sys.syscolumns AS C INNER JOIN
sys.systypes AS T ON C.xusertype = T.xusertype INNER JOIN
sys.sysobjects AS O ON C.id = O.id
WHERE (O.name = @TABLENAME)
ORDER BY O.type, O.name, 列名
END
END
ELSE
BEGIN
IF NOT @TABLENAME IS NULL
BEGIN
SELECT @TABLENAME AS 表名, A.NAME AS 列名,C.EXPLAIN AS 说明, B.NAME AS 数据类型,
A.LENGTH AS 长度,A.XPREC AS 精度,A.XSCALE AS 小数位数
FROM SYSCOLUMNS A join SYSTYPES AS B on B.XTYPE=A.XTYPE right join DATADICT AS C on C.FIELDS=A.NAME
WHERE ID = OBJECT_ID(@TABLENAME)
AND C.TABLENAME=@TABLENAME
ORDER BY A.NAME
END
ELSE
BEGIN
SELECT 表名= CASE O.TYPE WHEN 'V ' THEN '视图: '+ O.NAME ELSE '表: '+ O.NAME END
, A.NAME AS 列名,C.EXPLAIN AS 说明, B.NAME AS 数据类型,
A.LENGTH AS 长度,A.XPREC AS 精度,A.XSCALE AS 小数位数
FROM SYSCOLUMNS A join SYSTYPES AS B on B.XUSERTYPE=A.XUSERTYPE right join DATADICT AS C on C.FIELDS=A.NAME right join SYSOBJECTS AS O on C.TABLENAME=O.NAME
WHERE A.ID = O.ID
AND (O.TYPE= 'U ' OR O.TYPE= 'V ')
AND O.NAME NOT IN ( 'SYSCONSTRAINTS ', 'SYSSEGMENTS ')
ORDER BY O.TYPE, O.NAME,A.NAME
END
END
END
