sys.syscomments

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

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

  • 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

  • I disagree: the query returns all rows that contains the word 'order' in the column [text] that may appear in comments or definition.

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

    Regards,
    Jagan.

  • 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

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

  • 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

  • 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

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

  • 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 🙂

  • 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

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

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

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

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

  • 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