sqlserver数据库如何快速查看表结构sql

article/2025/10/2 20:31:10

有时候，我们熟悉一个新系统，首先就需要熟悉其数据库的各个表结构。这时候，其实我们可以通过一个SQL清晰的查看表结构。

SQL如下：

SELECT
CASEWHENcol.colorder = 1 THENobj.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 小数位数 ,CASEWHEN COLUMNPROPERTY( col.id, col.name, 'IsIdentity' ) = 1 THEN'√' ELSE '' END AS 标识 ,CASEWHEN EXISTS (SELECT1 FROMdbo.sysindexes siINNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indidINNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colidINNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK' WHEREsc.id = col.id AND sc.colid = col.colid ) THEN'√' ELSE '' END AS 主键 ,CASEWHEN col.isnullable = 1 THEN'√' ELSE '' END AS 允许空 ,ISNULL( comm.text, '' ) AS 默认值 FROMdbo.syscolumns colLEFT JOIN dbo.systypes t ON col.xtype = t.xusertypeINNER JOIN dbo.sysobjects obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.idLEFT 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' WHEREobj.name IN ( 'order_info' ) -- 表名order_info;