#0194-SQL Server-How to get the definition/script for a database object using T-SQL?

The moment you would have read the title of this post, you would have thought – “What’s so special in this post?” Well, did you know that there are not one, not two, but at least three (3) distinct methods that you can use to get the definition/script for any database object in Microsoft SQL Server using T-SQL?

Surprised? Read on…

Method #01 – sys.syscomments (obsolete)

For anyone who has been around since (or perhaps before) the days of SQL 2000, sys.syscomments must be familiar. This system table contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. Here’s how one would use it:

SELECT sc.text

FROM sys.syscomments AS sc

INNER JOIN sys.objects AS so ON sc.id = so.object_id

WHERE so.name = ‘uspUpdateEmployeePersonalInfo’

  AND so.schema_id = SCHEMA_ID(‘HumanResources’)

GO

For the purposes of this demonstration, I have switched the query results output to Text and changed the max. number of characters for text output to 8000. The output is shown below:

System tables have already been marked for deprecation in future releases of Microsoft SQL Server. Hence, starting SQL Server 2005, it is no longer advised to use the sys.syscomments system table.

Method #02 – sp_helptext

One of the drawbacks of using sys.comments is that the formatting of the script may be lost. Another hot favourite – a system stored procedure sp_helptext, returns a fairly formatted version of the script. Here’s how to get the definition for HumanResources.uspUpdateEmployeePersonalInfo.

USE AdventureWorks2012
GO

EXEC sp_helptext ‘HumanResources.uspUpdateEmployeePersonalInfo’
GO
/*Alternate methods:
EXEC sp_helptext [HumanResources.uspUpdateEmployeePersonalInfo]
GO

–The following does not work, both the schema name and the object name need to be one identifier:
EXEC sp_helptext [HumanResources].[uspUpdateEmployeePersonalInfo]
GO
*/

The output looks like the one shown below:

sp_helptext continues to use the sys.syscomments system table. However, the only good thing about using a system stored procedure rather than directly querying a system table is that whenever Microsoft changes the implementation of sp_helptext, any existing scripts that use the system stored procedure would not need to change.

Method #03(A) – OBJECT_DEFINITION()

This T-SQL function returns the object data using the meta-data information associated with the specified object. Instead of worrying about using the schema name and the object name as one identifier, the usage here is much simpler because one has to deal only with the OBJECT_ID. Getting the definition of any object is as simple as executing a simple SELECT statement now:

USE AdventureWorks2012

GO

SELECT OBJECT_DEFINITION(OBJECT_ID(‘HumanResources.uspUpdateEmployeePersonalInfo’)) AS ObjectText

GO

The result is as shown below. OBJECT_DEFINITION does not query sys.syscomments, instead directly queries the metadata associated to the database.

sp_helptext v/s OBJECT_DEFINITION: A Comparison

Given that sys.syscomments is obsolete and should not be used, I believe it would be a good time to compare the two remaining options – sp_helptext and OBJECT_DEFINITION.

Object type	sp_helptext	OBJECT_DEFINITION
CHECK constraint	Y	Y
Default (or stand-alone) constraint	Y	Y
Default, unencrypted stored procedure	Y	Y
SQL Scalar functions	Y	Y
Rules	Y	Y
Replication filter procedures	Y	Y
Triggers	Y	Y
In-line TVFs	Y	Y
Multi-line TVFs	Y	Y
Views	Y	Y
Computed Columns	Y	N

All is well until the computed columns show up. OBJECT_DEFINITION clearly lags behind sp_helptext in this area.

Method #03(B) – Using sys.computed_columns

To get the definition of computed columns, a separate metadata visibility view exists, named the sys.computed_columns. Here’s a sample implementation:

USE AdventureWorks2012

GO

SELECT object_id,

       name,

       definition,

       column_id,

       system_type_id,

       max_length,

       precision,

       scale,

       collation_name

FROM sys.computed_columns

GO

The results is as shown below:

Now that we have seen all the 3 methods that I know of, I would like to say that my favourite one (probably out of habit) is sp_helptext. The question to you is:

Which method do you use to fetch the object definition?

Do leave a note as you go.

EDIT [09/10/2012]: My dear friend, Chintak just wrote a post extending this topic. It’s a great read and you can find it here: http://beyondrelational.com/modules/2/blogs/115/posts/17659/sphelptext-vs-objectdefinition.aspx

[END EDIT]

References

sys.syscomments: http://msdn.microsoft.com/en-us/library/ms186293.aspx
sp_helptext: http://msdn.microsoft.com/en-us/library/ms176112.aspx
OBJECT_DEFINITION: http://msdn.microsoft.com/en-us/library/ms176090.aspx
sys.computed_columns: http://msdn.microsoft.com/en-us/library/ms188744.aspx

Until we meet next time,

Be courteous. Drive responsibly.

8 thoughts on “#0194-SQL Server-How to get the definition/script for a database object using T-SQL?”

Chintak Chhapia September 10, 2012 at 1:51 AM

Nakul,

Nice read.

My preferred method is to use Object_definition, below is the reason
[http://beyondrelational.com/modules/2/blogs/115/posts/17659/sphelptext-vs-objectdefinition.aspx][1]

One more thing to note that sp_helpText internally use syscomments:)

Some more views to get definition

select definition,* from sys.all_sql_modules

-Chintak.

[1]: http://beyondrelational.com/modules/2/blogs/115/posts/17659/sphelptext-vs-objectdefinition.aspx

LikeLike

Reply ↓

a.diniz September 10, 2012 at 3:50 AM

Hi Nakul

This is great. How do the required permission levels compare for each method?

LikeLike

Reply ↓

Abhishek Bandi September 10, 2012 at 8:45 AM

Hi nakul,

nice post.

there is one drawback using sp_helptext or OBJECT_DEFINITION
When we rename any object from **object explorer** or using sp_rename proc,
it wont give correct name of the object until we alter the proc.

the sample example is below.

**Creating sample Proc.**

create proc old
as
begin
select * from emp
end

Rename the object name **old** to ***new*** ,using Object Explorer (Select the object and right click >> Rename or F2)

After refreshing stored procedure folder we can see new stored procedure in the list.

exec sp_helptext new
select OBJECT_DEFINITION(OBJECT_ID(‘new’))

If we observe the result, the statement is
**Result:**

create proc old
as
begin
select * from emp
end

**Result set 2:**

create proc old as begin select * from emp end

**Observation**: Actual proc name is **new** but the result shows as **old** only.

**Using Sp_rename proc:**

exec sp_rename new ,verynew

If we refresh the **stored procedures** *folder* in **Object Explorer** we can see object with **verynew.**

exec sp_helptext verynew
select OBJECT_DEFINITION(OBJECT_ID(‘verynew’))

Result set is :

create proc old
as
begin
select * from emp
end

Result set 2:

create proc old as begin select * from emp end

Still it showing result with **old** name.

Next step ,

Right click on the **verynew** proc and select modify, we get the alter proc script.
Execute the statement and run the below query.

exec sp_helptext verynew
select OBJECT_DEFINITION(OBJECT_ID(‘verynew’))

The content is refreshed and displaying with **verynew** name.

LikeLike

Reply ↓

Nakul Vachhrajani September 10, 2012 at 9:05 AM

@abhIShek BandI: I don’t think there is a drawback.

Reason: Renaming a stored procedure using `sp_rename` or by any other method is not recommended by Microsoft ([http://msdn.microsoft.com/en-us/library/ms188351.aspx][1]).

> Renaming a stored procedure, function,
> view, or trigger will not change the
> name of the corresponding object name
> in the definition column of the
> sys.sql_modules catalog view.
> Therefore, we recommend that sp_rename
> not be used to rename these object
> types. Instead, drop and re-create the
> object with its new name.

The ideal way to rename a stored procedure is to drop the old procedure and recreate the new one.

[1]: http://msdn.microsoft.com/en-us/library/ms188351.aspx

LikeLike

Reply ↓

Abhishek Bandi September 10, 2012 at 11:16 AM

Ok…

Thanks for the link and info Nakul… 🙂

LikeLike

Reply ↓

marc_jellinek@hotmail.com September 11, 2012 at 10:25 PM

I understand using OBJECT_DEFINITION() for tables and views, 
but most other object definitions are stored, as-is, in [sys].[sql_modules] 
in the [definition] column.  Why not just query it directly?

LikeLike

Reply ↓