How to look for a string in a stored procedure
I have the following code that looks for a specific string in all stored procedures in a database:
DECLARE @StringToSearch VARCHAR(500) SET @StringToSearch = '%nolock%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
I now would like to find stored procedures THAT DON'T contain the string. I already tried NOT LIKE but I still get stored procedures with the string. Any ideas?
DECLARE @StringToSearch VARCHAR(500) SET @StringToSearch = '%nolock%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
I now would like to find stored procedures THAT DON'T contain the string. I already tried NOT LIKE but I still get stored procedures with the string. Any ideas?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
the problem is that for a single stored proc, you have eventually several rows in syscomments.
DECLARE @StringToSearch VARCHAR(500) SET @StringToSearch = '%nolock%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
WHERE NOT EXISTS( SELECT NULL FROM syscomments o
WHERE o.ID = so.ID
AND o.Text LIKE @stringtosearch
)
ORDER BY SO.Name
since you're on 2005...use sys.sql_modules
DECLARE @StringToSearch VARCHAR(500) SET @StringToSearch = 'nolock'
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINE
WHERE CHARINDEX(@StringToSearch , ROUTINE_DEFINITION ) = 0
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINE
WHERE CHARINDEX(@StringToSearch , ROUTINE_DEFINITION ) = 0
>> that looks for a specific string in all stored procedures in a database:
Below code should help you out.
>> I now would like to find stored procedures THAT DON'T contain the string.
Also make sure that this code will not verify encrypted stored procedures.
Below code should help you out.
>> I now would like to find stored procedures THAT DON'T contain the string.
Also make sure that this code will not verify encrypted stored procedures.
SELECT Name
FROM sysobjects
WHERE Type = 'P'
EXCEPT
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
ASKER
Sorry, none of these worked. We are having issues with our database, and out DBA found several stored procedures that were locking the tables. We have over 1000 stored procedures in our database, and what I specifically want to look for, is for a stored procedure that does not contain the NOLOCK statement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DECLARE @StringToSearch VARCHAR(500) SET @StringToSearch = '%nolock%'
SELECT Distinct SO.Name, SC.Text
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text NOT LIKE @stringtosearch
ORDER BY SO.Name
SELECT Distinct SO.Name, SC.Text
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text NOT LIKE @stringtosearch
ORDER BY SO.Name
ASKER
Works great. Thanks!
Did you try my code? that works
where charindex('NOLOCK', definition) = 0