Post reply

Modify sys.sp_columns system stored procedure

  • January 9, 2008 at 2:22 am

    #377899

    Hi all,

    I am using a lot the system stored procedure sys.sp_columns for retrieving the data type of the columns for a specific table in my database.

    Now, what i was trying to do is to modify the system stored procedure 😎 , but i could not achieve this.

    All i need is that the stored procedure returns less columns in the result set.

    Is there a way to do this?

    The error i get is:

    Msg 208, Level 16, State 6, Procedure sp_columns, Line 108

    Invalid object name 'sys.sp_columns'.

    :ermm:

    Any help would be greatly appreciated 🙂

    Regards,

    Oana.

  • January 9, 2008 at 2:41 am

    #766062

    You can't modify the system objects.

    You're probably best off writing your own query against the catalog views sys.columns, sys.types, sys.tables...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 9, 2008 at 2:51 am

    #766064

    I see ...

    But then why the Modify option is enabled in SQL Server 2005? Is there a reason?

    Regards,

    Oana.

  • January 9, 2008 at 7:04 am

    #766153

    Just a bit of a gui glitch. You really can not edit those procedures.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 9, 2008 at 7:28 am

    #766184

    Thanks for your replies.

    Regards,

    Oana.

  • January 9, 2008 at 8:42 am

    #766252

    Also note that the system procs aren't actually in the user databases, though they appear to be. They're in the hidden resource database.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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