December 17, 2007 at 1:01 pm
I want to search a Field for a condition and only get back the portion of the Field that meets the condition.
For example, I am searching sysobjects.name and syscomments.text to find the names of any nested stored procedures. I just want the name of the stored procedure and the names (if any) of the nested stored procedures. The Field I am searching is syscomments.text. The condition is
'%rpt_%'.
I can find the text that meets the condition by simpy doing WHERE text LIKE '%rpt_%' but that will retrieve the entire text. Like the query below:
SELECTso.[name], sc.text
FROM dbo.sysobjects so
LEFT OUTER JOIN dbo.syscomments sc
ON sc.[id] = so.[id]
WHEREso.type IN ('FN', 'IF', 'P')
ANDsc.text LIKE '%rpt_%'
will retrieve all the names that string but I do not want the entire text returned. i just want the name of the nested stored procedure - the portion of text that met the search condition.
How can I properly do this?
Also, the name of the stored procedure being searched will be contained in text so I need to avoid that name in the result set - while still allowing the other names, if any, to be returned.
Perhaps I am going about this wrong. Ideal result set would be the name of the stored procedure/function being searched and the names of any nested stored procedures/functions.
Please help Obi-Wan. You're my only hope.
December 17, 2007 at 1:12 pm
You are really doing parsing at this point, which isn't a strong point of T-SQL.
You can use charindex/patindex in the returned field (Cast to varchar) and then find the _rpt. Once you have that position, you need to find the space before and after this.
I'd actually run this as a loop against a table that I'd sucked out the data from syscomments into. Then I'd run it looking for the _rpt, find the space right before that, strip out all data before that with a replace. replace with all the data after the space.
Then I'd find the next space, suck out the data up to that into another table, which gives you the dependent procedure.
Repeat this until you don't find _rpt in the column anymore. If you write it generally enough, you can do all procedures at once.
Or you could just buy something like SQLCompare or Red Gate's Dependency Tracker (I work for Red GAte). It finds the dependent procedures and builds a tree and you can't write the algorithm for anything close to 4times the cost of the product.
December 17, 2007 at 1:18 pm
Thanks! I'll try doing the search you suggested as well as looking into Red Gate's Dependency Tracker.
December 17, 2007 at 2:53 pm
Or you may find dependencies from system table sysdepends.
_____________
Code for TallyGenerator
December 17, 2007 at 3:32 pm
The information contained in sysdepends is needed to compare the results found in the requested query. A record will not be added to sysdepends if a procedure makes a call to another procedure that has not yet been created. Also, if a nested procedure is dropped, the calling procedure will no longer appear in sysdepends; however, the nested procedure (although non-existent) will still appear in syscomments.text. So by comparing the objects found in syscomments.text against the dependent objects (or lack of) in sysdepends, I can find the procedures, if any, that are making calls to non-existent procedures.
As far as why would anyone drop a procedure that is being called by another or any other scenario you can think of is another story.
December 18, 2007 at 9:16 am
Here's what I came up with. Thanks Steve!
DECLARE@crlf char(2),
@counter int
SELECT@crlf = CHAR(13) + CHAR(10),
@counter = 10 -- arbitrary number
-- Insert records in temp table
-- Remove hidden characters to get an accurate CHARINDEX
IF OBJECT_ID('tempdb..#Pool') IS NOT NULL DROP TABLE #Pool
SELECTIDENTITY (int, 1,1 ) AS PID,
so.[Name],
RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(sc.text, @crlf, ' '), CHAR(10), ' '), CHAR(9), ' '))) [Text],
so.Type
INTO#Pool
FROM dbo.sysobjects so
JOIN dbo.syscomments sc
ON sc.[id] = so.[id]
WHEREso.type IN ('FN', 'IF', 'P')
ANDCHARINDEX('rpt_' , sc.text) > 0
WHILE@counter > 0
BEGIN
-- Update column to start where name of the next procedure appears
UPDATE #Pool
SET[Text] = RIGHT([Text], LEN([Text]) - CHARINDEX('rpt_', [Text])+1)
-- Move name of procedure to end
UPDATE#Pool
SET[Text] = LTRIM(RTRIM(RIGHT([Text], LEN([Text]) - CHARINDEX(' ', [Text])) + ' ' + LEFT([Text], CHARINDEX(' ', [Text]))))
SET@counter = @counter - 1
END
-- Cleanup
UPDATE#Pool
SET[Text] = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE([Text], '''', ' '),'(', ' '), ')', ' '), [Name], '')))
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply