使用下面的存储过程：
如果输入表名则取出此表，如果不输入表名则选出全表结构，有问题请继续问我。
--此存储过程可以取出所输入表的字段信息，如果表名为空则列出当前数据库中的所有表的信息，
--如果@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
转载于:https://www.cnblogs.com/goto/archive/2012/08/02/2619934.html
相关资源：SQL语句导出数据库 表结构 数据字典
