sys.syscomments

Question

sys.syscomments

Gobikannan

SSCrazy

Points: 2735
More actions
November 30, 2010 at 8:15 pm

#241959

Comments posted to this topic are about the item sys.syscomments
-----------------
Gobikannan

Viewing 15 posts - 1 through 15 (of 37 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Thanks for the question.

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

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 2

I disagree: the query returns all rows that contains the word 'order' in the column [text] that may appear in comments or definition.

jaganmohan.rao SSC Eights! Points: 976 More actions · Answer 3

Good Question! Keep questioning................

Regards,
Jagan.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

The question on itself is OK, but why a question about a deprecated SQL Server 2000 system table? (as BOL clearly states here)

I would have prefered the same question but about the equivalent system view.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 5

I think the answer is wrong. "All of the above" should be correct.

Let's look at the three first options.

1. "Retrieves the name of stored procedures which consists the text 'order' in the definition."

2. "Retrieves the name of view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedures which consists the text 'order' in the comments."

3. "Retrieves the name of view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedures which consists of the text 'order' in the definition."

Nowhere does it say "Only", like in "Only retrieves the name of stored procedures". That means that #1 is also correct. The same goes for #2 and #3. Both the definition and the comments can be retrieved from sys.syscomments view, and again, none of the answers excludes the other options by saying "Only comments" or "Only definition".

The correct answer, in my opinion, should therefore be "All of the above"

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 6

Nils Gustav Stråbø (12/1/2010)
Both the definition and the comments can be retrieved from sys.syscomments view

Are you sure about that? I can't see any comments in that view.

With regards to option 1, I agree that this is not an invalid option. But #3 is the "precicest" option.

Best Regards,

Chris Büttner

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 7

Carlo Romagnano (12/1/2010)
I disagree: the query returns all rows that contains the word 'order' in the column [text] that may appear in comments or definition.

I think the wording "consists" was just a language issue for a non-native speaker. But to me it was obvious that he meant "contain".

With regards to the comments - I am not aware of any "comments" being in that system view. The name of the view & the text type values may lead you to this assumption, but I have never seen any comments in there.

Best Regards,

Chris Büttner

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 8

Execute the following to create a proc called QotdFail

/* This is my comment */

create procedure QotdFail

as

select 1

Then

SELECT DISTINCT

OBJECT_NAME(id) AS [Column1]

FROM sys.syscomments

WHERE text LIKE '%This is my comment%'

On SQL Server 2005 I get my QotdFail returned.

vk-kirov SSCertifiable Points: 7686 More actions · Answer 9

Christian Buettner-167247 (12/1/2010)
Nils Gustav Stråbø (12/1/2010)
Both the definition and the comments can be retrieved from sys.syscomments view
Are you sure about that? I can't see any comments in that view.

Comments are part of a procedure's definition, so if you retrieve the definiton, you retrieve the comments as well.

CREATE PROCEDURE QOTD_Test

AS

PRINT 'This is a test'; -- this is a comment

GO

SELECT "text" FROM sys.syscomments WHERE id = OBJECT_ID('QOTD_Test');

The result of the SELECT statement contains the "this is a comment" comment 🙂

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 10

Ok, I think I finally arrived.

I thought the "comments" were the standalone extended properties / description of the stored procedures, constraints etc..

I did not consider the inline comments of the object definition (I actually considered these comments as object definition as well).

Best Regards,

Chris Büttner

anders-731262 Ten Centuries Points: 1256 More actions · Answer 11

Can someone please explain why "All of the above" is wrong?

Does comments refer to something other than inline comments ("--"'s and "/**/"'s)?

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 12

This is not a particularly very well designed question. What is a comment and what is the definition? I would say the comments inside an object are part of the definition.

There are of course the extended properties which are not in syscomments, but I've never come across anyone who calls that comments.

Just because you're right doesn't mean everybody else is wrong.

abdul.rahman Newbie Points: 6 More actions · Answer 13

Yeah "All the Above" is the correct answer. I think the author is confused with the option.

Toreador SSChampion Points: 11365 More actions · Answer 14

The first and third answers are definitely correct (the first being a subset of the third), so I went for 'all of the above'. A good question, spoiled slightly by the choice of answers.