How to see object definition in sql server

Found this neat function today - it shows you the internals of what tables a view is hitting, or the definition of a stored proc (ie sp_who2).  For example running the following will return you the definition of the service broker view:

SELECT object_definition(OBJECT_ID('sys.service_queues'))

Yields:

CREATE VIEW sys.service_queues AS  

SELECT o.name

       ,o.object_id

       ,o.principal_id

       ,o.schema_id

       ,o.parent_object_id

       ,o.type

       ,o.type_desc

       ,o.create_date

       ,o.modify_date

       ,o.is_ms_shipped

       ,o.is_published

       ,o.is_schema_published

       ,convert(smallint, o.property) AS max_readers

       ,convert(nvarchar(776), v.value) COLLATE database_default AS activation_procedure

       ,e.indepid AS execute_as_principal_id

       ,o.is_activation_enabled

       ,o.is_receive_enabled

       ,o.is_enqueue_enabled

       ,o.is_retention_enabled  

FROM sys.objects$ o  

  LEFT JOIN sys.sysobjvalues v

    ON v.valclass = 12 AND v.objid = o.object_id AND v.subobjid = 0 AND v.valnum = 0 -- SVC_QUEUEACTIVPROC  

  LEFT JOIN sys.syssingleobjrefs e

    ON e.depid = o.object_id AND e.class = 22 AND e.depsubid = 0 -- SRC_OBJEXECASOWNER  

WHERE o.type = 'SQ'

 

 Very cool stuff.