Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

sys.syscomments Expand / Collapse
Author
Message
Posted Wednesday, December 1, 2010 5:31 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,743, Visits: 9,293
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
Post #1028541
Posted Wednesday, December 1, 2010 6:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:47 AM
Points: 2,840, Visits: 3,872
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
Post #1028560
Posted Wednesday, December 1, 2010 6:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
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
Post #1028601
Posted Wednesday, December 1, 2010 7:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:25 AM
Points: 989, Visits: 1,823
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.
Post #1028650
Posted Wednesday, December 1, 2010 10:14 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:18 AM
Points: 3,909, Visits: 3,636
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.
Post #1028798
Posted Wednesday, December 1, 2010 3:04 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:50 PM
Points: 860, Visits: 2,411
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
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1028971
Posted Wednesday, December 1, 2010 4:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
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 .
Post #1029014
Posted Wednesday, December 1, 2010 6:44 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,743, Visits: 9,293
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
Post #1029043
Posted Thursday, December 2, 2010 2:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:58 PM
Points: 44, Visits: 322
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.
Post #1029108
Posted Thursday, December 2, 2010 3:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 5:30 AM
Points: 1,115, Visits: 448
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.
Post #1029142
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse