SQL查询表结构示例-优快云博客

本文提供了一个SQL查询示例，用于获取指定表的所有列名、类型、最大长度、是否允许为空、是否为主键及列的注释。通过这个查询可以快速了解表的结构和属性。

select sysobjects.name 表名,syscolumns.name 列名 from sysobjects JOIN syscolumns
ON syscolumns.id=sysobjects.id
WHERE sysobjects.xtype='U' and syscolumns.name in ('列1','列2')

ORDER BY sysobjects.name,syscolumns.name

--查询表中列的类型和说明等
declare @table_name as varchar(max)
set @table_name = 'ProjBaseInfo'
select sys.columns.name 列名, sys.types.name 类型,
sys.columns.max_length 最大长度, sys.columns.is_nullable 允许为空,
(select count(*) from sys.identity_columns
where sys.identity_columns.object_id = sys.columns.object_id
and sys.columns.column_id = sys.identity_columns.column_id) as 标识列 ,
(select value from sys.extended_properties
where sys.extended_properties.major_id = sys.columns.object_id
and sys.extended_properties.minor_id = sys.columns.column_id) as 列说明
from sys.columns, sys.tables, sys.types
where sys.columns.object_id = sys.tables.object_id
and sys.columns.system_type_id=sys.types.system_type_id
and sys.tables.name='ProjBaseInfo' order by sys.columns.column_id

转载于:https://www.cnblogs.com/mingmoth/archive/2012/10/30/2745942.html