Madhivanan's TSQL Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

Browse by Tags · View All

sql_server	217
t-sql	211
tsql	116
sqlserver	96
BRH	78
#SQLServer	66
#TSQL	56
SQL Server	34
function	11
SSMS	9

Archive · View All

August 2007	17
August 2010	8
June 2012	7
June 2011	7
November 2007	7
August 2012	6
May 2012	6
November 2011	6
August 2011	6
October 2011	6

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (389) · Preferences

routine_definition from information_schema.routines

Feb 2 2010 2:35AM by Madhivanan

Some people suggest usage of information_schema.routines View to view the definition of the procedure The problem is the datatype of that column is nvarchar(4000) and there is a chance that the definition gets truncated. So, the following wont give you full text of the definition

select routine_definition from information_schema.routines
where routine_name='your_procedure'

The following methods will give the full definition

--Method 1
select object_definition(object_id('your_procedure'))

--Method 2
EXEC sp_helptext 'your_procedure'

Tags: t-sql, sql_server, script, procedure,

Previous: Find alternate week day
Next: Converting Hexadcimal to decimal

Madhivanan

3 · 39% · 12472

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

4 Comments

I prefer to use sys.sql_modules instead where you've nvarchar(max) column storing full definition

select definition from sys.sqlmodules where OBJECTNAME(object_id) = 'your procedure'

And of course you should be using SQL 2005 or above

commented on Feb 2 2010 6:21AM

visakhm

187 · 1% · 248

Yes, you can use

But note that the methods specified above doesn't use any system tables

Also method 2 works in all the versions

commented on Feb 2 2010 7:21AM

Madhivanan

3 · 39% · 12472

sys.sql_modules is not a system table but it is a catalog view

commented on Feb 6 2010 11:28AM

visakhm

187 · 1% · 248

Yes. I forgot that. Thanks

commented on Feb 11 2010 12:39AM

Madhivanan

3 · 39% · 12472

Your Comment

Sign Up or Login to post a comment.

+Madhivanan