Click to Rate and Give Feedback
MSDN
MSDN Library
SQL Server
SQL Server 2008
Database Engine
Technical Reference
 OBJECT_DEFINITION (Transact-SQL)
Community Content
In this section
Statistics Annotations (0)
Collapse All/Expand All Collapse All
Other versions are also available for the following:
SQL Server 2008 Books Online (October 2009)
OBJECT_DEFINITION (Transact-SQL)

Returns the Transact-SQL source text of the definition of a specified object.

Topic link icon Transact-SQL Syntax Conventions

OBJECT_DEFINITION ( object_id )
object_id

Is the ID of the object to be used. object_id is int, and assumed to represent an object in the current database context.

nvarchar(max)

Returns NULL on error or if a caller does not have permission to view the object.

A user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECT_DEFINITION may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration and Troubleshooting Metadata Visibility.

The SQL Server Database Engine assumes that object_id is in the current database context. The collation of the object definition always matches that of the calling database context.

OBJECT_DEFINITION applies to the following object types:

  • C = Check constraint
  • D = Default (constraint or stand-alone)
  • P = SQL stored procedure
  • FN = SQL scalar function
  • R = Rule
  • RF = Replication filter procedure
  • TR = SQL trigger (schema-scoped DML trigger, or DDL trigger at either the database or server scope)
  • IF = SQL inline table-valued function
  • TF = SQL table-valued function
  • V = View

System object definitions are publicly visible. The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION. These permissions are implicitly held by members of the db_owner, db_ddladmin, and db_securityadmin fixed database roles.

A. Returning the source text of a user-defined object

The following example returns the definition of a user-defined trigger, uAddress, in the Person schema. The built-in function OBJECT_ID is used to return the object ID of the trigger to the OBJECT_DEFINITION statement.

USE AdventureWorks;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID(N'Person.uAddress')) AS [Trigger Definition]; 
GO

B. Returning the source text of a system object

The following example returns the definition of the system stored procedure sys.sp_columns.

USE AdventureWorks;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID(N'sys.sp_columns')) AS [Object Definition];
GO
Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
Processing
© 2009 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks | Privacy Statement | Site Feedback
Page view tracker