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
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

gavakie
Posting Yak Master

221 Posts

Posted - 10/01/2007 :  12:02:20  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 10/01/2007 :  12:09:55  Show Profile  Visit dinakar's Homepage  Reply with Quote
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 - 10/01/2007 :  12:14:43  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 10/01/2007 :  12:23:25  Show Profile  Visit dinakar's Homepage  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 10/01/2007 :  12:27:08  Show Profile  Reply with Quote
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

Slovenia
11751 Posts

Posted - 10/01/2007 :  12:30:43  Show Profile  Visit spirit1's Homepage  Reply with Quote
@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 - 10/01/2007 :  12:31:55  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 10/01/2007 :  12:32:22  Show Profile  Reply with Quote
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 - 10/01/2007 :  12:34:11  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 10/01/2007 :  12:38:38  Show Profile  Reply with Quote
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 - 10/01/2007 :  12:43:40  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
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 - 10/01/2007 :  13:11:56  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
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 - 10/01/2007 :  15:33:28  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
any help anyone please?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37460 Posts

Posted - 10/01/2007 :  15:41:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 10/01/2007 :  15:45:05  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 10/01/2007 :  15:45:48  Show Profile  Reply with Quote
"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

USA
37460 Posts

Posted - 10/01/2007 :  15:46:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 10/01/2007 :  15:47:04  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
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

USA
37460 Posts

Posted - 10/01/2007 :  15:48:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 10/01/2007 :  15:48:17  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 10/01/2007 :  15:50:12  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next 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.15 seconds. Powered By: Snitz Forums 2000