如何获取MSSQLServer,Oracel,Access中的数据字典信息

1、如何获取如何获取 MSSQLServer,Oracel,AccessMSSQLServer,Oracel,Access 中的数据字中的数据字典信息典信息本文关键字：本文关键字：获取MSSQLServerOracelAccess数据字典- MS SQLServer -表说明SELECT AS TableName,dbo.sysproperties.value AS TableDescFROM dbo.sysproperties INNER JOINdbo.sysobjects ON dbo.sysproperties.id = dbo.sysobjects

2、.idWHERE (dbo.sysproperties.smallid = 0)ORDER BY -字段说明SELECT AS TableName, dbo.syscolumns.colid, AS ColName, dbo.sysproperties.value AS ColDescFROM dbo.sysproperties INNER JOINdbo.sysobjects ON dbo.sysproperties.id = dbo.sysobjects.id INNER J

3、OINdbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id ANDdbo.sysproperties.smallid = dbo.syscolumns.colid本文关键字：本文关键字：获取 MSSQL Server Oracel Access 数据字典ORDER BY , dbo.syscolumns.colid-主键、外键信息(简化)selectc_as CONSTRAINT_NAME,t_as TABLE_NAME,as COLUMN_NAME,

4、case col.colidwhen ref.fkey1 then 1when ref.fkey2 then 2when ref.fkey3 then 3when ref.fkey4 then 4when ref.fkey5 then 5when ref.fkey6 then 6when ref.fkey7 then 7when ref.fkey8 then 8when ref.fkey9 then 9when ref.fkey10 then 10when ref.fkey11 then 11when ref.fkey12 then 12when ref.fkey13 then 13when

5、ref.fkey14 then 14when ref.fkey15 then 15本文关键字：本文关键字：获取 MSSQL Server Oracel Access 数据字典when ref.fkey16 then 16endas ORDINAL_POSITIONfromsysobjects c_obj,sysobjects t_obj,syscolumns col,sysreferences refwherepermissions(t_obj.id) != 0and c_obj.xtype in (#F #)and t_obj.id = c_obj.parent_objand t_obj.i

6、d = col.idand col.colidin(ref.fkey1,ref.fkey2,ref.fkey3,ref.fkey4,ref.fkey5,ref.fkey6,ref.fkey7,ref.fkey8,ref.fkey9,ref.fkey10,ref.fkey11,ref.fkey12,ref.fkey13,ref.fkey14,ref.fkey15,ref.fkey16)and c_obj.id = as CONSTRAINT_NAME,t_as TABLE_NAME,as COLUMN_NAM

7、E,v.numberas ORDINAL_POSITIONfrom本文关键字：本文关键字：获取 MSSQL Server Oracel Access 数据字典sysobjects c_obj,sysobjects t_obj,syscolumns col,master.dbo.spt_values v,sysindexes iwherepermissions(t_obj.id) != 0and c_obj.xtype in (#UQ# ,#PK#)and t_obj.id = c_obj.parent_objand t_obj.xtype = #U#and t_obj.id = col.ida

8、nd = index_col(t_,i.indid,v.number)and t_obj.id = i.idand c_ = and v.number 0and v.number = i.keycntand v.type = #P#order by CONSTRAINT_NAME, ORDINAL_POSITION-主键、外键对照(简化)selectfc_as CONSTRAINT_NAME,as UNIQUE_CONSTRAINT_NAMEfrom本文关键字：本文关键字：获取 MSSQL Server

9、Oracel Access 数据字典sysobjects fc_obj,sysreferences r,sysindexes i,sysobjects pc_objwherepermissions(fc_obj.parent_obj) != 0and fc_obj.xtype = #F#and r.constid = fc_obj.idand r.rkeyid = i.idand r.rkeyindid = i.indidand r.rkeyid = pc_obj.id- ORACLE -表信息select * from all_tab_comments twhere owner=#DBO#-

10、列信息select * from all_col_comments twhere owner=#DBO#-主键、外键对照select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME,R_OWNER, R_CONSTRAINT_NAME本文关键字：本文关键字：获取 MSSQL Server Oracel Access 数据字典from all_constraintswhere owner=#DBO# and (Constraint_Type=#P# orConstraint_Type=#R#)-主键、外键信息select *from all

11、_cons_columnswhere owner=#DBO#order by Constraint_Name, Position- Access - -/Access 中的系统表 MSysobjects 存储属性的字段是二进制格式，不能直接分析/可以采用 ADO 自带的 OpenSchema 方法获得相关信息/use ADOInt.pas/po: TableName/DBCon:TADOConnection/ds:TADODataSet-表信息DBCon.OpenSchema(siTables, VarArrayOf(Null, Null, #Table#),EmptyParam, ds);-列信息DBCon.OpenSchema(siColumns,