-------------------------------------------------- ----------- MS SQLSERVER ------------------------------------- ----------------------------
- table shows SELECT dbo.sysobjects.name AS TableName, dbo.sysproperties [value] AS TableDescFROM dbo.sysproperties INNER JOIN dbo.sysobjects ON dbo.sysproperties.id = dbo.sysobjects.idWHERE (dbo.sysproperties.smallid = 0. ) ORDER by dbo.sysObjects.name
- Field Description SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.colid, dbo.syscolumns.name AS ColName, dbo.sysproperties [value] AS ColDescFROM 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.colidORDER BY dbo.sysobjects.name, dbo.syscolumns.colid
- Main key, foreign key information (Simplified) Select c_obj.name as constraint_name, t_obj.name as table_name, col.name as column_name, case col. ketth ref.fkey1 kilns111 ref.fkey2 Then 2 When Ref.fkey3 Then 3 When Ref.fkey4 Then Ref.fkey5 Then 5 When Ref.fkey6 Then 6 When Ref.fkey7 Ten 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_POSITIONfrom sysobjects c_obj, sysobjects t_obj, syscolumns col, sysreferences refwhere 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.f.fKey4, REFKEY5, REFKEY6, REFKEY7, REFKEY8, REFKEY9, REFKEY10, REFKEY11, REFKEY12, REFKEY13, REFKEY14, REFKEY15, REFKEY14, REFKEY15, REFKEY16) And c_obj .id = ref.constidunion select i.name as CONSTRAINT_NAME, t_obj.name as TABLE_NAME, col.name as COLUMN_NAME, v.number as ORDINAL_POSITIONfrom sysobjects c_obj, sysobjects t_obj, syscolumns col, master.dbo.spt_values v, sysindexes iwhere 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 color = = 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'Rder by constraint_name, Ordinal_Position
- primary key, foreign key control (simplified) select fc_obj.name as CONSTRAINT_NAME, i.name as UNIQUE_CONSTRAINT_NAMEfrom sysobjects fc_obj, sysreferences r, sysindexes i, sysobjects pc_objwhere 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 - -------------------------------------------------- -------------
- Table Information Select * from all_tab_comments twhere = 'dbo'
- Columns SELECT * from ALL_COL_COMMENTS Twhere Owner = 'DBO'
- primary key, foreign key control select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAMEfrom all_constraintswhere owner = 'DBO' and (Constraint_Type = 'P' or Constraint_Type = 'R')
- Primon button, foreign key information Select * from all_cons_columns where owner = 'dbo'Rder by constraint_name, position
-------------------------------------------------- --------------- Access ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------- // Access system table MSYSObjects store attribute fields are binary formats, not directly analyzed // You can use the OpenSchema method you bring on ADO to get relevant information.
// Use adoint.pas // PO: TABLENAME / / DBCON: TADOCONNAME / DS: Tadodataset - Table Information DBCon.Openschema (Sitables, Vararrayof ([Null, Null, 'Table'], EMPTYPARAM, DS);
- Columns DBCON.OPENSCHEMA (SiColumns, Vararrayof ([NULL, NULL, 'PO']), EMPTYPARAM, DS); - Primary Key DBCon.Openschema (SiprimaryKeys, EmptyParam, EmptyParam, DS); - Programs, foreign key Control DBCon.OpenSchema (SiForeignKeys, EmptyParam, EmptyParam, DS);
