How to Display Object Definitions

Sometimes it’s necessary to see the definitions of views, functions or stored procedures. These definitions can easily be found using the following query:

SELECT TOP 20

       o.object_id,

       o.type_desc,

       s1.name AS schema_name,

       o.name,

       s.definition,  — This column contains the definition

       o.principal_id,

       o.schema_id,

       o.parent_object_id,

       o.type,

       o.create_date,

       o.modify_date,

       o.is_ms_shipped,

       o.is_published,

       o.is_schema_published,

       s.uses_ansi_nulls,

       s.uses_quoted_identifier,

       s.is_schema_bound,

       s.uses_database_collation,

       s.is_recompiled,

       s.null_on_null_input,

       s.execute_as_principal_id

FROM sys.sql_modules AS s

JOIN sys.objects AS o

       ON o.object_id = s.object_id

JOIN sys.schemas AS s1

       ON s1.schema_id = o.schema_id

 

The definition columns contains the tekst that makes up the object. Of course, you can filter the result by object name (o.name), or create a table valued function that accepts a name as a parameter if you like. I hope you like this.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s