SQL Server System View Definitions

Question

Is there a way to view the system view definitions in SSMS? I don't see how this is possible using SQL Server Management Studio.

Example of view definition I want to see: [sys].[all_sql_modules].

Thank you

Edit:

This first query works well, but the view I want to see does not show up in this query.

select top 100000
    object_definition(a.[object_id]) as [object_definition_results],
    b.[name] as [object_name],
    b.[type],
    case
        when b.[type] = 'P'  then 'SQL Stored Procedure'
        when b.[type] = 'RF' then 'Replication-filter-procedure'
        when b.[type] = 'V'  then 'View'
        when b.[type] = 'TR' then 'SQL DML Trigger'
        when b.[type] = 'FN' then 'SQL Scalar Function'
        when b.[type] = 'IF' then 'SQL inline table-valued function'
        when b.[type] = 'TF' then 'SQL Table-Valued Function'
        when b.[type] = 'R'  then 'Rule (old-style, stand-alone)'
        else ''
        end as [Object Type Description]
from
    [sys].[sql_modules] as a
inner join
    [sys].[objects] as b
    on
        a.[object_id] = b.[object_id]
order by
    [Object Type Description];

I have also tried this, but it seems like that view isn't even in the object list. Why would this be?

select top 10000
    a.*
from
    [sys].[objects] as a
where
    a.[name] like '%sql%module%'
order by
    a.[name];

Answer 1

It does seem odd to me that they thought it was useful to remove scripting options from the context menu for system / catalog views in Object Explorer, but they left in the option to Edit Top 200 Rows. I think someone got something backward.

In a lot of cases, you can get it in one of two ways:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.all_sql_modules'));

SELECT definition FROM sys.all_sql_modules
  WHERE [object_id] = OBJECT_ID(N'sys.all_sql_modules');

Sometimes, though, the definition of a system object is not in master (rather it is in the resource database), can only be viewed using the DAC connection, or is not in SQL Server at all (think xp_cmdshell).

In order to see the full definition of a procedure you'll need to be in results to text (results to grid will put the whole body on one line), likely need to change at least the default results to text output to its maximum of 8192 characters and, if that's not enough, switch back to grid and convert to XML.

Someone will surely suggest sp_helptext, but that doesn't always work; also, its output is wonky.