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


Upload Image Close it
Select File

Welcome to my blog. I work as Database lead at Synaptic Digital. Hope you find some interesting stuff here.
Browse by Tags · View All
BRH 17
SQL Server 15
#SQL Server 11
#BI 10
#TSQL 8
TSQL 8
BI 7
SSRS 6
#SQLServer 6
SSRS 2008R2 5

Archive · View All
January 2011 6
December 2010 5
September 2012 4
May 2012 4
March 2011 4
November 2012 2
October 2012 2
January 2012 2
February 2011 2
November 2010 2

sp_helptext v/s OBJECT_DEFINITION

Sep 10 2012 12:00AM by Chintak Chhapia   

Today, My friend Nakul has done a nice comparison of sp_helptext v/s OBJECT_DEFINITION in this post. As Nakul's other posts this post is also very well documented and easy to understand.

Before reading this post I was not aware that sp_helptext can be used to get definition of check constraints, defaults and computed columns.

But, below is the reason why I still prefer OBJECT_DEFINITION over sp_helpText.

Create one dummy stored procedure

use tempdb
go
Create procedure dbo.proc_dummyHighWidth
as
begin
	select * from dbo.dummy cross apply dbo.dummy cross apply dbo.dummy cross apply dbo.dummy cross apply dbo.dummy cross apply dbo.dummy cross apply dbo.dummy cross apply dbo.dummy cross apply dbo.dummy cross apply dbo.dummy cross apply dbo.dummy cross apply dbo.dummy cross apply dbo.dummy cross apply dbo.dummy cross apply dbo.dummy 
end
go

Now, run the below code

exec sp_helpText 'dbo.proc_dummyHighWidth'
select OBJECT_DEFINITION(object_id('dbo.proc_dummyHighWidth'))

When you run the above code hp_helpText will wrap the code after 256 chars, you can clearly see why it’s warped after 256 char if you look at the definition of sp_helptext

use master;
exec sp_helptext 'sp_helptext'

In definition of sp_helptext there is a defined length of 255 chars, which is causing the text to warp. As per best practice we should not have text wider than 150 chars, but many of us are working with legacy code or a developer who has just begun their career. So, if there a wide text in module definition sp_helpText wrapping can cause some discomfort.  Because of this reason I feel that it’s better to use OBJECT_DEFINITION then sp_helpText, even if we need to write some more chars.

Tags: SQL, sp_helptext, object_definition


Chintak Chhapia
40 · 5% · 1477
4
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • 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?
    commented on Sep 11 2012 10:18PM
    Marc Jellinek
    96 · 2% · 586
  • Mark,
    
    There is no issue in using the sys.sql_modules directly, internally it also use OBJECT_DEFINITION(). 
    Infect you can get some more information from sys.sql_modules at cost of little more typing. 
    Also Typing can be ignored by creating shortcuts, to me there is no issue in using sys.sql_modules. 
    
    Thanks for nice trick of putting comment inside pre block.
    
    --Chintak.
    commented on Sep 12 2012 12:58AM
    Chintak Chhapia
    40 · 5% · 1477

Your Comment


Sign Up or Login to post a comment.

"sp_helptext v/s OBJECT_DEFINITION" rated 5 out of 5 by 4 readers
sp_helptext v/s OBJECT_DEFINITION , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]