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
 General SQL Server Forums
 New to SQL Server Programming
 sp_msForeachdb

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 below

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:25:28
Dont post duplicate threads.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90280



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

- Advertisement -