January 22, 2009 at 7:29 am
I use the follwoing script to find any instances of any string in sp's table names etc - Thought someone might have a use for it. If you like it use if you don't don't 🙂
I guess there's probably a better way to do this so any comments accepted and I know that it's not 'best' practice to use system tables like I do.
CREATE PROCEDURE [dbo].[sp_Find] ( @SearchFor AS VARCHAR(200),
@SearchFor2 AS VARCHAR(200) = ' ',
@MaxGap AS INTEGER = 9999,
@Type AS VARCHAR(2) = '' )
AS -- Andy Reilly
-- Find text in a stored procdure or table / field and return list of all of these along with SP_HelpText on the SP's
SET NOCOUNT ON
SET @MaxGap = @MaxGap + LEN(@SearchFor2)
CREATE TABLE #SPs ( ID INTEGER )
CREATE TABLE #Triggers ( ID INTEGER )
CREATE TABLE #Functions ( ID INTEGER )
CREATE TABLE #WithoutComments ( ID INTEGER,
ColID INTEGER,
Text NVARCHAR(4000) )
DECLARE @Added AS INTEGER
DECLARE @CommentStartPos AS INTEGER
DECLARE @CommentEndPos AS INTEGER
DECLARE @SearchStart AS INTEGER
DECLARE @ID AS INTEGER
DECLARE @ColID AS INTEGER
DECLARE @MyText AS NVARCHAR(4000)
DECLARE @MyText2 AS NVARCHAR(4000)
-- Charindex won't work with wildcards so strip them out to use with Charindex
DECLARE @WildCardPosStart AS INTEGER
DECLARE @WildCardPosEnd AS INTEGER
DECLARE @SearchForCharIndex AS VARCHAR(200)
SELECT @SearchForCharIndex = @SearchFor
MyLoop:
IF CHARINDEX('[', @SearchForCharIndex, 0) > 0
BEGIN
SELECT @WildCardPosStart = CHARINDEX('[', @SearchForCharIndex, 0)
SELECT @WildCardPosEnd = CHARINDEX(']', @SearchForCharIndex, 0)
IF @WildCardPosStart > 1
SELECT @SearchForCharIndex = LEFT(@SearchForCharIndex,
@WildCardPosStart - 1)
+ SUBSTRING(@SearchForCharIndex, @WildCardPosEnd + 1, 200)
ELSE
IF @WildCardPosStart = 1
SELECT @SearchForCharIndex = SUBSTRING(@SearchForCharIndex,
@WildCardPosEnd + 1,
200)
GOTO MyLoop
END
IF @Type = ''
OR @Type = 'P'
OR @Type = 'TR'
OR @Type = 'FN'
OR @Type = 'TF'
BEGIN
SELECT DISTINCT
ID,
CHARINDEX(@SearchForCharIndex, Text) AS Pos,
ColID
INTO #IDs2
FROM SysComments
WHERE Text LIKE '%' + @SearchFor + '%'
INSERT #WithoutComments ( ID, ColID, Text )
SELECT I.ID,
I.ColID,
Text
FROM #IDs2 AS I
JOIN SysComments AS C ON I.ID = C.ID
AND I.ColID = C.ColID
DECLARE CommentCursor CURSOR
FOR SELECT ID,
ColID,
Text
FROM #WithoutComments
OPEN CommentCursor
FETCH NEXT FROM CommentCursor INTO @ID, @ColID, @MyText
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SELECT @CommentStartPos = 1
SELECT @CommentEndPos = 0
SELECT @SearchStart = 0
SELECT @MyText2 = ''
WHILE @CommentStartPos > 0
BEGIN
SELECT @CommentStartPos = CHARINDEX('/*',
SUBSTRING(@MyText, @SearchStart, 4000))
SELECT @CommentEndPos = CHARINDEX('*/',
SUBSTRING(@MyText, @SearchStart + @CommentStartPos, 4000))
+ 2
SELECT @MyText2 = @MyText2 + SUBSTRING(@MyText, @SearchStart, ABS(@CommentStartPos - 1))
IF @CommentStartPos > 0
SELECT @SearchStart = @SearchStart
+ @CommentStartPos + @CommentEndPos
END
SELECT @MyText2 = @MyText2 + CHAR(13) + CHAR(10)
+ SUBSTRING(@MyText, @SearchStart, 4000)
SELECT @CommentStartPos = 1
SELECT @CommentEndPos = 0
SELECT @SearchStart = 0
SELECT @MyText = @MyText2
SELECT @MyText2 = ''
WHILE @CommentStartPos > 0
BEGIN
SELECT @CommentStartPos = CHARINDEX('--',
SUBSTRING(@MyText, @SearchStart, 4000))
SELECT @CommentEndPos = CHARINDEX(CHAR(10),
SUBSTRING(@MyText, @SearchStart + @CommentStartPos, 4000))
IF @CommentStartPos > 0
BEGIN
SELECT @MyText2 = @MyText2
+ SUBSTRING(@MyText, @SearchStart,
ABS(@CommentStartPos - 1))
SELECT @SearchStart = @SearchStart
+ @CommentStartPos + @CommentEndPos
END
END
SELECT @MyText2 = @MyText2 + CHAR(13) + CHAR(10)
+ SUBSTRING(@MyText, @SearchStart, 4000)
UPDATE #WithoutComments
SET Text = @MyText2
WHERE ID = @ID
AND ColID = @ColID
FETCH NEXT FROM CommentCursor INTO @ID, @ColID, @MyText
END
CLOSE CommentCursor
DEALLOCATE CommentCursor
SELECT DISTINCT
ID,
CHARINDEX(@SearchForCharIndex, Text) AS Pos,
ColID
INTO #IDs
FROM #WithoutComments
WHERE Text LIKE '%' + @SearchFor + '%'
/*SELECT Name
FROM SysObjects AS S JOIN
#IDs AS E ON S.ID = E.ID*/
END
IF @Type = ''
OR @Type = 'P'
OR @Type = 'TR'
OR @Type = 'FN'
OR @Type = 'TF'
BEGIN
SET @Added = 1
WHILE @Added > 0
BEGIN
INSERT #IDs
SELECT W.ID,
CHARINDEX(@SearchForCharIndex, Text, I.Pos + 1),
W.ColID
FROM #WithoutComments AS W
JOIN #IDs AS I ON W.ID = I.ID
AND W.ColID = I.ColID
AND NOT EXISTS ( SELECT *
FROM #IDs AS ID
WHERE I.ID = ID.ID
AND ID.Pos > I.Pos
AND ID.ColID = I.ColID )
WHERE CHARINDEX(@SearchForCharIndex, Text, I.Pos + 1) > 0
IF @@ROWCOUNT = 0
SET @Added = 0
END
IF @Type IN ( '', 'P' )
BEGIN
INSERT #Sps ( ID )
SELECT W.ID
FROM #WithoutComments AS W
JOIN #IDs AS I ON W.ID = I.ID
AND W.ColID = I.ColID
JOIN SysObjects AS S ON W.ID = S.ID
WHERE S.Type = 'P'
AND CHARINDEX(@SearchFor2, W.Text,
I.Pos - @MaxGap) - I.Pos BETWEEN -@MaxGap
AND 0
AND CHARINDEX(@SearchFor2, W.Text,
I.Pos - @MaxGap) > 0
SET NOCOUNT OFF
SELECT DISTINCT
S.Name AS SPs
FROM #SPs AS P
JOIN SysObjects AS S ON P.ID = S.ID
ORDER BY 1
SET NOCOUNT ON
END
IF @Type IN ( '', 'TR' )
BEGIN
INSERT #Triggers ( ID )
SELECT W.ID
FROM #WithoutComments AS W
JOIN /*SysObjects AS S JOIN
SysComments AS C ON S.ID = C.ID JOIN*/ #IDs AS I ON W.ID = I.ID
AND W.ColID = I.ColID
JOIN SysObjects AS S ON W.ID = S.ID
WHERE S.Type = 'TR'
AND CHARINDEX(@SearchFor2, W.Text,
I.Pos - @MaxGap) - I.Pos BETWEEN -@MaxGap
AND 0
AND CHARINDEX(@SearchFor2, W.Text,
I.Pos - @MaxGap) > 0
SET NOCOUNT OFF
SELECT DISTINCT
S.Name AS Triggers
FROM #Triggers AS T
JOIN SysObjects AS S ON T.ID = S.ID
ORDER BY 1
SET NOCOUNT ON
END
IF @Type IN ( '', 'FN', 'TF' )
BEGIN
INSERT #Functions ( ID )
SELECT W.ID
FROM #WithoutComments AS W
JOIN /*SysObjects AS S JOIN
SysComments AS C ON S.ID = C.ID JOIN*/ #IDs AS I ON W.ID = I.ID
AND W.ColID = I.ColID
JOIN SysObjects AS S ON W.ID = S.ID
WHERE S.Type IN ( 'FN', 'TF' )
AND CHARINDEX(@SearchFor2, W.Text,
I.Pos - @MaxGap) - I.Pos BETWEEN -@MaxGap
AND 0
AND CHARINDEX(@SearchFor2, W.Text,
I.Pos - @MaxGap) > 0
SET NOCOUNT OFF
SELECT DISTINCT
S.Name AS Functions
FROM #Functions AS T
JOIN SysObjects AS S ON T.ID = S.ID
ORDER BY 1
SET NOCOUNT ON
END
END
SET NOCOUNT OFF
SELECT DISTINCT
S.Name AS Fields
FROM SysObjects AS S
JOIN SysColumns AS C ON S.ID = C.ID
AND S.Type = 'U'
AND C.Name LIKE '%' + @SearchFor + '%'
ORDER BY 1
SELECT Name AS Tables
FROM SysObjects
WHERE Type = 'U'
AND Name LIKE '%' + @SearchFor + '%'
SET NOCOUNT ON
DECLARE MyCursor CURSOR
FOR SELECT S.ID
FROM #SPs AS S
JOIN SysObjects AS O ON S.ID = O.ID
GROUP BY S.ID,
Name
ORDER BY Name
--DECLARE @ID2 AS INTEGER
DECLARE @Name AS VARCHAR(100)
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @ID
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SELECT @Name = ''
SELECT @Name = Name
FROM SysObjects
WHERE ID = @ID
IF NOT @Name = ''
EXEC ( 'SP_HELPTEXT ' + @Name )
FETCH NEXT FROM MyCursor INTO @ID
END
CLOSE MyCursor
DEALLOCATE MyCursor
DECLARE MyCursor CURSOR
FOR SELECT S.ID
FROM #Triggers AS S
JOIN SysObjects AS O ON S.ID = O.ID
GROUP BY S.ID,
Name
ORDER BY Name
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @ID
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SELECT @Name = ''
SELECT @Name = Name
FROM SysObjects
WHERE ID = @ID
IF NOT @Name = ''
EXEC ( 'SP_HELPTEXT ' + @Name )
FETCH NEXT FROM MyCursor INTO @ID
END
CLOSE MyCursor
DEALLOCATE MyCursor
DECLARE MyCursor CURSOR
FOR SELECT S.ID
FROM #Functions AS S
JOIN SysObjects AS O ON S.ID = O.ID
GROUP BY S.ID,
Name
ORDER BY Name
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @ID
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SELECT @Name = ''
SELECT @Name = Name
FROM SysObjects
WHERE ID = @ID
IF NOT @Name = ''
EXEC ( 'SP_HELPTEXT ' + @Name )
FETCH NEXT FROM MyCursor INTO @ID
END
CLOSE MyCursor
DEALLOCATE MyCursor
January 22, 2009 at 8:33 am
I didn't go through the whole script, but you might want to submit it to the script library here.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply