SQL Server获取表的元数据

最近客户需要数据库中表的数据字典，也就是需要表名、列名、列的数据类型及长度等信息。

通过下面的sql，可以抓取到表的这些基本信息，注意 不包含视图的信息：

SELECT 
	D.NAME AS TABLE_NAME,
	A.column_id ,
	A.NAME AS COLUMN_NAME,
	CASE WHEN A.IS_IDENTITY=1 THEN '√ ' ELSE '' END AS IS_IDENTITY,
	isnull(CASE WHEN x.is_primary_key=1 and x.type_desc='CLUSTERED' and x.is_unique=1 THEN '主键/聚集/唯一'
	WHEN x.is_primary_key=1 and x.is_unique=1 THEN '主键/唯一'
	WHEN x.type_desc='CLUSTERED' and x.is_unique=1 THEN '聚集/唯一'
	WHEN x.type_desc='CLUSTERED' THEN '聚集'
	WHEN x.is_unique=1 THEN '唯一'
	END + convert(varchar,y.index_id)+'_'+convert(varchar,y.key_ordinal),'') +ISNULL('[外键:'+h.foreignkey+']','') AS PK_FK_INDEX,
	B.NAME+
	case when A.user_type_id in(165,167,173,175,231,239) 
		then '('+(case when A.max_length=-1 then 'max' else convert(varchar,COLUMNPROPERTY(A.object_id,A.NAME,'PRECISION')) end)+')' 
		when A.user_type_id in(106,108) 
		then '('+convert(varchar,A.precision)+','+convert(varchar,A.scale)+')'
	else '' end AS COLUMN_TYPE,

	CASE WHEN A.IS_NULLABLE=1 THEN '√ 'ELSE '' END AS IS_NULL
	--ISNULL(E.definition,'') AS DEFAULT_VALUE,
	--ISNULL(G.[VALUE],'') AS COLUMN_MEAN
FROM SYS.COLUMNS A
LEFT JOIN SYS.TYPES B 
       ON A.system_type_id=B.system_type_id AND A.user_type_id=B.user_type_id
INNER JOIN SYS.TABLES D 
        ON A.object_id=D.object_id-- AND D.TYPE='U' --AND D.NAME<>'DTPROPERTIES'
LEFT JOIN sys.default_constraints E 
       ON A.default_object_id=E.object_id
LEFT JOIN sys.extended_properties G 
       ON A.object_id=G.major_id AND A.column_id=G.minor_id   
LEFT JOIN sys.extended_properties F 
       ON D.object_id=F.major_id AND F.minor_id=0
LEFT JOIN (SYS.INDEXES x join sys.index_columns y on x.index_id=y.index_id and x.object_id=y.object_id )
	on x.object_id=A.object_id and y.column_id=A.column_id and (x.is_primary_key=1 or x.type_desc='CLUSTERED' or x.is_unique=1)
OUTER APPLY(
	select object_name(O.object_id)+'('+O.name+')' as foreignkey 
	FROM SYS.COLUMNS O
	inner JOIN sys.foreign_key_columns P 
	   ON O.object_id=P.referenced_object_id and P.referenced_column_id=O.column_id
	where P.parent_object_id=A.object_id and P.parent_column_id=A.column_id
)H
where D.is_ms_shipped=0