Hi，大家好，我是编程小6，很荣幸遇见你，我把这些年在开发过程中遇到的问题或想法写出来，今天说一说SqlServer数据库:查看表结构的SQL「建议收藏」,希望能够帮助你!!!。

SELECT

( CASE WHEN a.colorder= 1 THEN d.name ELSE NULL END ) 表名,

a.colorder 字段序号,

a.name 字段名,

( CASE WHEN COLUMNPROPERTY( a.id, a.name, 'IsIdentity' ) = 1 THEN '√' ELSE '' END ) 标识,

(

CASE

WHEN (

SELECT COUNT

( * )

FROM

sysobjects

WHERE

(

name IN (

SELECT

name

FROM

sysindexes

WHERE

( id = a.id )

AND (

indid IN (

SELECT

indid

FROM

sysindexkeys

WHERE

( id = a.id )

AND (

colid IN (

SELECT

colid

FROM

syscolumns

WHERE

( id = a.id )

AND ( name = a.name )))))))

AND ( xtype = 'PK' )) > 0 THEN

'√' ELSE ''

END

) 主键,

b.name 类型,

a.length 占用字节数,

COLUMNPROPERTY( a.id, a.name, 'PRECISION' ) AS 长度,

isnull( COLUMNPROPERTY( a.id, a.name, 'Scale' ), 0 ) AS 小数位数,

( CASE WHEN a.isnullable= 1 THEN '√' ELSE '' END ) 允许空,

isnull( e.text, '' ) 默认值,

isnull( g.[value], ' ' ) AS [说明]

FROM

syscolumns a

LEFT JOIN systypes b ON a.xtype= 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.class

AND f.minor_id= 0

WHERE

b.name IS NOT NULL

AND d.name= '要查询的表'

ORDER BY

a.id,

a.colorder