Sitemap

Amicuk Programming Answers

Find all tables used in a stored procedure

-0001-11-30   Views:0

Advertisement

Hi, I have a requirement where i have to find all the tables used in a stored procedures from different  databases. Ex: i have a stored procedure where i use few tables from MASTER database and some from STAGE database.When i have written a query to

Hi,
I have a requirement where i have to find all the tables used in a stored procedures from different  databases.
Ex: i have a stored procedure where i use few tables from MASTER database and some from STAGE database.When i have written a query to find all tables used in the stored procedure, i am getting only those database table where i run the query and procedure
exists.
I have stored procedure SP1 in Master database, but i use the tables from both master and stage.
When i run this, i am getting the tables only from Master database but not from stage. i hope my requirement is clear.
I am trying to find all the tables from all databases used by a stored proc.
;WITH stored_procedures AS (
SELECT 
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d 
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name
Please advice

The replay answer
Advertisement
Your question is not entirely clear. You need to run the query on different databases.
You may find this blog post helpful
How to get information about all databases without a loop
Check the last script in that blog post and modify to your particular purpose.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
My TechNet articles

Go to See the other 5 answers

How to find the columns and tables used in a stored procedure?

Category:DefaultRelease time:-0001-11-30Views:130

Hi, Can someone suggest how to find the columns and tables used within a stored procedure? Thanks VBKFor example: SQL> create or replace   2    procedure p1   3      is   4          cnt number;   5      begin   6          select count(*) into cnt fro[More]

Find all tables used in a stored procedure

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have a requirement where i have to find all the tables used in a stored procedures from different  databases. Ex: i have a stored procedure where i use few tables from MASTER database and some from STAGE database.When i have written a query to[More]

Using XI - RFC table and an Oracle stored procedure that returns a cursor.

Category:DefaultRelease time:-0001-11-30Views:130

I need to create an interface using XI between an RFC table and an Oracle stored procedure that returns a cursor.  We are on oarcle 9.2 and  SP12. My stored procedure looks something like this: CREATE OR REPLACE PROCEDURE testproc_xi2 (p_recordset1 O[More]

Query to get list of linked server tables referenced inside a stored procedure

Category:DefaultRelease time:-0001-11-30Views:130

Hi, SQL Server 2005 sp4 I have a requirement to get list of linked server tables referenced insider a stored procedure Ex: Databases       DB1          DB2 Tables:            T1             T2                             Use DB1 Go Create proc P1 beg[More]

Creating a table/view or temporary table from within a stored procedure

Category:DefaultRelease time:2015-10-11Views:130

Hi Gurus, Can someone tell me if it is possible to create a table (or view) from within a stored procedure. PROBLEM: In fact I need to create a report at back end (without using oracle developer forms or reports). This report requires creating severa[More]

Passing Tables back from Java Stored Procedures

Category:DefaultRelease time:2015-10-11Views:130

Thomas Kyte has written (in reference to trying to pass an array back from a stored function call): You can do one of two things (and both require the use of objects). You cannot use PLSQL table types as JDBC cannot bind to this type -- we must use O[More]

Pivot table that uses a Stored Procedure parameter and filters the data based on it

Category:DefaultRelease time:-0001-11-30Views:130

Hello, my 1st post.  I am lost.  Please help. I am trying to create an Excel Pivot Table that has data that comes from an ODC but needs to be filtered based on a parameter from a Stored Procedure.  This involves Project Server.  I need to filter the[More]

"table type" result from stored procedure (Oracle 10g) for physical service

Category:DefaultRelease time:-0001-11-30Views:130

Hello, I'm trying to create a physical service based on a stored procedure which returns a table type like this: SQL -- create table create table myTable ( "col1" VARCHAR2(250) not null enable, "filtercol1" VARCHAR2(250), "filterc[More]

Retrieving PL/SQL Table Type returned by stored procedure using Java.

Category:DefaultRelease time:-0001-11-30Views:130

Hi All, I am facing an issue in a Stored Procedure (SP) which returns Table Type, the PL/SQL complex type. Below mentioned is how my stored procedure looks like. CREATE OR REPLACE package sp_test_pkg as TYPE v_value_table_type is table of SW_VALID_CO[More]

Parent-Child Table Update via Single Stored Procedure

Category:DefaultRelease time:-0001-11-30Views:130

Uses: Oracle 9i Server via a Remote Connection with restriction, Oracle 9i client, .NET 2.0+System.Data.Oracle namespace, Delphi+BDE; I have an entry form which consists of a parent and a child table. I'm so keen in knowing whether there is any possi[More]

Using a dynamic table variable in a stored procedure

Category:DefaultRelease time:-0001-11-30Views:130

SQL Server can do this where you declare a table variable and insert / modify / delete rows in that table without it ever being actually ON the database. Can Oracle do this? I know I can do things like pass in a unique user id, create the table in th[More]

Hot
OK, I searched the forums and can't find an answer related to this for mail (found it for itunes, safari, etc). I am sure this is a mundane thing but to my computer simple mind, I am stuck. My husband ran a Safari update. Since then the mail icon bou [More]
I run icloud email along with gmail, yahoo and hotmail all to the mail app. Thus far I've tried deleting and and reinstalling accounts. I've switched gmail to imap. I've changed apple id and passwords. I've restored factory network defaults and perfo [More]
We have Adobe InDesign CS6 installed on 2 different computers and on both computers it frequently crashes saying "cs6 has stopped working" and closes the application. Restarting it ok for a while and then the same thing happens. We have uninstal [More]
Hi, I have follwing dataset "herb with garden" "garden without herb" "herb with zbc" "garden with pqr" Now i want sql query like select * from table where text1 = "herb and garden" This should populate her [More]
I've heard a lot of horror stories with using the Tascam US-1800 audio interface with Lion. How about Snow Leopard? Are there horror stories as well? I recently got myself a Presonus Firestudio Project, but that failed on me the second time I used it [More]
What is the Difference b/w Business System and Business System and whne we use them?Hi, Business System : is a logical entity assigned to a Technical System in a SLD Business Service: is an Abstract entity without having assigned to a Technical Syste [More]
I am doing some mapping of the IO 0REFER_DOC to 0DOC_NUMBER, and I got an information message: ConExits for assigned InfoObjects inconsistent: (KS) - ALPHA(TS) Would this cause any problem at all on the data loads?Hi, If you want to guarantee for no [More]
Hello, We are experiencing a very strange Oracle issue.  We are installing new instances on Oracle 10.2.0.4 on Windows 2008 (2008 new to us).  When we attempt to logon to Oracle sqlplus /nolog, connect /as sysdb, startup, we are greeted by and ora-01 [More]
I updated some apps and since then, the sound stopped working on the IMac and the M-Audio speakers that are connected to it. They both worked before. The only thing I can figure I did was to update apps. Any other ideas for what went wrong?I'm runnin [More]
Hi, I need to add one custom field "WEIGHT" in transaction in vl03n.vl02n,vl01n  on Tab "shipment and loading" near Gross weight. i hav searcehed in sdn but only found how to add fields in custom tab but not in standard tab like " [More]