Friday, November 12, 2010

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'))

CREATE VIEW sys.service_queues AS  

       ,convert(smallint, AS max_readers
       ,convert(nvarchar(776), v.value) COLLATE database_default AS activation_procedure
       ,e.indepid AS execute_as_principal_id
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.

No comments:

Post a Comment