November 28, 2010 at 4:43 am
Hi All,
Thanks to others who have posted helpful tips I have a way of searching through stored procedures to list those that reference a specific table (one way listed below ) but ideally I would like to exclude those table references that are listed in comments or hashed out and no longer part of the active body of the procedure ..
Currently can't see a way to do this - has anyone got an idea on how to approach this or have actioned this themselves ?
It may be this is just not possible searching through the sys tables ..but thought I would just ask
Any feedback/tips much appreciated
Thanks,
Ralph
SELECT DISTINCT SO.NAME
FROM SYSOBJECTS SO (NOLOCK)
WHERE SO.TYPE = 'P'
AND SO.NAME LIKE @StringToSearch
ORDER BY SO.Name
November 28, 2010 at 6:47 am
Unless you get incredibly sophisticated with your search algorithms, possibly using CLR or something, to determine if the string you're looking at is contained within a comment, of either type, line comments or block comments, no, there's no way to do this with a straight-forward T-SQL statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 28, 2010 at 9:29 am
Wouldn't a search of dependencies tell you?
{edit} Bad suggestion on my part. :blush: Like Steve says below, it's NOT 100% guaranteed. It's always worked for me in the past but that's only because I (apparently) got lucky and followed "the rules". I knew that dynamic SQL wouldn't register but I didn't know the "fault" induced by deferred naming, etc.
See sys.sql_dependencies in Books Online for more info on why a dependency may or may not exist.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2010 at 10:06 am
If the dependency tree in SQL Server is up to date it works, but it isn't guaranteed. I'd agree with Grant, there isn't an easy way to do this. What you could do is just search, and if you find tables in stored procedure comments, go through and clean up the comments to change the table name in there to something that won't match.
November 28, 2010 at 11:31 am
Here's what Steve means about "if they're up to date". This comes from Books Online under "sys.sql_dependencies"...
[font="Arial Black"]Remarks[/font]
Dependencies are established during CREATE only if the referenced (independent) entity exists at the time that the referencing (dependent) object is created. Due to deferred name resolution, the referenced entity need not exist at the time of creation. In this case, a dependency row is not created. Moreover, entities referenced through dynamic SQL do not establish dependencies.
If the referenced (independent) entity is dropped using DROP, the dependency row is deleted automatically. To re-establish the dependency row, you will need to re-create both, using CREATE, in the correct dependency order.
Both schema-bound and non-schema-bound dependencies are tracked for objects. Dependencies on types, XML schema collections, and partition functions are only tracked for schema-bound dependencies. CHECK constraints, defaults, and computed column references are implicitly schema-bound.
Also note that further reading reveals that anything contained in dynamic SQL is NOT included, ever.
So... with all that in mind, I'll have to withdraw my suggestion because it was a bad one. :blush:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2010 at 11:49 am
Hi
I did doubt there was a way to do this using T-SQL but really appreciate the feedback from everyone - I'll look at Steve's suggestion of performing the search then go through and clean up the comments to change the table name in there to something that doesn't match.
Many thanks all
Ralph
November 28, 2010 at 12:05 pm
I have this snippets saved which might help; it's using a pair of loops table to strip out any comments , so you can then search just the remaining proc definition;
declare @definition varchar(max),
@objectname varchar(255),
@vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @objectname = 'sp_getDDL'
select @definition = definition + 'GO' + @vbcrlf from sys.sql_modules where [object_id] = object_id(@objectname)
--'objective: strip out comments.
--first loop is going to look for pairs of '/*' and '*/', and STUFF them with empty space.
--second loop is going to look for sets of '--' and vbCrLf and STUFF them with empty space.
--===== Replace all '/*' and '*/' pairs with nothing
WHILE CHARINDEX('/*',@definition) > 0
SELECT @definition = STUFF(@definition,
CHARINDEX('/*',@definition),
CHARINDEX('*/',@definition) - CHARINDEX('/*',@definition) + 2, --2 is the length of the search term
'')
--===== Replace all single line comments
WHILE CHARINDEX('--',@definition) > 0
AND CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) > CHARINDEX('--',@definition)
SELECT @definition = STUFF(@definition,
CHARINDEX('--',@definition),
CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) - CHARINDEX('--',@definition) + 2,
'')
print @definition --you can now search this without false positives from comments.
Lowell
November 29, 2010 at 6:05 am
Hi
This really helps a lot - can build up list of all objects referencing the table then run through this.:-)
Much appreciated
Many Thanks
Ralph
November 29, 2010 at 7:52 pm
You could also use this free utility application to search SQL code while optionally ignoring comments, and/or to script out selected SQL routines in correct dependency order...
Creator of SQLFacts, a free suite of tools for SQL Server database professionals.
July 15, 2011 at 5:53 pm
Lowell (11/28/2010)
I have this snippets saved which might help; it's using a pair of loops table to strip out any comments , so you can then search just the remaining proc definition;
declare @definition varchar(max),
@objectname varchar(255),
@vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @objectname = 'sp_getDDL'
select @definition = definition + 'GO' + @vbcrlf from sys.sql_modules where [object_id] = object_id(@objectname)
--'objective: strip out comments.
--first loop is going to look for pairs of '/*' and '*/', and STUFF them with empty space.
--second loop is going to look for sets of '--' and vbCrLf and STUFF them with empty space.
--===== Replace all '/*' and '*/' pairs with nothing
WHILE CHARINDEX('/*',@definition) > 0
SELECT @definition = STUFF(@definition,
CHARINDEX('/*',@definition),
CHARINDEX('*/',@definition) - CHARINDEX('/*',@definition) + 2, --2 is the length of the search term
'')
--===== Replace all single line comments
WHILE CHARINDEX('--',@definition) > 0
AND CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) > CHARINDEX('--',@definition)
SELECT @definition = STUFF(@definition,
CHARINDEX('--',@definition),
CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) - CHARINDEX('--',@definition) + 2,
'')
print @definition --you can now search this without false positives from comments.
That rocks! I used that...
February 9, 2012 at 7:55 am
I have used this. This will help us to ignore the commented lines as well as the line prior to the CREATE statetments. Look like conventional but helpful
--Exec dbo.search 'test'
Create Procedure [dbo].[search] (@searchstr varchar(100))
As
Declare @cntr int
Declare @cnt int
Declare @proc_name varchar(100)
Declare @proc_type varchar(100)
Declare @seq int
Declare @out_str varchar(255)
Create Table #proc_names (sl_no int identity(1,1), proc_name varchar(100),type varchar(100))
Insert into #proc_names
Select name,Case xtype When 'P' Then 'Stored Procedure'
When 'V' Then 'View'
When 'TR' Then 'Trigger'
When 'FN' Then 'Function' End
from dbo.sysobjects where xtype in ('P','V','TR','FN') and uid = 1
Create Table #proc_script (seq_no int identity(1,1), proc_str text)
Create Table #Rtn_proc_str (sl_no int identity(1,1), object_name varchar(100), object_type varchar(100), errortext varchar(255))
Set @cntr = 1
Select @cnt = count(1) from #proc_names
While @cntr <> @cnt
Begin
Select @proc_name = proc_name, @proc_type = type from #proc_names where sl_no = @cntr
BEGIN TRY
Insert into #proc_script
Exec dbo.sp_helptext @proc_name
END TRY
BEGIN CATCH
Set @out_str = ERROR_MESSAGE()
Insert into #Rtn_proc_str
Values (@proc_name, @proc_type, @out_str)
END CATCH
Select @seq = seq_no from #proc_script where proc_str like 'Create %'
Delete from #proc_script where seq_no < @seq
Delete from #proc_script where proc_str like '--%'
If exists (Select 1 from #proc_script where proc_str like '%'+@searchstr+'%')
Begin
Insert into #Rtn_proc_str
Values (@proc_name, @proc_type,'')
End
Truncate Table #proc_script
Set @cntr = @cntr + 1
End
Select object_name, object_type, errortext from #Rtn_proc_str order by object_type, object_name
September 3, 2012 at 6:00 pm
The script is great!!!
Do you happen to have one that does the exact opposite? I'm doing Peer Review of a DB project and need to find all SP without a comments section.
Thanks
April 8, 2013 at 7:36 am
Hi, Lowell!
How do you actually call your code snippet that ignores text inside comments?
--Brian
April 8, 2013 at 8:08 am
brian wow it's been a while since i saw this thread.
Today, I actually do the same with regular expressions and a CLR; if that's an option, let me know and i'll pso tthat too.
here's a little more advanced snippet , all via TSQL, which creates a temp table of all definitions, and then strips out the comments, based on my earlier post.
in theory, the finished temp table has just the object definitions, so if you search THAT, you get just the items that truly reference the string you search for:
IF (SELECT OBJECT_ID('Tempdb.dbo.#tmp')) IS NOT NULL
DROP TABLE #tmp
SELECT
Schema_Name(objz.schema_id) As SchemaName,
objz.name As ObjectName,
objz.type As TypeCode,
objz.type_desc As ObjectType,
modz.definition
into #tmp
FROM sys.objects objz
INNER JOIN sys.sql_modules modz
on objz.object_id = modz.object_id
--################################################################################################
--Pre Step
--generic cleanup:
--some definitions may end in only vbLf / CHAR(10), and not my convention of vbCrLf/CHAR(13) + CHAR(10)
--this cleanup is REQUIRED because we need some sort common of End-Of-Line indicator for single line comments.
UPDATE #tmp
SET [definition] = REPLACE([definition],CHAR(10),CHAR(13) + CHAR(10))
UPDATE #tmp
SET [definition] = REPLACE([definition],CHAR(13) + CHAR(10) + CHAR(10),CHAR(13) + CHAR(10))
--################################################################################################
--'objective: strip out comments.
--first loop is going to look for pairs of '/*' and '*/', and STUFF them with empty space.
--===== Replace all '/*' and '*/' pairs with nothing
WHILE EXISTS(SELECT 1 FROM #tmp WHERE CHARINDEX('/*',[definition]) > 0 )
BEGIN
UPDATE #tmp
SET [definition] = STUFF([definition],
CHARINDEX('/*',[definition]),
CHARINDEX('*/',[definition]) - CHARINDEX('/*',[definition]) + 2, --2 is the length of the search term
'')
WHERE CHARINDEX('/*',[definition]) > 0
IF @@ROWCOUNT = 0
BREAK;
END --WHILE
--################################################################################################
--second loop is going to look for sets of '--' and vbCrLf and STUFF them with empty space.
--===== Replace all single line comments
WHILE EXISTS(SELECT 1 FROM #tmp
WHERE CHARINDEX('--',[definition]) > 0
AND CHARINDEX(CHAR(13) + CHAR(10),[definition],CHARINDEX('--',[definition])) > 0 )
BEGIN
UPDATE #tmp
SET [definition] = STUFF([definition],
CHARINDEX('--',[definition]),
CHARINDEX(CHAR(13) + CHAR(10),[definition],CHARINDEX('--',[definition])) - CHARINDEX('--',[definition]) + 2,
'')
WHERE CHARINDEX('--',[definition]) > 0
AND CHARINDEX(CHAR(13) + CHAR(10),[definition],CHARINDEX('--',[definition])) > 0
IF @@ROWCOUNT = 0
BREAK;
END --WHILE
SELECT * FROM #tmp
--WHERE definition LIKE 'MyObjectName%'
Lowell
April 8, 2013 at 8:18 am
Cool, I'll try playing with that.
What I'm trying to do is create a Stored Procedure that will take a string of text as a parameter, and then search all the database objects, in all schemas, within a given database, and return a table showing all the objects the string appears, or optionally, to do the same but ignore any object where the search string only appears in the comments. And I think I need to create it in the master database to make it available in all the other databases on my server.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply