Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Searching in other Db's

Author  Topic 

gavakie
Posting Yak Master

221 Posts

Posted - 2007-10-01 : 12:02:20
So I have the code listed below and it searches the table and tells me the name of the Procedure or View or trigger its used in . But it only tells me Procedure or View or trigger of that table in the database that the table is in. So if I have that table being used in a SP in another DB how can I search for that.

SET nocount ON

DECLARE @string VARCHAR(1000)

--SET @string = 'dbo.RetailSales_ByStore_ByCustomer_ByDay' --> This is your search criteria
SET @string = 'dbo.Store' --> This is your search criteria

DECLARE
@errnum INT,
@errors CHAR(1),
@rowcnt INT,
@output VARCHAR(255)

SELECT
@errnum = 0,
@errors = 'N',
@rowcnt = 0,
@output = ''

DECLARE @Results TABLE (
Name VARCHAR(55),
Type VARCHAR(12),
DateCreated DATETIME,
ProcLine VARCHAR(4000)
)


INSERT
INTO @Results
SELECT DISTINCT
'Name' = CONVERT(VARCHAR(55), SO.name),
'Type' = SO.type,
crdate,
''
FROM
sysobjects SO
JOIN syscomments SC
ON SC.id = SO.id
WHERE
SC.text LIKE '%' + @string + '%'
UNION
SELECT DISTINCT
'Name' = CONVERT(VARCHAR(55), SO.name),
'Type' = SO.type,
crdate,
''
FROM
sysobjects SO
WHERE
SO.name LIKE '%' + @string + '%'
UNION
SELECT DISTINCT
'Name' = CONVERT(VARCHAR(55), SO.name),
'Type' = SO.type,
crdate,
''
FROM
sysobjects SO
JOIN syscolumns SC
ON SC.id = SO.ID
WHERE
SC.name LIKE '%' + @string + '%'
ORDER BY
2,
1

SELECT
Name,
'Type' = CASE (Type)
WHEN 'P' THEN 'Procedure'
WHEN 'TR' THEN 'Trigger'
WHEN 'X' THEN 'Xtended Proc'
WHEN 'U' THEN 'Table'
WHEN 'C' THEN 'Check Constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'Foreign Key'
WHEN 'K' THEN 'Primary Key'
WHEN 'V' THEN 'View'
ELSE Type
END,
DateCreated
FROM
@Results
ORDER BY
2,
1

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-01 : 12:09:55
check out sp_msForeachdb.

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

gavakie
Posting Yak Master

221 Posts

Posted - 2007-10-01 : 12:14:43
I cant find that in Help could you give me an example of how it runs? Or how the syntax works?
i.e. exec sp_msForeachdb sproc
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-01 : 12:23:25
http://www.google.com/search?hl=en&q=sp_msForeachdb&btnG=Google+Search

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

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 12:27:08
Syntax is:

exec sp_MSforeachdb 'PRINT ''This is the database >?<'' '

where "?" is replaced by the Database name, and it is executed for every database.

Note that this command is UNdocumented.

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-01 : 12:30:43
@gavakie:
please don't cross post.


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 2007-10-01 : 12:31:55
I meant to post in the new forums first since Im new to SQL I apologize. I though in the new forums it might be dumbed down for me.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 12:32:22
I had to move my answer across when I discovered that there was a second copy of that thread, not the best use of the time I have available to help here ...
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 2007-10-01 : 12:34:11
Sorry I apologize again. So do I run the code with my code that i posted above. what im trying to do is find away to run a piece of code that will tell me all the tables that are being run in my SP, and views but like above when I run it if there is a table from a different DB it wont show.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 12:38:38
I don't see how running your code on each database will help, as that will only find Sprocs on other database, rather than sprocs on YOUR database that REFERENCE tables on other database.

Assuming that I have understood your question properly.

We store all our Sprocs and Triggers etc. in individual files in a Version Control system, and if I needed to solve this particular problem I would write something to parse all those files documenting the tables etc. references.

Kristen
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 2007-10-01 : 12:43:40
I have a DB called reporting for instance. In one of the SP in that DB are tables obviousally. Well say that sp has tables running on it from DB Stores, and One from DB Sales so If i try to run My code Aboce or sp_depends it wont show the tables from DB Stores or DB Sales because there not in the Reporting DB. So Im trying to find Code that will show all tables being used in that SP.
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 2007-10-01 : 13:11:56
sp_MSforeachdb and sp_depends what would the syntax be to use these two together?
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 2007-10-01 : 15:33:28
any help anyone please?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-01 : 15:41:26
Googling for the syntax of sp_MSforeachdb didn't help you at all? Could you post what you tried and what errors you got?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 2007-10-01 : 15:45:05
I have tried everythin I havent actually found an example of using the two together. everything Im findding is showwing dbcc

sp_MSForEachDB 'use ? exec sp_depends' this what i have tried and it get ther error

Server: Msg 201, Level 16, State 4, Procedure sp_depends, Line 0
Procedure 'sp_depends' expects parameter '@objname', which was not supplied.

so when i try to fill the object name in with say one of my tables it get an error on the table name


Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 15:45:48
"Googling for the syntax of sp_MSforeachdb didn't help you at all?"

FWIW I provided an example higher up the thread ...

... but I still don't think this is going to get the OP the answer the the ACTUAL question!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-01 : 15:46:59
Agreed. Does sp_depends even show cross-database objects?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 2007-10-01 : 15:47:04
Googling didnt work I keep getting the same few examples over and over. Even the same few threads on different sites over and over.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-01 : 15:48:11
quote:
Originally posted by gavakie

I have tried everythin I havent actually found an example of using the two together. everything Im findding is showwing dbcc

sp_MSForEachDB 'use ? exec sp_depends' this what i have tried and it get ther error

Server: Msg 201, Level 16, State 4, Procedure sp_depends, Line 0
Procedure 'sp_depends' expects parameter '@objname', which was not supplied.

so when i try to fill the object name in with say one of my tables it get an error on the table name






Use DatabaseName.dbo.ObjectName instead of what you have. You don't need the USE statements to access objects in other databases, just the three part naming convention.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 2007-10-01 : 15:48:17
I dont know thats why I tried to repost in new threads Im only a couple weeks new to SQL. Im just trying to find answers.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 15:50:12
I've asked this before, but I'll ask again.

Why do you need to know this?

What's the root-problem you are trying to solve?

There might be a smarter way to get an answer to the core problem, rather than this particular proposed-solution to the problem.

Kristen
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -