Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

sys.syscomments Expand / Collapse
Author
Message
Posted Tuesday, November 30, 2010 8:15 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 22, 2014 3:48 PM
Points: 1,851, Visits: 569
Comments posted to this topic are about the item sys.syscomments

-----------------
Gobikannan
Post #1028363
Posted Tuesday, November 30, 2010 11:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 21,638, Visits: 15,305
Thanks for the question.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1028426
Posted Wednesday, December 1, 2010 12:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 2,500, Visits: 2,381
I disagree: the query returns all rows that contains the word 'order' in the column [text] that may appear in comments or definition.
Post #1028436
Posted Wednesday, December 1, 2010 12:14 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, March 25, 2013 4:03 AM
Points: 850, Visits: 71
Good Question! Keep questioning................

Regards,
Jagan.
Post #1028438
Posted Wednesday, December 1, 2010 12:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 13,605, Visits: 10,484
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1028443
Posted Wednesday, December 1, 2010 12:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:51 AM
Points: 1,854, Visits: 3,451
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"
Post #1028445
Posted Wednesday, December 1, 2010 12:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:47 AM
Points: 2,840, Visits: 3,872
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
Post #1028448
Posted Wednesday, December 1, 2010 1:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:47 AM
Points: 2,840, Visits: 3,872
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
Post #1028451
Posted Wednesday, December 1, 2010 1:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:51 AM
Points: 1,854, Visits: 3,451
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.
Post #1028452
Posted Wednesday, December 1, 2010 1:08 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
Post #1028454
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse