|
gavakie
Posting Yak Master
220 Posts |
 Posted - 10/01/2007 : 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
|
|
sp_msForeachdb
Topic Locked
