34,593
社区成员
发帖
与我相关
我的任务
分享
--查询用户表对象信息
SELECT Tab.Name AS [表名],
Tab.create_date AS [创建时间],
Tab.modify_date AS [最后修改时间],
Col.Name AS [列名],
Type.name AS [数据类型],
Col.max_length AS [字段长度],
CASE
WHEN pk.is_primary_key = 1 THEN 'Y'
ELSE 'N'
END AS [是否主键],
CASE
WHEN Col.is_identity = 1 THEN 'Y'
ELSE 'N'
END AS [是否自增],
identity_columns.seed_value AS [自增种子],
identity_columns.increment_value AS [自增步长],
CASE
WHEN Col.is_nullable = 1 THEN 'Y'
ELSE 'N'
END AS [是否允许为NULL],
Def.text AS [默认值],
CASE
WHEN Col.is_computed = 1 THEN 'Y'
ELSE 'N'
END AS [是否计算列],
computed_columns.definition AS [计算公式],
Col_Desc.Value AS [列备注]
FROM sys.objects Tab
INNER JOIN sys.columns Col
ON Tab.object_id = Col.object_id
INNER JOIN sys.types Type
ON Col.system_type_id = Type.system_type_id
LEFT JOIN sys.identity_columns identity_columns
ON Tab.object_id = identity_columns.object_id
AND Col.column_id = identity_columns.column_id
LEFT JOIN syscomments Def
ON Col.default_object_id = Def.ID
LEFT JOIN(SELECT index_columns.object_id,
index_columns.column_id,
indexes.is_primary_key
FROM sys.indexes indexes
INNER JOIN sys.index_columns index_columns
ON indexes.object_id = index_columns.object_id
AND indexes.index_id = index_columns.index_id
WHERE indexes.is_primary_key = 1/*主键*/
) PK
ON Tab.object_id = PK.object_id
AND Col.column_id = PK.column_id
LEFT JOIN sys.computed_columns computed_columns
ON Tab.object_id = computed_columns.object_id
AND Col.column_id = computed_columns.column_id
LEFT JOIN sys.extended_properties Col_Desc
ON Col_Desc.major_id = Tab.object_id
AND Col_Desc.minor_id = Col.Column_id
AND Col_Desc.class = 1
WHERE Tab.type = 'U'
AND Tab.Name NOT LIKE'sys%'
ORDER BY Tab.create_date