不能不佩服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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
'
最后一行Where so.NAME='schools',是只获取表名为Schools的信息,把这个Where条件去掉,则获取当前数据库的所有对象的架构信息。
相比SubSonic 2.2中获取数据库对象详细信息,SQL Doc要详细和准确一些,经我的测试,对于字段描述信息,如果删除某个表的字段,SubSonic的方式获取字段描述信息有可能会出现错位的情况,下面是SubSonic中的获取数据库表详细信息的SQL:
很好奇SQL Doc是怎么获取数据库所有表的信息,比如数据库下的所以表名,表的字段信息,字段是否为空,关系,索引信息等,于是用Sql Profiler监视一下,果然是厉害,其实如果要做数据库实体类代码生成,ORM都有可能用到。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
最后一行Where so.NAME='schools',是只获取表名为Schools的信息,把这个Where条件去掉,则获取当前数据库的所有对象的架构信息。
相比SubSonic 2.2中获取数据库对象详细信息,SQL Doc要详细和准确一些,经我的测试,对于字段描述信息,如果删除某个表的字段,SubSonic的方式获取字段描述信息有可能会出现错位的情况,下面是SubSonic中的获取数据库表详细信息的SQL:
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)