sys.syscomments

Question

sys.syscomments

Viewing 15 posts - 16 through 30 (of 37 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Toreador (12/1/2010)
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.

Ditto here.

#2 is not 100% clear and leaves a little too much room for interpretation. But since 1 and 3 are definitely right, the only possible answer had to be all of the above.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

Ninja's_RGR'us (12/1/2010)
Toreador (12/1/2010)
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.
Ditto here.
#2 is not 100% clear and leaves a little too much room for interpretation. But since 1 and 3 are definitely right, the only possible answer had to be all of the above.

Author wants to confuse by using word "Comments". "All of the above" is right if we consider inline comments in the proc or other db objects.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Hardy21 (12/1/2010)
Ninja's_RGR'us (12/1/2010)
Toreador (12/1/2010)
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.
Ditto here.
#2 is not 100% clear and leaves a little too much room for interpretation. But since 1 and 3 are definitely right, the only possible answer had to be all of the above.
Author wants to confuse by using word "Comments". "All of the above" is right if we consider inline comments in the proc or other db objects.

That's what I meant by interpretation. I, like at least another posted considered the meta data comments that we can put in the columns of the "edit table" gui. That's the only way #2 could be false. But it seemed too far fetched so I chose all of the above as #1 couldn't be false.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Moreover, there's no mention of functions in the explanation or the answers. Those definitions are also available in syscomments.

So I guess that none of the above is also a good option even if #1 is definitely correct if no thorough :hehe:

TomThomson SSC Guru Points: 104773 More actions · Answer 5

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.
With regards to option 1, I agree that this is not an invalid option. But #3 is the "precicest" option.

If you can't see any comments in that view, you have no comments in any of your stored procedures, triggers, views etcetera - appalling coding practise - but in any case the view does include all none of them, so the correct answer is still "all of the above".

The bad English combined with the wrong answer and the use of a deprecated sql 2000 compatibility view instead os sys.sql_modules make it a pretty awful question.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 6

Christian Buettner-167247 (12/1/2010)
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.

If you've never seen any comments in there I don't want you writing any stored procedures or triggers in my shop!

Tom

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

Tom.Thomson (12/1/2010)
If you've never seen any comments in there I don't want you writing any stored procedures or triggers in my shop!

You may have missed my 3rd post where I have clarified my original two posts.

I was referring to object level comments (more known as "Description" or "Extended properties" in SQL Server), and I did not consider inline comments when I wrote my post.

Best Regards,

Chris Büttner

sjimmo SSChampion Points: 11139 More actions · Answer 8

Good question, and actually makes one think a little.

Tom
If you've never seen any comments in there I don't want you writing any stored procedures or triggers in my shop!

Kind of strong, don't you think? Let me ask this, where are the comments contained in the syscomments table? I see many comments where the writer thinks that the text field contains either definition or comments. I don't see it myself.

I see a definition for an item created which contains comments if there are any in the definition. I do not see a place specifically for comments of an object created. I see a difference here.

The definition of the fields can be found at http://msdn.microsoft.com/en-us/library/ms186293.aspx

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 9

Bad question.

To me, the author was seeking to see whether we think that the comments are stripped out during compilation and only "true" code is saved in the text column, or whether the text column has everything that is transmitted in the execution of the CREATE PROC. Obviously the latter is true and by that set of terms "All of the above" is true. I know of no other set of comments nor do I use them. If there are metadata comments for objects outside of the actual definition, the question needed to specify what was meant. By my experience, given the wording, "all of the above" was needed.

Cliff Jones SSChampion Points: 10547 More actions · Answer 10

I don’t feel too bad about missing this question for reasons already stated. The take away from this one is to search OBJECT_DEFINITION of sys.procedures to find stored procedures or functions containing particular Text, if that is the intent. Or search ROUTINE_DEFINITION of INFORMATION_SCHEMA.ROUTINES.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 11

I don't see the confusion on why the only acceptable answer would be the 3rd option. Of course I might think differently than others.

1) Retrieves the name of stored procedures which consists the text 'order' in the definition.-The query does not retrieve stored procedures it retrieves "each" object within sys.syscomments that the liking of "orders" 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.

- What does the "text" column hold...according to BOL "The text column contains the original SQL definition statements." If I create a procedure with just blocked comments in it, yes it consists of comments, but those comments are the definition of that object. At least in my mind that is how I read it.

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.- BOL exact words at the very top "Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements." If the system view "contains" it, that means I would be retrieving it with a query.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Shawn Melton (12/1/2010)
I don't see the confusion on why the only acceptable answer would be the 3rd option. Of course I might think differently than others.
1) Retrieves the name of stored procedures which consists the text 'order' in the definition.-The query does not retrieve stored procedures it retrieves "each" object within sys.syscomments that the liking of "orders" 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.
- What does the "text" column hold...according to BOL "The text column contains the original SQL definition statements." If I create a procedure with just blocked comments in it, yes it consists of comments, but those comments are the definition of that object. At least in my mind that is how I read it.
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.- BOL exact words at the very top "Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements." If the system view "contains" it, that means I would be retrieving it with a query.

Well then even bol is incomplete in this case as functions are also contained there.

It's nice info to understand, yet... I hate repeating myself and 100 others :hehe:.

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Shawn Melton (12/1/2010)
I don't see the confusion on why the only acceptable answer would be the 3rd option. Of course I might think differently than others.

I didn't see any confusion other than that many people don't understand the meaning of "all of the above" until I saw your comment.

1) Retrieves the name of stored procedures which consists the text 'order' in the definition.-The query does not retrieve stored procedures it retrieves "each" object within sys.syscomments that the liking of "orders" in the definition.

So you think that the definition of a stored procedure doesn't contain its name? That strikes me as real confusion! Unless you read "consists" as "consists of" rather than as "contains" (in which case the answer is "none of the above", if you are consistent and read it that way in each of the first three options, so I would still think you were confused).

2) Retrieves the name of view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedures which consists the text 'order' in the comments.
- What does the "text" column hold...according to BOL "The text column contains the original SQL definition statements." If I create a procedure with just blocked comments in it, yes it consists of comments, but those comments are the definition of that object. At least in my mind that is how I read it.

So do you think that the text field doesn't contain any comments that are included in the object definition, or do you think that the LIKE operator somehow skips the comments in the definition unless the definition consists of nothing but comments? It must be one or the other! That's definitely confusion.

Tom

hugo-939487 SSC Enthusiast Points: 143 More actions · Answer 14

Although you will find views, rules, defaults, etc. with the word "order" in them, you may not find ALL views, rules, etc. with the word "order".

For larger objects the text is split among several rows, and the word "order" could be split into two different rows.

Imran Ashraf-452633 SSCommitted Points: 1969 More actions · Answer 15

Northwind is normally associated with SQL Server 2000 and below. For sys.syscomments only came around in SQL Server 2005 and above where Northwind no longer was provided. Based on the Version you are using the query will fail if you are using versions below 2005 or it will complete and cause the issues that people have already detailed.