sql server查询表结构 sqlserver查看表结构语句

declare @tablename nvarchar(100)='sys_company_employee'--表名  
begin   
if(isnull(@tablename,'')='')  
 return ;  
  
set @tablename=rtrim(ltrim(replace(replace(@tablename,'[',''),']','')))  
  
--快速查询表描述  
SELECT tbs.name 表名,ds.value 描述,case when tbs.type='U' then 'User Table' else tbs.type end  as 类型 ,tbs.crdate as 创建时间  
FROM sysobjects tbs  
LEFT JOIN sys.extended_properties ds ON ds.major_id=tbs.id  
WHERE  ds.minor_id=0 and tbs.name=@tablename;--表名  
  
--快速查看表结构  
SELECT  CASE WHEN col.colorder = 1 THEN obj.name ELSE '' END 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 ;  
  
--快速查询自增长字段  
SELECT  
表名= obj.name,   
列名= col.name,  
是否自增= CASE WHEN COLUMNPROPERTY( col.ID,col.NAME, 'ISIDENTITY ')=1 THEN '√'ELSE '' END,  
主键= CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE= 'PK ' AND PARENT_OBJ=col.ID AND name IN (  
    SELECT NAME FROM SYSINDEXES WHERE INDID IN(  
    SELECT INDID FROM SYSINDEXKEYS WHERE ID = col.ID AND COLID=col.COLID))) THEN '√' ELSE '' END  
FROM SYSCOLUMNS col  
LEFT JOIN SYSTYPES t ON col.XUSERTYPE=t.XUSERTYPE  
INNER JOIN SYSOBJECTS obj ON col.ID=obj.ID AND obj.XTYPE= 'U' AND obj.NAME <> 'DTPROPERTIES '  
where  COLUMNPROPERTY( col.ID,col.NAME, 'ISIDENTITY ')=1   
and obj.name=@tablename --表名  
  
--快速查询表索引信息  
exec sp_helpindex  @tablename   
  
--快速查询表约束  
exec sp_helpconstraint @tablename  
  
end