表说明
SELECT dbosysobjectsname AS TableName
dbosysproperties[value] AS TableDesc
FROM dbosysproperties INNER JOIN
dbosysobjects ON dbosyspropertiesid = dbosysobjectsid
WHERE (dbosyspropertiessmallid = )
ORDER BY dbosysobjectsname
字段说明
SELECT dbosysobjectsname AS TableName lid
dbosyscolumnsname AS ColName dbosysproperties[value] AS ColDesc FROM dbosysproperties INNER JOIN
dbosysobjects ON dbosyspropertiesid = dbosysobjectsid INNER JOIN
dbosyscolumns ON dbosysobjectsid = dbosyscolumnsid AND
dbosyspropertiessmallid = lid
ORDER BY dbosysobjectsname lid
主键外键信息(简化)
select
c_objnameas CONSTRAINT_NAME
t_objnameas TABLE_NAME
colnameas COLUMN_NAME
case lid
when reffkey then
when reffkey then
when reffkey then
when reffkey then
when reffkey then
when reffkey then
when reffkey then
when reffkey then
when reffkey then
when reffkey then
when reffkey then
when reffkey then
when reffkey then
when reffkey then
when reffkey then
when reffkey then
endas ORDINAL_POSITION
from
sysobjects c_obj
sysobjects t_obj
syscolumns col
sysreferencesref
where
permissions(t_objid) !=
and c_objxtype in (F )
and t_objid = c_objparent_obj
and t_objid = colid
and lid in
(reffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkey)
and c_objid = nstid
union
select
iname as CONSTRAINT_NAME
t_objnameas TABLE_NAME
colnameas COLUMN_NAME
vnumberas ORDINAL_POSITION
from
sysobjectsc_obj
sysobjectst_obj
syscolumnscol
masterdbospt_valuesv
sysindexesi
where
permissions(t_objid) !=
and c_objxtype in (UQ PK)
and t_objid = c_objparent_obj
and t_objxtype= U
and t_objid = colid
and colname = index_col(t_objnameiindidvnumber)
and t_objid = iid
and c_objname= iname
and vnumber>
and vnumber<= ikeycnt
and vtype= P
order by CONSTRAINT_NAME ORDINAL_POSITION
主键外键对照(简化)
select
fc_objname as CONSTRAINT_NAME
iname as UNIQUE_CONSTRAINT_NAME
from
sysobjects fc_obj
sysreferences r
sysindexes i
sysobjects pc_obj
where
permissions(fc_objparent_obj) !=
and fc_objxtype = F
and nstid= fc_objid
and rrkeyid= iid
and rrkeyindid= iindid
and rrkeyid= pc_objid
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 ADOIntpas
//po: TableName
//DBCon:TADOConnection
/ds:TADODataSet
表信息
DBConOpenSchema(siTables VarArrayOf([Null Null Table]) EmptyParam ds);
列信息
DBConOpenSchema(siColumns VarArrayOf([Null Null po]) EmptyParam ds);
主键
DBConOpenSchema(siPrimaryKeys EmptyParam EmptyParam ds);
主键外键对照
DBConOpenSchema(siForeignKeys EmptyParam EmptyParam ds);
