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 ONDECLARE @string VARCHAR(1000)--SET @string = 'dbo.RetailSales_ByStore_ByCustomer_ByDay' --> This is your search criteriaSET @string = 'dbo.Store' --> This is your search criteriaDECLARE @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) )INSERTINTO @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, 1SELECT 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, DateCreatedFROM @ResultsORDER 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/ |
|
|
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 |
|
|
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+SearchDinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
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 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-01 : 12:30:43
|
@gavakie:please don't cross post._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
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. |
|
|
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 ... |
|
|
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. |
|
|
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 |
|
|
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. |
|
|
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? |
|
|
gavakie
Posting Yak Master
221 Posts |
Posted - 2007-10-01 : 15:33:28
|
any help anyone please? |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
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 errorServer: Msg 201, Level 16, State 4, Procedure sp_depends, Line 0Procedure '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 |
|
|
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! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-01 : 15:46:59
|
Agreed. Does sp_depends even show cross-database objects?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
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. |
|
|
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 errorServer: Msg 201, Level 16, State 4, Procedure sp_depends, Line 0Procedure '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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
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. |
|
|
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 |
|
|
Previous Page&nsp;
Next Page
|