August 22, 2005 at 9:15 am
Hi,
What if I want to find out which stored procedure is using COLUMN "RegProdType" in "SUB_ISSUES" table?
I tried to query syscomments but it only can find objects
by table name or sp name.
But what I need is to find out which SP is using a column name.
Thanks,
Robert
August 22, 2005 at 9:24 am
Sometimes you need to ask a question in order to find an answer yourself.
I just generated script for all SP's and seacrhed for "RegProdType".
Easy.
August 22, 2005 at 9:27 am
Assuming it comes in only one table...
September 6, 2005 at 1:28 pm
NOT MY work, and unfortunately have forgotten who the author is. But thought I would pass it along since no one else seems to remember the SQL ... and to the author I apologize for losing your name, but you know who you are so stand up and take credit for the following:
CREATE PROCEDURE UDP_FindWordsInaSP
@Watchword varchar(50)
AS
SELECT distinct
'type' = case type
when 'FN' then 'Scalar function'
when 'IF' then 'Inlined table-function'
when 'P' then 'Stored procedure'
when 'TF' then 'Table function'
when 'TR' then 'Trigger'
when 'V' then 'View'
end,
o.[name],
watchword = @Watchword
FROM dbo.sysobjects o (NOLOCK)
JOIN dbo.syscomments c (NOLOCK)
ON o.id = c.id
WHERE charindex(lower(@Watchword),lower(text)) <> 0
and o.type in ('FN', 'IF', 'P', 'TF', 'TR', 'V')
and o.name NOT LIKE 'dt%' and o.name NOT LIKe 'sys%'
and o.name NOT LIKE 'UDP_FindWordsInaSP'
ORDER BY type, o.[name]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply