sqlserver数据库 Schema


//读取数据库中所有的数据库

USE MASTER 
        DECLARE @is_policy_automation_enabled bit
        SET @is_policy_automation_enabled  = (SELECT CONVERT(bit, current_value) FROM msdb.dbo.syspolicy_configuration WHERE name = Enabled)
SELECT
dtb.name AS [Database_Name],
Server[@Name= + quotename(CAST(serverproperty(NServername) AS sysname),‘‘‘‘) + ] + /Database[@Name= + quotename(dtb.name,‘‘‘‘) + ] AS [Database_Urn],
case when 1=@is_policy_automation_enabled and exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like Server+ /Database\[@ID= + convert(nvarchar(20),dtb.database_id) + \]% ESCAPE \) then 1 else 0 end AS [Database_PolicyHealthState],
dtb.recovery_model AS [Database_RecoveryModel],
suser_sname(dtb.owner_sid) AS [Database_Owner],
              case
              when DATABASEPROPERTY(dtb.name,IsShutDown) is null then 0x200
              else 0
              end |
              case
              when 1 = dtb.is_in_standby then 0x40
              else 0
              end |
              case
              when 1 = dtb.is_cleanly_shutdown then 0x80
              else 0
              end |
              case dtb.state
              when 1 then 0x2
              when 2 then 0x8
              when 3 then 0x4
              when 4 then 0x10
              when 5 then 0x100
              when 6 then 0x20
              else 1
              end
             AS [Database_Status],
dtb.compatibility_level AS [Database_CompatibilityLevel],
ISNULL(dmi.mirroring_role,0) AS [Database_MirroringRole],
ISNULL(dmi.mirroring_state + 1, 0) AS [Database_MirroringStatus],
dtb.recovery_model AS [RecoveryModel],
dtb.user_access AS [UserAccess],
dtb.is_read_only AS [ReadOnly],
dtb.name AS [Database_DatabaseName2]
FROM
master.sys.databases AS dtb
LEFT OUTER JOIN sys.database_mirroring AS dmi ON dmi.database_id = dtb.database_id

WHERE
(CAST(case when dtb.name in (master,model,msdb,tempdb) then 1 else dtb.is_distributor end AS bit)=0)
ORDER BY
[Database_Name] ASC
View Code
//读取数据库中所有的表
 1 USE [Nop]
 2         DECLARE @is_policy_automation_enabled bit
 3         SET @is_policy_automation_enabled  = (SELECT CONVERT(bit, current_value)
 4                                               FROM msdb.dbo.syspolicy_configuration
 5                                               WHERE name = Enabled)
 6 SELECT
 7 tbl.name AS [Name],
 8 SCHEMA_NAME(tbl.schema_id) AS [Schema],
 9 Server[@Name= + quotename(CAST(
10         serverproperty(NServername)
11        AS sysname),‘‘‘‘) + ] + /Database[@Name= + quotename(db_name(),‘‘‘‘) + ] + /Table[@Name= + quotename(tbl.name,‘‘‘‘) +  and @Schema= + quotename(SCHEMA_NAME(tbl.schema_id),‘‘‘‘) + ] AS [Urn],
12 case when 1=@is_policy_automation_enabled and exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like Server + /Database\[@ID= + convert(nvarchar(20),dtb.database_id) + \]+ /Table\[@ID= + convert(nvarchar(20),tbl.object_id) + \]% ESCAPE \) then 1 else 0 end AS [PolicyHealthState],
13 tbl.create_date AS [CreateDate],
14 CAST(
15  case 
16     when tbl.is_ms_shipped = 1 then 1
17     when (
18         select 
19             major_id 
20         from 
21             sys.extended_properties 
22         where 
23             major_id = tbl.object_id and 
24             minor_id = 0 and 
25             class = 1 and 
26             name = Nmicrosoft_database_tools_support) 
27         is not null then 1
28     else 0
29 end          
30              AS bit) AS [IsSystemObject]
31 FROM
32 master.sys.databases AS dtb,
33 sys.tables AS tbl
34 WHERE
35 (CAST(
36  case 
37     when tbl.is_ms_shipped = 1 then 1
38     when (
39         select 
40             major_id 
41         from 
42             sys.extended_properties 
43         where 
44             major_id = tbl.object_id and 
45             minor_id = 0 and 
46             class = 1 and 
47             name = Nmicrosoft_database_tools_support) 
48         is not null then 1
49     else 0
50 end          
51              AS bit)=0)and((db_name()=Nop)and(dtb.name=db_name()))
52 ORDER BY
53 [Schema] ASC,[Name] ASC
View Code

//读取数据库表中的字段信息

USE [Nop]
SELECT
clmns.is_identity AS IsIdentity,
clmns.name AS [Name],
Server[@Name= + quotename(CAST(
        serverproperty(NServername)
       AS sysname),‘‘‘‘) + ] + /Database[@Name= + quotename(db_name(),‘‘‘‘) + ] + /Table[@Name= + quotename(tbl.name,‘‘‘‘) +  and @Schema= + quotename(SCHEMA_NAME(tbl.schema_id),‘‘‘‘) + ] + /Column[@Name= + quotename(clmns.name,‘‘‘‘) + ] AS [Urn],
CAST(ISNULL(cik.index_column_id, 0) AS bit) AS [InPrimaryKey],
CAST(ISNULL((select TOP 1 1 from sys.foreign_key_columns AS colfk where colfk.parent_column_id = clmns.column_id and colfk.parent_object_id = clmns.object_id), 0) AS bit) AS [IsForeignKey],
usrt.name AS [DataType],
ISNULL(baset.name, N‘‘) AS [SystemType],
CAST(CASE WHEN baset.name IN (Nnchar, Nnvarchar) AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
CAST(clmns.precision AS int) AS [NumericPrecision],
CAST(clmns.scale AS int) AS [NumericScale],
clmns.is_nullable AS [Nullable],
clmns.is_computed AS [Computed],
ISNULL(s2clmns.name, N‘‘) AS [XmlSchemaNamespaceSchema],
ISNULL(xscclmns.name, N‘‘) AS [XmlSchemaNamespace],
ISNULL( (case clmns.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint],
CAST(clmns.is_sparse AS bit) AS [IsSparse],
CAST(clmns.is_column_set AS bit) AS [IsColumnSet],
isnull(comments.text,‘‘) as DefaultValue,
isnull(properties.[value],‘‘) as FieldRemark
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.indexes AS ik ON ik.object_id = clmns.object_id and 1=ik.is_primary_key
LEFT OUTER JOIN sys.index_columns AS cik ON cik.index_id = ik.index_id and cik.column_id = clmns.column_id and cik.object_id = clmns.object_id and 0 = cik.is_included_column
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON xscclmns.xml_collection_id = clmns.xml_collection_id
LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id
LEFT OUTER JOIN syscomments AS comments on clmns.default_object_id=comments.id 
LEFT OUTER JOIN sys.extended_properties AS properties on clmns.object_id=properties.major_id AND clmns.column_id = properties.minor_id 

WHERE
(tbl.name=AclRecord and SCHEMA_NAME(tbl.schema_id)=dbo)
ORDER BY
clmns.column_id ASC
View Code

 




sqlserver数据库 Schema,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。