Search for TExt of Stored Procedure - Replacement for syscomments

Question

Post reply

Search for TExt of Stored Procedure - Replacement for syscomments

Welsh Corgi

SSC Guru

Points: 116520
More actions
June 26, 2012 at 3:35 pm

#260500

What would the SQL Server 2005 and above eqivilent command for the following?
SELECT *
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%Receivable_Activity%'
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Curious, have you looked in BOL?

http://msdn.microsoft.com/en-us/library/ms175081.aspx

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

This may also be useful.

http://msdn.microsoft.com/en-us/library/ms187997.aspx

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I tried BOL but I must not have entered the right search criteria.

Then I goggled it and got a article that provided the example that I listed in my original post but the SQL Server 2005 version that the author claims was the equivalent was not.

SELECT *

FROM sys.procedures

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Receivable_Activity%'

BTW, thanks!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Lynn Pettis (6/26/2012)
This may also be useful.
http://msdn.microsoft.com/en-us/library/ms187997.aspx

Excellent, thank you! 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Welsh Corgi (6/26/2012)
I tried BOL but I must not have entered the right search criteria.
Then I goggled it and got a article that provided the example that I listed in my original post but the SQL Server 2005 version that the author claims was the equivalent was not.
SELECT *
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Receivable_Activity%'
BTW, thanks!

Just curious, because the second link helped me earlier in my current position when I needed to find all the views that were defined using SELECT *.

Gazareth One Orange Chip Points: 27737 More actions · Answer 6

Lynn Pettis (6/26/2012)
Welsh Corgi (6/26/2012)
I tried BOL but I must not have entered the right search criteria.
Then I goggled it and got a article that provided the example that I listed in my original post but the SQL Server 2005 version that the author claims was the equivalent was not.
SELECT *
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Receivable_Activity%'
BTW, thanks!
Just curious, because the second link helped me earlier in my current position when I needed to find all the views that were defined using SELECT *.

I'm using sys.sql_modules right now! Good for finding those pesky NOLOCKs... 🙂

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

I did not find sys.syscomments until today.

Why is sys.sql_modules the preferred table?

That's funny that you mention NOLOCK's. I had someone tell me that the WITH (NOLOCK) was their company standard.:w00t:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Welsh Corgi (6/27/2012)
I did not find sys.syscomments until today.
Why is sys.sql_modules the preferred table?
That's funny that you mention NOLOCK's. I had someone tell me that the WITH (NOLOCK) was their company standard.:w00t:

The table sys.syscomments is the for backward compatibility for SQL Server 2000 systems. The new DMV sys.sql_modules is the one you should be using for new development.

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

I had someone tell me that the WITH (NOLOCK) was their company standard.:w00t:

Did you start launching pork chops at them immediately? Somebody really needs to educate that company on what a stupid idea that is.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Sean Lange (6/27/2012)
I had someone tell me that the WITH (NOLOCK) was their company standard.:w00t:
Did you start launching pork chops at them immediately? Somebody really needs to educate that company on what a stupid idea that is.

I have run into a couple of companies like that. It has offered up several teaching opportunities:-D

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 11

Welsh Corgi (6/27/2012)
Why is sys.sql_modules the preferred table?

sys.syscomments uses nvarchar(4000) for the sql text, so if you have a procedure longer than 4000 it will be split into multiple rows.