WEB开发网
开发学院数据库MySQL 如何获取MSSQLServerOracelAccess数据字典信息 阅读

如何获取MSSQLServerOracelAccess数据字典信息

 2007-11-11 15:38:06 来源:WEB开发网   
核心提示:--表说明SELECT dbo.sysobjects.name AS TableName,dbo.sysproperties.[value] AS TableDescFROM dbo.sysproperties INNER JOINdbo.sysobjects ON dbo.sysproperties.id = dbo

  --表说明
  SELECT dbo.sysobjects.name AS TableName,
  dbo.sysproperties.[value] AS TableDesc
  FROM dbo.sysproperties INNER JOIN
  dbo.sysobjects ON dbo.sysproperties.id = dbo.sysobjects.id
  WHERE (dbo.sysproperties.smallid = 0)
  ORDER BY dbo.sysobjects.name
  
  --字段说明
  SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.colid,
  dbo.syscolumns.name AS ColName, dbo.sysproperties.[value] AS ColDesc FROM dbo.sysproperties INNER JOIN
  dbo.sysobjects ON dbo.sysproperties.id = dbo.sysobjects.id INNER JOIN
  dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id AND
  dbo.sysproperties.smallid = dbo.syscolumns.colid
  ORDER BY dbo.sysobjects.name, dbo.syscolumns.colid
  
  --主键、外键信息(简化)
  select
  c_obj.name  as CONSTRAINT_NAME
  ,t_obj.name  as TABLE_NAME
  ,col.name  as COLUMN_NAME
  ,case col.colid
  when ref.fkey1 then 1
  when ref.fkey2 then 2
  when ref.fkey3 then 3
  when ref.fkey4 then 4
  when ref.fkey5 then 5
  when ref.fkey6 then 6
  when ref.fkey7 then 7
  when ref.fkey8 then 8
  when ref.fkey9 then 9
  when ref.fkey10 then 10
  when ref.fkey11 then 11
  when ref.fkey12 then 12
  when ref.fkey13 then 13
  when ref.fkey14 then 14
  when ref.fkey15 then 15
  when ref.fkey16 then 16
  end   as ORDINAL_POSITION
  from
  sysobjects c_obj
  ,sysobjects t_obj
  ,syscolumns col
  ,sysreferences ref
  where
  permissions(t_obj.id) != 0
  and c_obj.xtype in ('F ')
  and t_obj.id = c_obj.parent_obj
  and t_obj.id = col.id
  and col.colid  in
  (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 = ref.constid
  union
  select
  i.name   as CONSTRAINT_NAME
  ,t_obj.name  as TABLE_NAME
  ,col.name  as COLUMN_NAME
  ,v.number  as ORDINAL_POSITION
  from
  sysobjects c_obj
  ,sysobjects t_obj
  ,syscolumns col
  ,master.dbo.spt_values v
  ,sysindexes i
  where
  permissions(t_obj.id) != 0
  and c_obj.xtype in ('UQ' ,'PK')
  and t_obj.id = c_obj.parent_obj
  and t_obj.xtype = 'U'
  and t_obj.id = col.id
  and col.name = index_col(t_obj.name,i.indid,v.number)
  and t_obj.id = i.id
  and c_obj.name = i.name
  and v.number > 0
  and v.number <= i.keycnt
  and v.type = 'P'
  
  order by CONSTRAINT_NAME, ORDINAL_POSITION
  
  --主键、外键对照(简化)
  select
  fc_obj.name  as CONSTRAINT_NAME
  ,i.name   as UNIQUE_CONSTRAINT_NAME
  from
  sysobjects fc_obj
  ,sysreferences r
  ,sysindexes i
  ,sysobjects pc_obj
  where
  permissions(fc_obj.parent_obj) != 0
  and fc_obj.xtype = 'F'
  and r.constid = fc_obj.id
  and r.rkeyid = i.id
  and r.rkeyindid = i.indid
  and r.rkeyid = pc_obj.id
  
  ----------------- Oracle(大型网站数据库平台) -------------------
  --表信息
  select * from all_tab_comments t
  where owner='DBO'
  
  --列信息
  select * from all_col_comments t
  where owner='DBO'
  
  --主键、外键对照
  select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME
  from all_constraints
  where owner='DBO' and (Constraint_Type='P' or Constraint_Type='R')
  
  --主键、外键信息
  select *
  from all_cons_columns
  where 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, VarArrayOf([Null, Null, 'po']), EmptyParam, ds);
  
  --主键
  DBCon.OpenSchema(siPrima(最完善的虚拟主机管理系统)ryKeys, EmptyParam, EmptyParam, ds);
  
  --主键、外键对照
  DBCon.OpenSchema(siForeignKeys, EmptyParam, EmptyParam, ds);

Tags:如何 获取 MSSQLServerOracelAccess

编辑录入:coldstar [复制链接] [打 印]
赞助商链接