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
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 