View system object definitions


You’ve probably all noticed that when you right click on a user database object in SSMS object explorer that you get a context menu. One of the options in that context menu gives us the ability to script the object out.

However, try the same thing on an object belonging to a system database and you’ll find the option to script the object is not in the context menu at all.

This may lead us to the conclusion that we are unable to view the definitions of system database objects in SSMS.

Not so.

There is a very helpful function that lets us view the definition for most system objects, and that is OBJECT_DEFINITION

Admittedly the TSQL returned is not formatted – but hey, you’ll just need to do that yourself!

I think that it has great educational value to look at the meta data of system objects and learn from them.

oh, and this function can be used to get the definition of any object, it’s not limited to system objects at all.

have a good day.



Tags: ,

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.