sys.syscomments

Question

sys.syscomments

Viewing 8 posts - 31 through 37 (of 37 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

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 SSChampion Points: 11139 More actions · Answer 2

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 SSChampion Points: 11139 More actions · Answer 3

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 SSCrazy Eights Points: 9585 More actions · Answer 4

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 SSChampion Points: 11139 More actions · Answer 5

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 SSChampion Points: 13482 More actions · Answer 6

Thanks for the question, but I agree with the people that think "all of the above" is the correct answer.

Carlton Leach SSCarpal Tunnel Points: 4202 More actions · Answer 7

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??' 😀

thava SSCrazy Points: 2275 More actions · Answer 8

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 [/url]