{"id":546,"date":"2016-01-05T00:01:26","date_gmt":"2016-01-05T06:01:26","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=546"},"modified":"2016-01-21T16:43:56","modified_gmt":"2016-01-21T22:43:56","slug":"i-am-only-here-to-help-object_definition","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=546","title":{"rendered":"I am Only Here to Help &#8211; OBJECT_DEFINITION()"},"content":{"rendered":"<p>Many people are familiar the Windows Command Prompt. You can open one. type &#8220;HELP&#8221; and click enter.\u00a0It will return descriptions of various OS commands.\u00a0\u00a0You can \u00a0type &#8220;-?&#8221; after a command and get a list on all the switches that can be used with that command. Go ahead and try it (<strong>sqlcmd\u00a0 -?)<\/strong>The system is simply returning metadata.SQL has several commands that return metadata about system and user objects. We will look at the <strong><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms176090.aspx\" target=\"_blank\"><span style=\"color: #0066cc;\">OBJECT_DEFINITION() <\/span><\/a><\/strong>command.<\/p>\n<div id=\"attachment_569\" style=\"width: 463px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-569\" class=\"size-full wp-image-569\" src=\"https:\/\/jackdonnell.com\/wp-content\/uploads\/2016\/01\/CommandlineHelp.gif\" alt=\"Windows Command Prompt and HELP command.\" width=\"453\" height=\"147\" \/><p id=\"caption-attachment-569\" class=\"wp-caption-text\">Windows Command Prompt and HELP command.<\/p><\/div>\n<p>From the link we learn that\u00a0it &#8220;Returns the Transact-SQL source text of the definition of a specified object.&#8221; We all know you can right click on an object in SSMS and script it out. Why use this? Output to SSMS has a limit on how much can be returned at a time. Large object like procedures can be thousands of lines long and will be truncated. \u00a0AH!<\/p>\n<p>Beside of the nerd cool factor of being able to return source code, it can be handy.\u00a0 One use is to search for the occurrences of specific objects or syntax within objects. Maybe you want to see what objects reference a certain table or maybe find objects that use a specific <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms181714.aspx\" target=\"_blank\"><strong>QUERY HINT<\/strong>.<\/a><\/p>\n<p>OBJECT_DEFINITION applies to the following object types:<\/p>\n<ul class=\"unordered\">\n<li>C = Check constraint<\/li>\n<li>D = Default (constraint or stand-alone)<\/li>\n<li>P = SQL stored procedure<\/li>\n<li>FN = SQL scalar function<\/li>\n<li>R = Rule<\/li>\n<li>RF = Replication filter procedure<\/li>\n<li>TR = SQL trigger (schema-scoped DML trigger, or DDL trigger at either the database or server scope)<\/li>\n<li>IF = SQL inline table-valued function<\/li>\n<li>TF = SQL table-valued function<\/li>\n<li>V = View<\/li>\n<\/ul>\n<p><!--more--><\/p>\n<p>This first few examples show metadata for a procedure, a view and a function. Since I do not want to look up the object id and pass it to the command, I use <strong><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms190328.aspx\" target=\"_blank\">OBJECT_ID()<\/a> <\/strong>to do the lookup and pass the id at the same time. You will see how this is pretty common when using commands like these.<\/p>\n<pre lang=\"tsql\">USE [AdventureWorks]\r\nGO\r\n\r\nSET NOCOUNT ON\r\nGO\r\n-- Return SQL code of specific objects. Examples: Procedures, Views and Functions\r\nSELECT OBJECT_DEFINITION(OBJECT_ID('dbo.uspGetEmployeeManagers')) [Procedure dbo.uspGetEmployeeManagers]\r\nGO\r\nSELECT OBJECT_DEFINITION(OBJECT_ID('HumanResources.vEmployee')) [View HumanResources.vEmployee]\r\nGO\r\nSELECT OBJECT_DEFINITION(OBJECT_ID('dbo.ufnGetContactInformation')) [Function dbo.ufnGetContactInformation]\r\nGO<\/pre>\n<div id=\"attachment_570\" style=\"width: 612px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-570\" class=\"size-full wp-image-570\" src=\"https:\/\/jackdonnell.com\/wp-content\/uploads\/2016\/01\/OBJECT_DEFINE_OBJECTS.gif\" alt=\"Sample results of OBJECT_DEFINITION() commands\" width=\"602\" height=\"189\" \/><p id=\"caption-attachment-570\" class=\"wp-caption-text\">Sample results\u00a0for OBJECT_DEFINITION() commands<\/p><\/div>\n<p>&nbsp;<\/p>\n<p>Let&#8217;s build upon that use <strong><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms176090.aspx\" target=\"_blank\"><span style=\"color: #0066cc;\">OBJECT_DEFINITION() <\/span><\/a><\/strong>to not only return the data, but use it to find references to\u00a0the HumanResource.Employee table. You can use it to find any text within the source code. It does not have to be an object name. I have used it\u00a0 to look for references to a specific developer, a call to a store procedure , TRUNCATE commands and even to find UPDATE STATISTICS commands. I think this can be useful for developers and DBAs. Take a look at <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms176105.aspx\" target=\"_blank\"><strong>OBJECTPROPERTY()<\/strong><\/a> to further refine your search.<\/p>\n<pre lang=\"tsql\">-- Search code for an Objects that use [HumanResource].[Employee]\r\nSELECT name [ObjectName]\r\n,type [Object_type]\r\n,OBJECT_DEFINITION(id) [Code]\r\nFROM sysobjects WHERE OBJECT_DEFINITION(id) like '%[HumanResource].[Employee]%'\r\nORDER BY name\r\n<\/pre>\n<div id=\"attachment_571\" style=\"width: 681px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-571\" class=\"size-full wp-image-571\" src=\"https:\/\/jackdonnell.com\/wp-content\/uploads\/2016\/01\/OBJECT_DEFINE_OBJECTS_Search.gif\" alt=\"Sample result using OBJECT_DEFINITION() for searches.\" width=\"671\" height=\"209\" \/><p id=\"caption-attachment-571\" class=\"wp-caption-text\">Sample results using OBJECT_DEFINITION() for searches.<\/p><\/div>\n<p>There is an alternate way to find the information using a join to the sys.syscomments table.<\/p>\n<pre lang=\"tsql\">-- Example using JOIN to sys.syscomments\r\n-- If the search criteria shows up more than once in the code the it will return duplicat duplicate rows when DISTINCT is not used.\r\nSELECT DISTINCT a.name [ObjectName]\r\n,a.type [Object_type]\r\n,OBJECT_DEFINITION(a.id) [Code]\r\nFROM sys.sysobjects as a\r\nJOIN sys.syscomments as b\r\non a.id = b.id\r\nWHERE b.text like '%[HumanResource].[Employee]%'\r\nORDER BY a.name\r\n<\/pre>\n<p>Hope this has been useful. I plan to do more posts related to some of the helpful function and various help stored procedures in the near future.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Many people are familiar the Windows Command Prompt. You can open one. type &#8220;HELP&#8221; and click enter.\u00a0It will return descriptions of various OS commands.\u00a0\u00a0You can \u00a0type &#8220;-?&#8221; after a command and get a list on all the switches that can &hellip;<\/p>\n<p class=\"read-more\"><a href=\"https:\/\/jackdonnell.com\/?p=546\">Read more &raquo;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[273,266,22,351,5],"tags":[260,374,372,373,315,35,295,52,408,409,291,407,389],"class_list":["post-546","post","type-post","status-publish","format-standard","hentry","category-administration","category-dba","category-sql-server","category-ssms","category-t-sql","tag-developer","tag-metadata","tag-object_definition","tag-object_id","tag-query","tag-select","tag-sql-server","tag-ssms","tag-sys-comments","tag-sys-objects","tag-t-sql","tag-trigger","tag-update-statistics"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/546","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=546"}],"version-history":[{"count":14,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/546\/revisions"}],"predecessor-version":[{"id":642,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/546\/revisions\/642"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=546"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=546"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=546"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}