Searching Stored Procedures for Table Names
-
Thanks!
-
HTH
. -
Search this site (check the scripts box) for FindStringInCode or FindStringInCode2.
May have flaws I don't know about but works a treat for me.
-
Can you post the exact link. The nly 2 ones I found don't do any better than what has been discussed here already... aside from over-validation.
-
I have tried three of the suggestions against my database:
declare @find varchar(1000),
@sp_prefix varchar(1000)
set @find = 'unitxref'-- enter search string here
set @sp_prefix = isnull(@sp_prefix,'') + '%'
set @find = '%' + @find + '%'select distinct o.name
from sysobjects o
join syscomments c1
on o.id = c1.id
left join syscomments c2
on c2.id = o.id
and c2.colid = c1.colid + 1
where o.xtype in ('P','TR')
and o.name like @sp_prefix
and c1.text + isnull(c2.text,'') like @find
Select distinct O.Name
from
dbo.SysObjects O
inner join dbo.SysComments C ON o.id = C.ID and O.XType = 'P'
AND OBJECTPROPERTY(O.id, 'IsMsShipped') = 0
AND C.Text like '%unitxref%'
ORDER BY O.Name
EXEC sp_depends @objname = N'unitxref'
The first two returned the same results. The third one returned not only the stored procedures but all objects but of the stored procedures, it missed one of them.
To Colin I say, I am sorry that this has been posted before. I am new to really using this site this way and did not really know how to research it on my own. Based on the responses it seems to me that it is not a straight forward answer either.
In general, I appreciate all of your input.
-
Let me make this real simple.
Make a vbX app. Then run this statement :
SELECT OBJECT_NAME(ID) AS ObjectName, Colid, Text FROM dbo.SysComments AND OBJECTPROPERTY(id, 'IsMsShipped') = 0 ORDER BY Object_ObjectName, Colid
Then loop throught the result set and concatenate the text rows as long as the ObjectName remains unchanged, then run an instring function to find the string you are looking for. This ain't the fasted version of the code but it'll work 100% of the time.
-
Actually many reasons. Here's the main one :
DECLARE @t1 AS NVARCHAR(4000)
DECLARE @t2 AS NVARCHAR(4000)
SET @t1 = REPLICATE('a', 4000)
SET @t2 = REPLICATE('b', 4000)
SELECT 0 WHERE (@t1 + ISNULL(@t2, '')) like '%b%'
--returns nothing, the 2nd variable is not concatenated to the first one because of the 8000 limit
UNION ALL
SELECT 1 WHERE RIGHT(@t1, 1) + LEFT(ISNULL(@t2, ''), 1) like '%ab%'
SELECT DATALENGTH(@t1), DATALENGTH(@t2), DATALENGTH(@t1 + @t2)
--AS you can see 8000 + 8000 now equals 8000 🙂
However you gave me the solution on how to finally have a single, sql server only solution as opposed to a client side solution.
I'll give you this unfinished version at the moment. I'll most likely post the final version in the script section later this week.
USE master
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'sp_SearchSyscomments' AND XType = 'P' AND USER_NAME(uid) = 'dbo')
DROP PROCEDURE dbo.sp_SearchSyscomments
GO
CREATE PROCEDURE dbo.sp_SearchSyscomments @Find AS VARCHAR(1000)
AS
SET NOCOUNT ON
DECLARE @Length AS INT
SET @Length = DATALENGTH(@Find)
SET @Find = '%' + @Find + '%'
SELECT
DISTINCT
O.Name
, O.XType
FROM dbo.SysObjects O
INNER JOIN dbo.SysComments C1
ON O.id = C1.id
LEFT OUTER JOIN dbo.SysComments C2
ON C2.id = O.id
AND C2.Colid = C1.Colid + 1
WHERE
1 = CASE
WHEN C1.Text LIKE @Find THEN 1
WHEN C2.Text IS NOT NULL AND
RIGHT(C1.Text, @Length)
+ LEFT(C2.Text, @Length) LIKE @Find THEN 1
ELSE 0
END
AND OBJECTPROPERTY(O.id, 'IsMsShipped') = 0
ORDER BY
O.XType
, O.Name
SET NOCOUNT OFF
GO
--EXEC dbo.sp_SearchSyscomments 'SysComments'
EXEC dbo.sp_SearchSyscomments 'PRINT ''Backup databa'
EXEC dbo.sp_SearchSyscomments 'OFF'
--SELECT LEFT(Text, 10), Right(Text, 10), LEN(Text) FROM dbo.SysComments WHERE id = OBJECT_ID('sp_CreateAuditDB')
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply