SqlServer查询表结构及表/字段说明

所需系统表

-- 对象信息表，U：表 ，V：视图
select * from sys.objects where type='U' or type='V'

-- schema信息表
select * from sys.schemas

-- 表或字段备注信息表
select * from sys.extended_properties

-- 字段信息表
select * from sys.columns

-- 数据类型信息表
select * from sys.types order by name

查询表/视图信息

SELECT
	schemas.name AS schema_name,
	objects.name AS table_name,
	objects.OBJECT_ID AS object_id,
	objects.type AS object_type,
	properties.value AS remarks 
FROM
	sys.objects AS objects
	INNER JOIN sys.schemas AS schemas ON objects.SCHEMA_ID = schemas.SCHEMA_ID 
	AND ( objects.type= 'U' OR objects.type= 'V' ) 
	LEFT JOIN sys.extended_properties AS properties ON objects.OBJECT_ID = properties.major_id 
	AND properties.minor_id= 0

查询字段信息

SELECT
	schemas.name as schema_name,
	objects.name as table_name,
	objects.object_id as object_id,
	columns.name as column_name,
	columns.column_id as column_id,
	columns.max_length as max_length,
	columns.precision as precision,
	columns.scale as scale,
	columns.system_type_id as system_type_id,
	types.name as type_name,
	properties.value as remarks
FROM
	sys.objects AS objects
	INNER JOIN sys.schemas AS schemas ON objects.SCHEMA_ID = schemas.SCHEMA_ID 
	AND ( objects.type= 'U' OR objects.type= 'V' ) 
	and schemas.name='指定schema'
	and objects.name='指定表/视图'
	inner join sys.columns columns on columns.object_id=objects.object_id
	left join sys.extended_properties AS properties ON objects.OBJECT_ID = properties.major_id 
	and properties.minor_id = columns.column_id
	left JOIN sys.types as types on columns.user_type_id=types.user_type_id