Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sys.syscomments


sys.syscomments

Author
Message
Gobikannan
Gobikannan
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2180 Visits: 611
Comments posted to this topic are about the item sys.syscomments

-----------------
Gobikannan
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21113 Visits: 18259
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3627 Visits: 3236
I disagree: the query returns all rows that contains the word 'order' in the column [text] that may appear in comments or definition.
jaganmohan.rao
jaganmohan.rao
SSC Eights!
SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)SSC Eights! (850 reputation)

Group: General Forum Members
Points: 850 Visits: 71
Good Question! Keep questioning................

Regards,
Jagan.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16515 Visits: 13208
The question on itself is OK, but why a question about a deprecated SQL Server 2000 system table? (as BOL clearly states here)

I would have prefered the same question but about the equivalent system view.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
I think the answer is wrong. "All of the above" should be correct.
Let's look at the three first options.

1. "Retrieves the name of stored procedures which consists the text 'order' 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."

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."

Nowhere does it say "Only", like in "Only retrieves the name of stored procedures". That means that #1 is also correct. The same goes for #2 and #3. Both the definition and the comments can be retrieved from sys.syscomments view, and again, none of the answers excludes the other options by saying "Only comments" or "Only definition".

The correct answer, in my opinion, should therefore be "All of the above"
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 Visits: 3889
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.

Best Regards,

Chris Büttner
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 Visits: 3889
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.

Best Regards,

Chris Büttner
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
Execute the following to create a proc called QotdFail

/* This is my comment */

create procedure QotdFail
as

select 1



Then
SELECT DISTINCT
OBJECT_NAME(id) AS [Column1]
FROM sys.syscomments
WHERE text LIKE '%This is my comment%'



On SQL Server 2005 I get my QotdFail returned.
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3494 Visits: 4408
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 :-)
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