SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Searching in other Db's
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

gavakie
Posting Yak Master

221 Posts

Posted - 10/01/2007 :  15:52:10  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
I am trying to get a list of all the tables that are used in my stored procedure and views. The problem is Sp_depends doesnt return all the tables because they may be joined with another db. So I was told by another poster earlier to try this type of method
sp_MSForEachDB ' ? exec sp_depends Mas.dbo.style' which isnt working either.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/01/2007 :  15:53:08  Show Profile  Reply with Quote
OK, but WHY do you need a list of tables-used?

Just Documentation, or some other purpose?

Kristen
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 10/01/2007 :  15:55:39  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
Yes documentation. our IT department made changes to our servers and Db's without letting us know so we have putt all our reports running on just code into SP's and views. so instead of doing everything by hand in excel we were hoping there was an easier way as we go and fix the couple thousand reports were working on.
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 10/01/2007 :  16:07:42  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
sp_MSForEachDB ' ? exec sp_depends 'Mas.dbo.style'' so this is what im trying no and still getting a syntax error as MAS
Go to Top of Page

Van
Constraint Violating Yak Guru

458 Posts

Posted - 10/01/2007 :  16:10:49  Show Profile  Reply with Quote
I don't think you are going to have any luck whatsoever with sp_depends if you are looking for usage info from other dbs. It's only for the current db that the object is in. You are probably going to have to do it all manually.
Go to Top of Page

Van
Constraint Violating Yak Guru

458 Posts

Posted - 10/01/2007 :  16:13:51  Show Profile  Reply with Quote
You could write some sort of a 'findtext' SP and use it to pass in a table name and see if that table is used in any SPs, views, functions, ect. That would be doing it backwards from knowing the SP and finding what tables it uses though.
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 10/01/2007 :  16:15:29  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
I am so new to this I wouldnt even know where to start with that.
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 10/01/2007 :  16:39:44  Show Profile  Visit dinakar's Homepage  Reply with Quote
You might find some function here: http://www.sqlservercentral.com/Scripts/ perhaps not directly usable but something you can work off of.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 10/01/2007 :  17:05:56  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
sp_msforeachdb @command1= 'USE ? exec sp_Depends "style"' so im here now and im kind of getting it to work but still getting errors. this is my error code if you look at the botoom its telling me
In the current database, the specified object is referenced by the following:



Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'master'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'tempdb'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'msdb'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'GoldMine_Sales_and_Marketing'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'CustomerConcerns'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'Aggregate'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'OLD_BUDGET'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'CreditCardData'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'DELSA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'COZUM'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'DelSolNet'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'DelSolNet2'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'DYNAMICS'.
Object does not reference any object, and no objects reference it.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'HUNTB'.
In the current database, the specified object is referenced by the following:

Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'MASDEV'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'MASDEVSmall'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'OLDOnlineOrdering'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'OnlineRMA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'OnlineStore'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'DELSO'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'Reporting'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'SSE'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'STTHO'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'TMPLT'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'WHAVL'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'QSCHQ'.
In the current database, the specified object is referenced by the following:

In the current database, the specified object is referenced by the following:

In the current database, the specified object is referenced by the following:

Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'SOLKD_DATA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'SOLKD_NET'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'ALOHA_DATA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'style' does not exist in database 'ALOHA_NET'.
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 10/01/2007 :  22:31:14  Show Profile  Reply with Quote
Looks like there is object named style in three dbs, did you double check there?
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000