August 30, 2006 at 9:10 am
Can this be done?
Does anyone know of a tool or command I could use that can find all occurrences of a particular string?
I want to look in all of my procs and triggers.
For example, I have an event_code column on some tables. I reference that column in my procs and triggers. In my scripts, I may have something like @event_code = 'DDENT'. I want to search through all procs and triggers for 'DDENT'.
I have all of my procs & triggers in .sql files and I could search using windows explorer for the DDENT string but I don't trust the results.
Thank you
August 30, 2006 at 9:12 am
You can script all database objects into one file. Then simply search through the file for all objects. Works with the MMC plugin for 7/2000.
August 30, 2006 at 9:17 am
"You can script all database objects into one file." How do I do this?
What is MMC plugin for 7/2000 and how/where can I get it?
August 30, 2006 at 9:17 am
select
distinct object_name(id) from syscomments where text like '%DDENT%'
This will return the object names that contain the string "DDENT".
Hope that helps,
August 30, 2006 at 9:19 am
Karl -
That worked great - thanks!
August 30, 2006 at 10:15 am
The MMC is the enterprise consule. Basically right click on the database and script to file. It should let you select all objects.
The 2nd option appears to work better but I was not familiar with it.
good luck
daralick
August 31, 2006 at 8:12 am
SysComments is a system table that holds all the scripts of all the objects of the database (sps, triggers, views...). So this technic is really fast an effective to find out stuff like that.
August 31, 2006 at 8:44 am
The [text] column in syscomments is nvarchar(4000). If the length of the text for the object is greater that 4000, it will be split into two or more rows. Be advised that when searching syscomments with LIKE, there is a slight possibility that the string you are searching for could be split and cross over into the next row. In that case, LIKE will not find a match. Granted, it's a relatively remote possibility, but it could happen.
August 31, 2006 at 8:51 am
Ya I had to code for that once... Since I had a application I did 2 searchs... The first search was only considering objects with one entry, then the second search downloaded the code in the application, the app concatenated all parts into a single script and then the search was ran. I'm sure a similar process could be done in a dts but I don't have the time to code it now.
August 31, 2006 at 8:58 am
Good point.
You can do this entirely in T-SQL by concatenating the [text] field into a seperate table that has a field with a text datatype (thus allowing more than 8000 characters). You can then safely perform a like comparison.
It's a bit awkward though and given how remote the chances of this occurring are, unless it's an essential part of an app you're building, you might as well just script the procs and do a Find/Replace search. Or accept the risk.
September 1, 2006 at 1:43 am
I can see that you've got the problem solved but another suggestion would be the text editor TextPad (http://www.textpad.com/). It has a very good "Find In Files" facility and is a reasonable text editor.
September 1, 2006 at 6:36 am
That's a nice idea, but it doesn't solve the problem that first have to script all the procs to a file before search... that's why I preffer a on server processing. That way you always get the real result in no time at all (once you've written the simple code once).
September 1, 2006 at 8:29 am
Here's some quick and dirty code that will just print out the object name. Sorry, but I used a cursor. Since I'm assuming this is for an ad-hoc administration task, I'm don't mind using the cursor. Also, I left some debugging code in (it is commented out).
To run the stored procedure after it is created, don't include the '%' around the search string, as they are appended in the SPROC:
-- EXEC uspFindStringInObjectSourceCode 'string to search for'
Here is the stored procedure code:
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'uspFindStringInObjectSourceCode'
AND type = 'P')
DROP PROCEDURE uspFindStringInObjectSourceCode
GO
CREATE PROCEDURE uspFindStringInObjectSourceCode
(
@search varchar(1000)
)
AS
-- For each object (stored procedure, function, or trigger),
-- concatenate the text from all sysmessages rows into a
-- single text column in a temp table.
SET NOCOUNT ON
CREATE TABLE #text
(
id int
, newtext ntext
)
DECLARE @objId int
, @colId smallint
, @ntext nvarchar(4000)
DECLARE @curObj int
, @tPtr varbinary(16)
DECLARE cObjText CURSOR READ_ONLY FOR
SELECT c.[id]
, c.colid
, c.[text]
FROM syscomments c
JOIN sysobjects o
ON c.id = o.id
WHERE o.xtype IN ('P', 'FN', 'TF', 'IF', 'TR', 'V')
ORDER BY c.[id], c.colid
OPEN cObjText
-- Get first row from cursor, set current obj to -1 to force start of new object
FETCH cObjText INTO @objId, @colId, @ntext
SET @curObj = -1
--DECLARE @newObjName varchar(100)
-- , @prevColId smallint
WHILE @@FETCH_STATUS = 0
BEGIN
IF @curOBj <> @objId --start a new object
BEGIN
SET @curOBj = @objId
-- IF @newObjName IS NOT NULL
-- PRINT Convert(varchar(8), @prevColId) + ' row(s) processed'
-- SELECT @newObjName = name
-- FROM sysobjects
-- WHERE id = @objId
-- PRINT 'New object: ' + @newObjName
INSERT #text (id, newtext) VALUES (@objId, @ntext)
SELECT @tPtr = TextPtr(newtext) FROM #text WHERE [id] = @objId
END
ELSE --continue with current object
BEGIN
-- PRINT 'Appending row ' + Convert(varchar(8), @colId)
UPDATETEXT #text.newtext @tPtr NULL NULL @ntext
END
--SET @prevColId = @ColId
-- get next row from cursor
FETCH cObjText INTO @objId, @colId, @ntext
END --WHILE
CLOSE cObjText
DEALLOCATE cObjText
--SELECT id
-- , Datalength(newtext) AS numBytes
-- , Datalength(newtext) / 2 AS numChars
-- , Substring(newtext,1,200) AS BeginningOfText
-- FROM #text
SELECT [id]
, Datalength(newtext) AS numBytes
, Datalength(newtext) / 2 AS numChars
, (SELECT Min(name) FROM sysobjects WHERE id = t.id) AS ObjName
FROM #text t
WHERE newtext LIKE '%' + @search + '%'
ORDER BY 4
DROP TABLE #text
SET NOCOUNT OFF
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply