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
I have a Mac mini (2012) and I use it with multiple users. When the iMovie update was posted this week, I updated on user #2. Everything went fine. Then, iMovie wanted to update AGAIN on user #1. So...I started the update. Now, the update has been hu [More]
Hi, I am trying to compile a file into swf using mxmlc in command line.  When it is a very simple mxml, compile is successful. But when the file  contaion the fx name space, I get a compile error like: could not  resolve COMPONENT_NAME to a component [More]
How can I add a Scroll Bar to the JTextArea in the following code, I have tried adding it, as one can see below, however no matter what I seem to change, the scroll bar does not appear. If anyone could help me I'd grealtly appreciate it. public class [More]
Hi All, I am using select stmt on MARA and ZTSDNETPR. In ZTSDNETPR table,  pltyp,konda,MATNR,DATAB,DATBI are indexes in same order as i am using in my select stmt. But it is taking 1 hrs 45 min to take execute. Please give me any suggestion. SELECT  [More]
I am using: SQL Server 2012 (Service Pack 2 - Business Intelligence Edition)  11.0.5058 .Net Version 4.5.1 (378675) Windows Server 2012 R2 Standard Exchange Server 2010 R2 (Hosted on a seperate server) I am getting the following error in the Reportin [More]
Hi all, I am facing a stragnge problem. when go to portal page and enter the Username password instead of landing on home page some junk characters are shown. as below "�����"KÓ0-×ĸc«zÜ2, I+ ÎCš-:KǹM,œ8Ø7}ðëq'Vtà ²‰œ{νǟøìêi'|^Âmòpϯ_îïÀ [More]
I downloaded the update for raw camera 5.7 and followed all the instructions to a tee. Still whenever I edit a raw data image it still displays raw camera 5.5 in the upper left hand corner of the display. Is this correct or is something amiss here/ A [More]
On following page I have a pdf form and it doesnt work and it looks weird(logo changed color, button missing, heading looks weird): http://www.palomat.dk/Files/Billeder/palomat/formular/Bestilling_PALOMATinlinemappe_DK.pdf I've have checked and its n [More]
Hi, I want to create an application that displays a clock. I want to the clock to have a clock face with 2 fingers. I am planning to extend JPanel and use the paint method to draw the clock and both fingers. I am planning on using affineTransform to [More]
To hold my libary material and keep a backup of current projects I have a Drobo Pro, 8 Hdd storage unit. It's not fast but very flexible in the way it uses the hdds. I have it set up to cope with two drive failures. I was backing up over night when o [More]