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.
Author |
Topic |
gavakie
Posting Yak Master
221 Posts |
Posted - 2007-10-01 : 12:21:52
|
I have this chunk of code that you enter your table and it tells you what sp, views etc it is in. But it does not show if that table is used in an sp in a different DB. i was told that sp_msForeachdb may help but i have no idea how use it. Help please here is the code i have belowSET 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 |
|
|
|
|
|
|