SQL Doc获取表结构信息的SQL

核心提示：不能不佩服Red Gate的强大，旗下产品SQL Prompt 是我最喜欢的，SQL Doc获取表结构信息的SQL，SQL Doc生成数据库文档的确非常方便，而且支持在操作中修改表、字段、数据库、存储过程、函数的描述信息，果然是厉害，其实如果要做数据库实体类代码生成，生成的文档支持html和chm两种方式，排版效果都很

不能不佩服Red Gate的强大，旗下产品SQL Prompt 是我最喜欢的，SQL Doc生成数据库文档的确非常方便，而且支持在操作中修改表、字段、数据库、存储过程、函数的描述信息，生成的文档支持html和chm两种方式，排版效果都很不错。

很好奇SQL Doc是怎么获取数据库所有表的信息，比如数据库下的所以表名，表的字段信息，字段是否为空，关系，索引信息等，于是用Sql Profiler监视一下，果然是厉害，其实如果要做数据库实体类代码生成，ORM都有可能用到。

SELECT sp.major_id AS id,
so.name AS ObjectName,
sys.schemas.name AS ObjectOwner,　
so.type AS ObjectType,
sp.minor_id AS smallid,　
sp.name AS PropertyName,
sp.value AS PropertyValue,
CAST(sp.value AS varbinary(MAX)) AS PropertyValueBinary,
sql_variant_property(sp.value,'BaseType') AS PropertyValueBaseType,
sql_variant_property(sp.value,'Precision') AS PropertyValuePrecision,
sql_variant_property(sp.value,'Scale') AS PropertyValueScale,
sql_variant_property(sp.value,'MaxLength') AS PropertyValueMaxLength,
CASE sp.class WHEN 4 THEN USER_NAME(sp.major_id) END AS UserName,
CASE sp.class WHEN 6 THEN (SELECT TOP 1 name FROM sys.types sst WHERE sst.user_type_id=sp.major_id) END AS UDTName,
CASE sp.class WHEN 6 THEN (SELECT TOP 1 sys.schemas.name FROM sys.schemas INNER JOIN sys.types ON sys.types.schema_id=sys.schemas.schema_id WHERE sys.types.user_type_id=sp.major_id) END AS UDTOwner,
CASE sp.class
　　　 WHEN　 3 THEN (SELECT TOP 1 ss2.name as a FROM sys.schemas ss2 WITH (NOLOCK) WHERE ss2.schema_id = sp.major_id)
　　　 WHEN　 5 THEN (SELECT TOP 1 sa2.name as a FROM sys.assemblies sa2 WITH (NOLOCK) WHERE sa2.assembly_id = sp.major_id)
　　　 WHEN 10 THEN (SELECT TOP 1 sxsc2.name as a FROM sys.xml_schema_collections sxsc2 WITH (NOLOCK) WHERE sxsc2.xml_collection_id = sp.major_id) COLLATE database_default
　　　 WHEN 15 THEN (SELECT TOP 1 smt2.name FROM sys.service_message_types smt2 WITH (NOLOCK) WHERE smt2.message_type_id = sp.major_id)
　　　 WHEN 16 THEN (SELECT TOP 1 ssc2.name FROM sys.service_contracts ssc2 WITH (NOLOCK) WHERE ssc2.service_contract_id = sp.major_id)
　　　 WHEN 17 THEN (SELECT TOP 1 ss2.name FROM sys.services ss2 WITH (NOLOCK) WHERE ss2.service_id = sp.major_id)
　　　 WHEN 18 THEN (SELECT TOP 1 srs2.name FROM sys.remote_service_bindings srs2 WITH (NOLOCK) WHERE srs2.remote_service_binding_id = sp.major_id)
　　　 WHEN 19 THEN (SELECT TOP 1 sr2.name FROM sys.routes sr2 WITH (NOLOCK) WHERE sr2.route_id = sp.major_id)
　　　 WHEN 20 THEN (SELECT TOP 1 sps2.name FROM sys.partition_schemes sps2 WITH (NOLOCK) WHERE sps2.data_space_id = sp.major_id)
　　　 WHEN 21 THEN (SELECT TOP 1 spf2.name FROM sys.partition_functions spf2 WITH (NOLOCK) WHERE spf2.function_id = sp.major_id)
　　　 ELSE CAST(NULL AS sysname)
END AS YukonObjectName,
CASE sp.class
　　　 WHEN 10 THEN SCHEMA_NAME(( SELECT TOP 1 sx2.schema_id FROM sys.xml_schema_collections sx2 WITH (NOLOCK) WHERE sx2.xml_collection_id = sp.major_id))
　　　 WHEN 6 THEN SCHEMA_NAME((SELECT TOP 1 st2.schema_id FROM sys.types st2 WITH (NOLOCK) WHERE st2.user_type_id = sp.major_id))
ELSE
USER_NAME(CASE sp.class
　　　 WHEN 3 THEN (SELECT TOP 1 sch2.principal_id FROM sys.schemas sch2 WITH (NOLOCK) WHERE sch2.schema_id = sp.major_id)
　　　 WHEN 4 THEN (SELECT TOP 1 sp2.owning_principal_id FROM sys.database_principals sp2 WITH (NOLOCK) WHERE sp2.principal_id = sp.major_id)
　　　 WHEN 5 THEN (SELECT TOP 1 sa2.principal_id FROM sys.assemblies sa2 WITH (NOLOCK) WHERE sa2.assembly_id = sp.major_id)
　　　 WHEN 15 THEN (SELECT TOP 1 smt2.principal_id FROM sys.service_message_types smt2 WITH (NOLOCK) WHERE smt2.message_type_id = sp.major_id)
　　　 WHEN 16 THEN (SELECT TOP 1 ssc2.principal_id FROM sys.service_contracts ssc2 WITH (NOLOCK) WHERE ssc2.service_contract_id = sp.major_id)
　　　 WHEN 17 THEN (SELECT TOP 1 ss2.principal_id FROM sys.services ss2 WITH (NOLOCK) WHERE ss2.service_id = sp.major_id)
　　　 WHEN 18 THEN (SELECT TOP 1 srs2.principal_id FROM sys.remote_service_bindings srs2 WITH (NOLOCK) WHERE srs2.remote_service_binding_id = sp.major_id)
　　　 WHEN 19 THEN (SELECT TOP 1 sr2.principal_id FROM sys.routes sr2 WITH (NOLOCK) WHERE sr2.route_id = sp.major_id)
　　　 WHEN 23 THEN (SELECT TOP 1 sft2.principal_id FROM sys.fulltext_catalogs sft2 WITH (NOLOCK) WHERE sft2.fulltext_catalog_id = sp.major_id)
　　　 WHEN 24 THEN (SELECT TOP 1 ssk2.principal_id FROM sys.symmetric_keys ssk2 WITH (NOLOCK) WHERE ssk2.symmetric_key_id = sp.major_id)
　　　 WHEN 26 THEN (SELECT TOP 1 sak2.principal_id FROM sys.asymmetric_keys sak2 WITH (NOLOCK) WHERE sak2.asymmetric_key_id = sp.major_id)
　　　 WHEN 25 THEN (SELECT TOP 1 sc2.principal_id FROM sys.certificates sc2 WITH (NOLOCK) WHERE sc2.certificate_id = sp.major_id)
　　　 ELSE CAST(NULL AS sysname)
END)
END AS YukonObjectOwner,
CASE sp.class
　　　 WHEN 2 then spar.name
　　　 ELSE sc.name
END AS FieldName,
si.name AS IndexName,
pso.name AS ParentName,
pu.name AS ParentOwner,
pso.type AS ParentType,
sp.class AS type,
ddlt.name AS DdlTriggerName,
en.name AS EventNotificiationName,
qensq.name AS ENQueueName,
qensqs.name AS ENQueueSchema

FROM sys.extended_properties AS sp WITH (NOLOCK)
LEFT JOIN sys.objects　 AS so WITH (NOLOCK) ON so.object_id=sp.major_id
LEFT JOIN sys.schemas　 WITH (NOLOCK) ON sys.schemas.schema_id=so.schema_id
LEFT JOIN sys.columns AS sc WITH (NOLOCK) ON sc.object_id=sp.major_id AND sc.column_id=sp.minor_id
LEFT JOIN sys.parameters AS spar WITH (NOLOCK) ON spar.object_id=sp.major_id AND spar.parameter_id=sp.minor_id
LEFT JOIN sys.indexes si WITH (NOLOCK) ON si.object_id=sp.major_id AND si.index_id=sp.minor_id
LEFT JOIN sys.objects pso　 WITH (NOLOCK) ON so.parent_object_id=pso.object_id
LEFT JOIN sys.schemas pu　 WITH (NOLOCK) ON pso.schema_id=pu.schema_id
LEFT JOIN sys.triggers　 AS ddlt WITH (NOLOCK) ON ddlt.object_id=sp.major_id
LEFT JOIN sys.event_notifications　 AS en WITH (NOLOCK) ON en.object_id=sp.major_id
LEFT JOIN sys.service_queues　 AS qensq WITH (NOLOCK) ON qensq.object_id=en.parent_id
LEFT JOIN sys.schemas AS qensqs WITH (NOLOCK) ON qensqs.schema_id=qensq.SCHEMA_ID
WHERE so.NAME='schools'