Search String in SPs excluding the comment section

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Search String in SPs excluding the comment section

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2012-04-20 : 10:38:24

Hi,

How can i get the list of stored procedures that uses the table 'EmployeeTable' excluding the stored procedures which contains the
'EmployeeTable' in comment section.

To find the list of stored procedures that contains the 'EmployeeTable'

I tried following, but my issue is it list the stored procedure name if 'EmployeeTable' contains in comment section also plz advice.

SELECT Name
FROM sys.objects
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'

SELECT
DISTINCT o.name AS Object_Name,
o.type_desc ,
m.definition
FROM
sys.sql_modules m
INNER JOIN
sys.objects o
ON
m.object_id = o.object_id
WHERE
m.definition Like '%Employee%'
ORDER BY
2,1

Thanks,
Vision.v1

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-20 : 10:45:50

You would have to parse the entire procedure code and tokenize or remove the comment sections, which is not easy, and even harder using T-SQL. You might want to investigate a third party code management tool. Redgate makes some for SQL Server that may have such a feature (search excluding comments).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources