//读取数据库中所有的数据库
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(N'Servername') 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
//读取数据库中所有的表
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(N'Servername') 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 = N'microsoft_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 = N'microsoft_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
//读取数据库表中的字段信息
USE [Nop] SELECT clmns.is_identity AS IsIdentity, clmns.name AS [Name], 'Server[@Name=' + quotename(CAST( serverproperty(N'Servername') 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 (N'nchar', N'nvarchar') 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
Oracle数据库Scheme
1，连接数据库字符串
string connStr = "Data Source='" + Utility.Database + "';uid='" + Utility.Username + "';pwd='" + Utility.Password + "';";
2，通过注册表获取oracle实例名方法；
public static string[] GetOracleTnsNames() { // 查询注册表，获取oracle服务文件路径 RegistryKey key = Registry.LocalMachine.OpenSubKey("SOFTWARE").OpenSubKey("ORACLE\HOME0"); if (key == null) { key = Registry.LocalMachine.OpenSubKey("SOFTWARE").OpenSubKey("ORACLE\KEY_OraClient11g_home1"); } if (key == null) { key = Registry.LocalMachine.OpenSubKey("SOFTWARE").OpenSubKey("ORACLE\KEY_OraDb11g_home1"); } if (key == null) return null; string home = (string)key.GetValue("ORACLE_HOME"); string file = home + @" etworkADMIN nsnames.ora"; // 解析文件 string line; ArrayList arr = new ArrayList(); StreamReader sr = new StreamReader(file); while ((line = sr.ReadLine()) != null) { line = line.Trim(); if (line != "") { char c = line[0]; if (c >= 'A' && c <= 'z') { if(line.IndexOf('=')>0) arr.Add(line.Substring(0, line.IndexOf('=')).Trim()); } } } sr.Close(); // 返回字符串数组 return (string[])arr.ToArray(typeof(string)); }
3，读取所有表
select table_name from user_tables order by table_name;
4，读取表字段和类型信息
select 0 Status, a.Column_Name, a.nullable as Nullable, a.data_default as Defaults, a.data_type || '(' || a.data_length || ')' as Column_Type, b.comments as Comments, decode(c.column_name, null, null, 'PRI') as keyflag from user_tab_columns a, user_col_comments b, (select c.table_name, d.column_name from user_constraints c, user_cons_columns d where c.constraint_name = d.constraint_name and c.constraint_type = 'P') c where a.table_name = b.table_name(+) and a.column_name = b.column_name(+) and a.table_name = c.table_name(+) and a.column_name = c.column_name(+) and a.table_name = 'WWW' //www是具体的表名 order by a.column_id
有了这些就能根据表结构来生成一些代码了。