| Author |
 Topic  |
|
gavakie
Posting Yak Master
218 Posts |
 Posted - 10/01/2007 : 15:52:10
|
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. |
 |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/01/2007 : 15:53:08
|
OK, but WHY do you need a list of tables-used?
Just Documentation, or some other purpose?
Kristen |
|
|
|
gavakie
Posting Yak Master
218 Posts |
Posted - 10/01/2007 : 15:55:39
|
| 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. |
|
|
|
gavakie
Posting Yak Master
218 Posts |
Posted - 10/01/2007 : 16:07:42
|
| sp_MSForEachDB ' ? exec sp_depends 'Mas.dbo.style'' so this is what im trying no and still getting a syntax error as MAS |
|
|
|
Van
Constraint Violating Yak Guru
456 Posts |
Posted - 10/01/2007 : 16:10:49
|
| 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. |
|
|
|
Van
Constraint Violating Yak Guru
456 Posts |
Posted - 10/01/2007 : 16:13:51
|
| 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. |
|
|
|
gavakie
Posting Yak Master
218 Posts |
Posted - 10/01/2007 : 16:15:29
|
| I am so new to this I wouldnt even know where to start with that. |
|
|
|
dinakar
Flowing Fount of Yak Knowledge
USA
2507 Posts |
|
|
gavakie
Posting Yak Master
218 Posts |
Posted - 10/01/2007 : 17:05:56
|
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'.
|
|
|
|
rmiao
Flowing Fount of Yak Knowledge
USA
7266 Posts |
Posted - 10/01/2007 : 22:31:14
|
| Looks like there is object named style in three dbs, did you double check there? |
|
|
|
Topic |
|
|
|
Searching in other Db's
