Many people are familiar the Windows Command Prompt. You can open one. type “HELP” and click enter. It will return descriptions of various OS commands. You can type “-?” after a command and get a list on all the switches that can be used with that command. Go ahead and try it (sqlcmd -?)The system is simply returning metadata.SQL has several commands that return metadata about system and user objects. We will look at the OBJECT_DEFINITION() command.
Windows Command Prompt and HELP command.
From the link we learn that it “Returns the Transact-SQL source text of the definition of a specified object.” 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. AH!
Beside of the nerd cool factor of being able to return source code, it can be handy. 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 QUERY HINT.
OBJECT_DEFINITION applies to the following object types:
- C = Check constraint
- D = Default (constraint or stand-alone)
- P = SQL stored procedure
- FN = SQL scalar function
- R = Rule
- RF = Replication filter procedure
- TR = SQL trigger (schema-scoped DML trigger, or DDL trigger at either the database or server scope)
- IF = SQL inline table-valued function
- TF = SQL table-valued function
- V = View
Read more »
I am Only Here to Help – OBJECT_DEFINITION()
Many people are familiar the Windows Command Prompt. You can open one. type “HELP” and click enter. It will return descriptions of various OS commands. You can type “-?” after a command and get a list on all the switches that can be used with that command. Go ahead and try it (sqlcmd -?)The system is simply returning metadata.SQL has several commands that return metadata about system and user objects. We will look at the OBJECT_DEFINITION() command.
Windows Command Prompt and HELP command.
From the link we learn that it “Returns the Transact-SQL source text of the definition of a specified object.” 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. AH!
Beside of the nerd cool factor of being able to return source code, it can be handy. 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 QUERY HINT.
OBJECT_DEFINITION applies to the following object types:
Read more »