stored procedures

Database
Enthusiast

stored procedures

We have a stored procedure

 

Database DB_A;

CREATE PROCEDURE DB_SP.SAMPLE()

BEGIN

INSERT INTO DB_WH.TABLE1 SEL * FROM TABLE2;

END

 

 

The procedure compiles successfully.(Say USER1 complied this) Based on the stored procedure default security (i.e SQL SECURITY DEFINER), I assume when the stored procedure is called, it looks for TABLE2 in schema DB_A.

 

Say, TABLE2 is also in another schema DB_B. Still the procedure executes because it looks for TABLE2 in DB_A.

 

Now how does another user (say USER2) who is looking in to the stored procedure code, knows which schema (DB_A or DB_B) to use for TABLE2? is this stored anywhere in dbc tables/views

 

1 REPLY
Senior Apprentice

Re: stored procedures

HELP PROCEDURE DB_SP.SAMPLE ATTRIBUTES returns the default database, I don't know if this information is stored anywhere in dbc.