sys.Syscomments not showing all the rows

Question

Post reply

sys.Syscomments not showing all the rows

Sandeep Saretha

SSChasing Mays

Points: 647
More actions
May 13, 2009 at 1:32 pm

#130093

I am not able to see all the rows in syscomments table. When I login as sa, I can more rows.
SQL Server 2005 Developer Edition.
TIA

Viewing 7 posts - 1 through 6 (of 6 total)

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

syscomments is only available for backward compatibility. Try sys.sql_modules instead:

SELECT

OBJECT_NAME(object_id),

*

FROM sys.sql_modules

Greets

Flo

Sandeep Saretha SSChasing Mays Points: 647 More actions · Answer 2

Thanks,

I know I can use sys.sql_modules but still I am Perplexed why sys.syscomments is not showing all rows.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

I cannot reproduce this effect on my system. Which kind of objects are you missing?

Sandeep Saretha SSChasing Mays Points: 647 More actions · Answer 4

Lets say you have 2 logins. One with DBO and another without

DBO can see more rows than other login.

TIA

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

My miss. I didn't read the "sa" part correct...

It's a security feature. Users can only see the object definition with correct permissions. Same depends on sys.sql_modules, OBJECTDEFINITION, INFORMATION_SCHEMA.ROUTINES and some more system views.

Further information:

http://msdn.microsoft.com/en-us/library/ms187113(SQL.90).aspx

Greets

Flo

Sandeep Saretha SSChasing Mays Points: 647 More actions · Answer 6

Thanks,

GRANT VIEW ANY DEFINITION TO User1

This will give permission to see all rows to use1 in metadata and catalog view.