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
Hi Experts! I researched a lot and came to a conclusion that Program SAPFM06P can only be used trigger either a SAP SRIPT Output or a PDF FORM output. It cannot be used to trigger a SMARTFORM output directly as there is no code in it to trigger a SMA [More]
We have had our server running fine for a good 18 months. One day however the server locked up to the state that I was able to move the mouse and move windows however menus etc wouldn't load. I contacted apple care support and sent off my logs but ha [More]
OK, So here's the case on my new iPhone. A friend gifted me this app for my iPhone and it worked fine on my friends phone ( he downloaded it as well from my account and I authorized it temporarily so he could install it) but when I just tried to inst [More]
I am using a brand new 27" iMac and an iOmega eGo 2TB hard drive (http://www.pcmag.com/article2/0,2817,2374133,00.asp). The drive has it's own power source and is connected to my computer via USB. I'm trying to transfer files from the hard drive to m [More]
I need help, I'm trying to figure out how to get songs I have in my shared folder to a new folder I have created, I tried dragging it into the folder but doesn't work, how do I go about doing this?I need help, I'm trying to figure out how to get song [More]
Even though they are saved as AI. On other computers it opens properly. The few files I can open, I can't select using the selection tool because of a clipping mask I cannot get rid of.field, You may try the following (you may have tried/done some of [More]
hi, in our implementation we plan to use ERP sales org for all sales documents in CRM hence we plan to map org structures between CRM and ERP. we also have a need to leverage Employee maintenance done in HR. hence we plan to download employees from H [More]
Hey guys I could really use some help with this. How can I get a keynote presentation to automatically repeat it self after it ends...in other words I need it to loop indefinitely. Thanks, ChristianSure - in the Inspector, go to the 'Document' sectio [More]
Dear Freinds,                   I  have created a dynamic selections view form Se80 > Editobject>Selection view > Created by adding the required tables pa0002,pa006,pa0009 . But the transport request we have saved it locally now i want to create [More]
Post Author: sgsampey CA Forum: Data Integration A newbie question: I have a source table called Events with (significant) fields that look like: EventIDEventNameBeginDateEndDateDaysOfWeekModifyDate These data ranges can be quite long, and there's an [More]