Home » SQL & PL/SQL » SQL & PL/SQL » Problem: Find all table names in db and then find the list of SP used by this table (SQL Server)
Problem: Find all table names in db and then find the list of SP used by this table [message #326625] Thu, 12 June 2008 02:20 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I have 3 database say
db1
db2
db3

I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server).

How can I do that? Need your help!


Regards,
Oli
Re: Problem: Find all table names in db and then find the list of SP used by this table [message #326630 is a reply to message #326625] Thu, 12 June 2008 02:24 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Quote:
Stored Procedure using each table.(SQL Server).


This is not a SQL Server forum
Re: Problem: Find all table names in db and then find the list of SP used by this table [message #326631 is a reply to message #326630] Thu, 12 June 2008 02:25 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
How I would do that in Oracle?
Re: Problem: Find all table names in db and then find the list of SP used by this table [message #326632 is a reply to message #326625] Thu, 12 June 2008 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OraFAQ = Oracle FAQ's in the top banner.

SQL & PL/SQL forum: Oracle SQL and PL/SQL topics including queries, DML and DDL statements, stored procedures, functions, packages and triggers.

I don't think it is misleading. SQL Server is mentioned nowhere.

Regards
Michel

[Updated on: Thu, 12 June 2008 02:27]

Report message to a moderator

Re: Problem: Find all table names in db and then find the list of SP used by this table [message #326633 is a reply to message #326631] Thu, 12 June 2008 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Olivia wrote on Thu, 12 June 2008 09:25
How I would do that in Oracle?

Query dba_/all_/user_dependencies

Regards
Michel

Re: Problem: Find all table names in db and then find the list of SP used by this table(oracle) [message #326636 is a reply to message #326632] Thu, 12 June 2008 02:30 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Yep its nowhere..[Smile]
But if the same problem is in Oracle?
Re: Problem: Find all table names in db and then find the list of SP used by this table(oracle) [message #326643 is a reply to message #326636] Thu, 12 June 2008 02:43 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I am not being able to figure out abt how I would find that. Need your help!
Re: Problem: Find all table names in db and then find the list of SP used by this table [message #326644 is a reply to message #326625] Thu, 12 June 2008 02:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If I remember correctly, what SQL Server refers to as Databases as Schemas in Oracle.

You can get a list of the tables in those schemas with this query:
SELECT owner,table_name
FROM   all_tables
WHERE  owner in ('DB1','DB2','DB3');


To get a list of procedures using these tables, a query like this will suffice:
select name,type,owner,referenced_name,referenced_owner 
from   all_dependencies
where  referenced_owner in ('DB1','DB2','DB3')
and    type in ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION');
Re: Problem: Find all table names in db and then find the list of SP used by this table [message #326649 is a reply to message #326644] Thu, 12 June 2008 02:57 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
@JRowbottom

Thank you!
Previous Topic: help in trigger craetion.
Next Topic: can we use update and select command in single query.
Goto Forum:
  


Current Time: Sun Dec 04 00:42:13 CST 2016

Total time taken to generate the page: 0.24558 seconds