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
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 OBJECT_ID() 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.
USE [AdventureWorks]
GO
SET NOCOUNT ON
GO
-- Return SQL code of specific objects. Examples: Procedures, Views and Functions
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.uspGetEmployeeManagers')) [Procedure dbo.uspGetEmployeeManagers]
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('HumanResources.vEmployee')) [View HumanResources.vEmployee]
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.ufnGetContactInformation')) [Function dbo.ufnGetContactInformation]
GO
Sample results for OBJECT_DEFINITION() commands
Let’s build upon that use OBJECT_DEFINITION() to not only return the data, but use it to find references to the 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 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 OBJECTPROPERTY() to further refine your search.
-- Search code for an Objects that use [HumanResource].[Employee]
SELECT name [ObjectName]
,type [Object_type]
,OBJECT_DEFINITION(id) [Code]
FROM sysobjects WHERE OBJECT_DEFINITION(id) like '%[HumanResource].[Employee]%'
ORDER BY name
Sample results using OBJECT_DEFINITION() for searches.
There is an alternate way to find the information using a join to the sys.syscomments table.
-- Example using JOIN to sys.syscomments
-- If the search criteria shows up more than once in the code the it will return duplicat duplicate rows when DISTINCT is not used.
SELECT DISTINCT a.name [ObjectName]
,a.type [Object_type]
,OBJECT_DEFINITION(a.id) [Code]
FROM sys.sysobjects as a
JOIN sys.syscomments as b
on a.id = b.id
WHERE b.text like '%[HumanResource].[Employee]%'
ORDER BY a.name
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.
Tiny URL for this post:
Comments are closed.