Home About Us SQL Interview Book Contact Us RSS
Articles
Tools
Code Snippets
Links
Tips & Tricks
FAQ
Resources
Articles
Code Snippets
Links
FAQ
Resources

Saravana Kumar
Vinod Kumar


Finding Object Definition in SQL Server 2005

Any database requirement would be to view the created objects definition. And SQL Server is no different here. And in the SQL Server 2000 world we used to use the simple system stored procedure as sp_helptext to view the definition. For all backward compatibility this command still works for all the user defined objects wihtout any problems .

But the moment we try to find out the definition for system objects this fails in SQL Server 2005.NOTE: This used to work like a gem in SQL Server 2000. The error for the same would be like below:

 

So your immediate question would be "How do I access the same" !!! For every problem there is a command in Yukon. Hence, for this to work flawlessly use the Object_Definition command. This new syntax works great for all the objects (both system and user defined).

SELECT OBJECT_DEFINITION (OBJECT_ID ('sys.sp_help') )

Now this works flawlessly !!! Use the new syntax in Yukon hereon.