IF EXISTS ( SELECT *
FROM sysobjects
WHERE id = OBJECT_ID(N'TableDesc')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE TableDesc;
END;
GO
CREATE PROCEDURE TableDesc
@Name NVARCHAR(100)
AS
BEGIN
SELECT 表名 = CASE WHEN a.colorder = 1 THEN d.name
ELSE ''
END ,
表说明 = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '')
ELSE ''
END ,
字段序号 = a.colorder ,
字段名 = a.name ,
标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√'
ELSE ''
END ,
主键 = CASE WHEN EXISTS ( SELECT 1
FROM sysobjects
WHERE xtype = 'PK'
AND parent_obj = a.id
AND name IN (
SELECT name
FROM sysindexes
WHERE indid IN (
SELECT indid
FROM sysindexkeys
WHERE id = a.id
AND colid = a.colid ) ) )
THEN '√'
ELSE ''
END ,
类型 = b.name ,
占用字节数 = a.length ,
长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION') ,
小数位数 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) ,
允许空 = CASE WHEN a.isnullable = 1 THEN '√'
ELSE ''
END ,
默认值 = ISNULL(e.text, '') ,
字段说明 = ISNULL(g.[value], '')
FROM syscolumns a
LEFT JOIN systypes b ON a.xusertype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id
AND d.xtype = 'U'
AND d.name <> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN sys.extended_properties g ON a.id = g.major_id
AND a.colid = g.minor_id
LEFT JOIN sys.extended_properties f ON d.id = f.major_id
AND f.minor_id = 0
WHERE d.name = @Name --如果只查询指定表,加上此where条件，tablename是要查询的表名；去除where条件查询所有的表信息
ORDER BY a.id ,
a.colorder;
END
GO
