Get trigger list from database

Hi all :-)

Small problem... I need to know list of tables, stored procedures, triggers and generators... Tables and stored procedures're ok with RX components, but what with triggers and generators ??? Exist some component for it ??? (I need in any SQL server - MSSQL/ORACLE/Borland)....

Thanx
LVL 1
rayofunrealAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
LischkeConnect With a Mentor Commented:
Usually all info about a databases structure is stored in system tables (that's what they are for). Of course these tables aren't the same in all DBMS, so you have to find the way to retrieve the information you need for each database system separately.

Ciao, Mike
0
 
kretzschmarCommented:
hi rayofunreal,

All information you want, you can get from the resultset of this query

select * from user_objects

meikl
0
 
kretzschmarCommented:
hi rayofunreal,

tested in oracle only

meikl

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
rayofunrealAuthor Commented:
Sorry,m this doesn't work with standard BDE :-(

Thanx....

R.
0
 
rayofunrealAuthor Commented:
I mean Interbase....
0
 
kretzschmarCommented:
Hi rayofunreal,

a SQL Statement (Interbase)

Show Triggers

meikl
0
 
rayofunrealAuthor Commented:
I need to do it RUNTIME... Here're some BDE API calls, but I haven't doc about them :-(

Show Triggers ??? I'll try it...
0
 
kretzschmarCommented:
Hi rayofunreal,

you can do it at runtime!
use a tquery-component!

meikl
0
 
LischkeCommented:
There's no consistent way to determine the number of system objects in all available DBMS. This is the reason why you cannot retrive them with the BDE. At least for Interbase you can do it so:

procedure ListItemNames(ItemType: TItemType; Info: PConnectionInfo; List: TStrings; SysInclude: Boolean);

begin
  if AquireConnectionSet(Info) then
    with Info.Connection do
    begin
      with TIB_Cursor.Create(nil) do
      try
        IB_Connection:=Info.Connection;
        case ItemType of
          itDomain     : SQL.Add('select rdb$field_name from rdb$fields');
          itException  : SQL.Add('select rdb$exception_name from rdb$exceptions');
          itFilter     : SQL.Add('select rdb$function_name from rdb$filters');
          itGenerator  : SQL.Add('select rdb$generator_name from rdb$generators');
          itStoredProc : SQL.Add('select rdb$procedure_name from rdb$procedures');
          itTable      : SQL.Add('select rdb$relation_name from rdb$relations');
          itTrigger    : SQL.Add('select rdb$trigger_name from rdb$triggers');
          itUDF        : SQL.Add('select rdb$function_name from rdb$functions');
          itView       : SQL.Add('select rdb$relation_name from rdb$relations');
        end;
        if not SysInclude then SQL.Add('where ((RDB$SYSTEM_FLAG <> 1) or (RDB$SYSTEM_FLAG IS NULL))');
        if ItemType = itView then SQL.Add('and (rdb$view_source is not null)');
        SQL.Add('order by 1');
        Prepare;
        First;
        while not EOF do
        begin
          List.Add(Trim(Fields[0].AsString));
          Next;
        end;
      finally
        Free;
      end;
    end;
end;

I used IB Objects for this code but you can very easy adapt it to your needs. Info.Connection/IB_Connection can be replaced by a TDatabase component and TIB_Cursor by a TQuery component.

Ciao, Mike
0
 
rayofunrealAuthor Commented:
Lischke - come from your points :-)
0
 
rayofunrealAuthor Commented:
Btw. what for other ? will work it ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.