查询sqlserver中表信息

 

ALTER PROCEDURE [dbo].[GetTableInfo]
 @tableName NVARCHAR(MAX)
AS
BEGIN
SELECT
--                 CASE
--            WHEN col.colorder = 1 THEN
--                obj.name
--            ELSE
--                ''
--        END AS 表名,
             obj.name AS 表名,
       col.colorder AS 序号,
       col.name AS 列名,
       ISNULL(ep.[value], '') AS 列说明,
       t.name AS 数据类型,
       col.length AS 长度,
       ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数,
       CASE
           WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN
               '√'
           ELSE
               ''
       END AS 标识,
       CASE
           WHEN EXISTS
                (
                    SELECT 1
                    FROM dbo.sysindexes si
                        INNER JOIN dbo.sysindexkeys sik
                            ON si.id = sik.id
                               AND si.indid = sik.indid
                        INNER JOIN dbo.syscolumns sc
                            ON sc.id = sik.id
                               AND sc.colid = sik.colid
                        INNER JOIN dbo.sysobjects so
                            ON so.name = si.name
                               AND so.xtype = 'PK'
                    WHERE sc.id = col.id
                          AND sc.colid = col.colid
                ) THEN
               '√'
           ELSE
               ''
       END AS 主键,
       CASE
           WHEN col.isnullable = 1 THEN
               '√'
           ELSE
               ''
       END AS 允许空,
       ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns col
    LEFT JOIN dbo.systypes t
        ON col.xtype = t.xusertype
    INNER JOIN dbo.sysobjects obj
        ON col.id = obj.id
           AND obj.xtype = 'U'
           AND obj.status >= 0
    LEFT JOIN dbo.syscomments comm
        ON col.cdefault = comm.id
    LEFT JOIN sys.extended_properties ep
        ON col.id = ep.major_id
           AND col.colid = ep.minor_id
           AND ep.name = 'MS_Description'
    LEFT JOIN sys.extended_properties epTwo
        ON obj.id = epTwo.major_id
           AND epTwo.minor_id = 0
           AND epTwo.name = 'MS_Description'
WHERE obj.name = @tableName --表名
ORDER BY col.colorder;

END

 

execute GetTableInfo '表名'