#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:


image


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:


image


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.


image


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:


image


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



Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

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

  1. Chintak Chhapia

    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

    Like

    Reply
  2. Abhishek Bandi

    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.

    Like

    Reply
  3. Nakul Vachhrajani

    @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

    Like

    Reply
  4. marc_jellinek@hotmail.com
    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?

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

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