---------------------------------------------- --------------- MS SQLServer ----------------- ----------------------------------------------
--表说明 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(siPrimaryKeys, EmptyParam, EmptyParam, ds); --主键、外键对照 DBCon.OpenSchema(siForeignKeys, EmptyParam, EmptyParam, ds);
[C语言系列]winform datagridview如何获取选中行的索引 [Delphi程序]如何获取TWebBrowser控件的超级链接 [Sql Server]如何在SqlServer与oracel中进行分页的讨论!
|