最近客户需要数据库中表的数据字典,也就是需要表名、列名、列的数据类型及长度等信息。
通过下面的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