Is it possible to modify system stored procedure?

Question

I would like to alter sp_helpindex system procedure, or rather replace it with my version.

The reasons are:

• show index_id
• show included columns
• show filter definition
• show fill factor
• show index sizes
• show heap info as well

Also, interestingly enough my version turned out to be better performing (faster, fewer reads, no cursor).

I tried the following, but none of these worked:

• sp_rename
• alter procedure
• drop procedure

The error Invalid object name 'sys.sp_helpindex'.

The only option I can think of right now is just to add a new procedure instead of replacing existing. However, there is one disadvantage. I was hoping that sp_help (which in turn calls sp_helpindex) would pick up my change seamlessly and as a result everybody who uses Alt+F1 on the server would see the change without changing default SSMS key shortcut on every client.

Answer 1

No, you can't modify any system procedure. You can create your own and call it instead; you just have to give it a different sp_ name, mark it as a system object, and not put it in the sys schema.

Oh, and instead of creating your own from scratch, Kimberly Tripp has constantly been evolving her own version, updated for new features and of course you should grab Kendra Little's sp_BlitzIndex. You might find that you don't need to write anything, because plenty of other people have already reinvented that wheel, and done it quite well.