SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sys.syscomments


sys.syscomments

Author
Message
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25386 Visits: 12491
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
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5195 Visits: 3889
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
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4738 Visits: 2907
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
jeff.mason
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2631 Visits: 2137
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
Cliff Jones
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5149 Visits: 3648
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
Shawn Melton
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5671 Visits: 3537
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: blog.wsmelton.info
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65141 Visits: 9671
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.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25386 Visits: 12491
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
hugo-939487
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 513
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
Imran Ashraf-452633
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1239 Visits: 484
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search