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: 25405 Visits: 12494
hugo-939487 (12/2/2010)
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.

Another reason not to use sys.syscomments and use sys.sql_modules instead (the type of the definition column of sys.sql_modules is nvarchar(max)).

Tom

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: 4740 Visits: 2907
Imran
Northwind is normally associated with SQL Server 2000 and below.

Unless you performed an upgrade from 2000, in which case the database would be upgraded to the next version as well.

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
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: 4740 Visits: 2907
Tom
Another reason not to use sys.syscomments and use sys.sql_modules instead (the type of the definition column of sys.sql_modules is nvarchar(max)).


This isn't what the question is. Additionally, maybe if the question was about sys.sql_modules then you wouldn't be casting stones, as the field is called definition reather than text.

Once again, since there is not a field for comments about the object, how can you say that comments are returned? The truth of the matter is that the definition of the object is returned, which may or may not contain comments internal to it.

If you cannot tell the difference, then, using your words, I would not want you writing code in my shop. I couldn't afford to have you spend your day telling everyone how wrong they are over such nonsense. Obviously, you got the question wrong, yet have made up for the point.

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
sjimmo (12/2/2010)

Once again, since there is not a field for comments about the object, how can you say that comments are returned? The truth of the matter is that the definition of the object is returned, which may or may not contain comments internal to it.



But you had to qualify the word "comments" in your own reply! There ARE comments within the definition of the stored procedure, and they DO count! Many languages, when they compile the code, strip out comments as wasted space. I could easily see a scenario where the definition of the SP, in order to save space, strips out the comments from the definition in order to maximize the system table space. You have defined comments as "an explanatory column about the object that is separate from the object itself" and most of us did it as "explanations within the object that tell you what it does". Just the fact that we have to have this discussion at all proves how poorly written the question was, if no one can agree upon what is meant by "comments" and from two separate interpretations both views could be correct.

Also, FWIW, within the batch of a stored procedure creation, comments before the definition actually begins are also included within the column Text, so by a certain definition those are not "within the definition of the object" since the CREATE PROC command has not come yet, and yet comments exist.

In order for these questions to have value, we really need them to have clarity and no ambiguity.
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: 4740 Visits: 2907
Jeff
Many languages, when they compile the code, strip out comments as wasted space.


Agreed - but these are not comiled apps. Before you say it, I know that many HTML generators also can strip out comments and white space.

Actually, I think we agree on most points, but the arrogance of some to make this a black and white case, rather than being able to see the areas of gray which this question evokes is my issue.

The question does not ask what is in the text field, but what is returned based upon the select statement where the string is in the text field. The answer does not contain anything from the text field, only a translatin of the id into a name, and these names are of what?

How we got into comments is truely remarkable, but since it was broached then it should be explained correctly. You do not know how many beginners are now out there looking for comments which may or may not exist inside the definition.

In order for these questions to have value, we really need them to have clarity and no ambiguity.


We also agree here, though I do not feel that we should knock someone who obviously has some other language besides english as their primary language. This is an international group and thus multiple primary languages as well as dialects of those languages are used. The fact that the individual tried, and hopefully will continue is what is important.

Besides, with the attitude, I wouldn't want to work in his shop anyways;-)

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
UMG Developer
UMG Developer
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3936 Visits: 2204
Thanks for the question, but I agree with the people that think "all of the above" is the correct answer.
Carlton Leach
Carlton Leach
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1540 Visits: 1304
5 cents courtesy of Mr. Leach:

The term 'Comments' in this question is about as ambiguous as the word 'Database' when some monkey asks you to drop one. Or 'car' when the missus tells you she saw a really flash one.

Comments in my eyes are just commented out statements in the objects schema.
Extended properties = entirely different (if this is what it was aimed at).

Should we start a thread called 'WTF are comments??' :-D
thava
thava
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 557
vk-kirov (12/1/2010)
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 :-)


i am also go with you



Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
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