sys.syscomments

  • Gobikannan

    SSCrazy

    Points: 2735

    Comments posted to this topic are about the item sys.syscomments

    -----------------
    Gobikannan

  • SQLRNNR

    SSC Guru

    Points: 281210

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

  • Carlo Romagnano

    SSC-Insane

    Points: 21792

    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

    SSC Eights!

    Points: 976

    Good Question! Keep questioning................

    Regards,
    Jagan.

  • Koen Verbeeck

    SSC Guru

    Points: 258940

    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.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    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

    SSChampion

    Points: 13729

    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

    SSChampion

    Points: 13729

    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ø

    SSChampion

    Points: 11259

    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

    SSCertifiable

    Points: 7686

    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 🙂

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Ok, I think I finally arrived.

    I thought the "comments" were the standalone extended properties / description of the stored procedures, constraints etc..

    I did not consider the inline comments of the object definition (I actually considered these comments as object definition as well).

    Best Regards,

    Chris Büttner

  • anders-731262

    Ten Centuries

    Points: 1254

    Can someone please explain why "All of the above" is wrong?

    Does comments refer to something other than inline comments ("--"'s and "/**/"'s)?

  • Rune Bivrin

    SSCertifiable

    Points: 7573

    This is not a particularly very well designed question. What is a comment and what is the definition? I would say the comments inside an object are part of the definition.

    There are of course the extended properties which are not in syscomments, but I've never come across anyone who calls that comments.


    Just because you're right doesn't mean everybody else is wrong.

  • abdul.rahman

    Newbie

    Points: 6

    Yeah "All the Above" is the correct answer. I think the author is confused with the option.

  • Toreador

    SSChampion

    Points: 11231

    The first and third answers are definitely correct (the first being a subset of the third), so I went for 'all of the above'. A good question, spoiled slightly by the choice of answers.

Viewing 15 posts - 1 through 15 (of 38 total)

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