In SQL Server 2005 or 2008 we can easily get the definition of user created functions, stored procedures as well as system views, procedures etc.
There are three ways to get the definition of system views,procedures as well as user created objects.
- Using OBJECT_DEFINITION function
- Using sys.system_sql_modules view ( Only returns system views,procedures etc)
- Using sp_helptext procedure
1) Using OBJECT_DEFINITION :
Using OBJECT_DEFINITION function we can get the source text of definition of the specified object.
Syntax:
OBJECT_DEFINITION function takes one parameter object_id. It is the ID of the object and it's datatype is int and assumed to represent an object in the current database context. It's return type is nvarchar(max).
Example of System Stored Procedure:
select OBJECT_DEFINITION(object_id('sp_databases'))
Output:
create procedure sys.sp_databases as set nocount on select DATABASE_NAME = db_name(s_mf.database_id), DATABASE_SIZE = convert(int, case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int... when convert(bigint, sum(s_mf.size)) >= 268435456 then null else sum(s_mf.size)*8 -- Convert from 8192 byte pages to Kb end), REMARKS = convert(varchar(254),null) from sys.master_files s_mf where s_mf.state = 0 and -- ONLINE has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access group by s_mf.database_id order by 1
Example of System Views:
We can get the definition of sys.tables system view as
select OBJECT_DEFINITION(object_id('sys.tables'))
Output:
CREATE VIEW sys.tables AS SELECT o.name, o.object_id, o.principal_id, o.schema_id, o.parent_object_id, o.type, o.type_desc, o.create_date, o.modify_date, o.is_ms_shipped, o.is_published, o.is_schema_published, lob.lobds AS lob_data_space_id, rfs.indepid AS filestream_data_space_id, o.property AS max_column_id_used, o.lock_on_bulk_load, o.uses_ansi_nulls, o.is_replicated, o.has_replication_filter, o.is_merge_published, o.is_sync_tran_subscribed, o.has_unchecked_assembly_data, lob.intprop AS text_in_row_limit, o.large_value_types_out_of_row, o.is_tracked_by_cdc, o.lock_escalation_option AS lock_escalation, ts.name AS lock_escalation_desc FROM sys.objects$ o LEFT JOIN sys.sysidxstats lob ON lob.id = o.object_id AND lob.indid <= 1 LEFT JOIN sys.syssingleobjrefs rfs ON rfs.depid = o.object_id AND rfs.class = 42 AND rfs.depsubid = 0 -- SRC_OBJTOFSDS LEFT JOIN sys.syspalvalues ts ON ts.class = 'LEOP' AND ts.value = o.lock_escalation_option WHERE o.type = 'U'
In similar manner we can get the definition of sys.columns, sys.views, sys.databases etc.
Now I will show you some examples using Adventure Works database.
Example of Stored Procedure:
select OBJECT_DEFINITION(object_id('uspGetEmployeeManagers'))
Output:
CREATE PROCEDURE [dbo].[uspGetEmployeeManagers] @EmployeeID [int] AS BEGIN SET NOCOUNT ON; WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [Title], [RecursionLevel]) -- CTE name and columns AS ( SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], 0 -- Get the initial Employee FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID] WHERE e.[EmployeeID] = @EmployeeID UNION ALL SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], [RecursionLevel] + 1 -- Join recursive member to anchor FROM [HumanResources].[Employee] e INNER JOIN [EMP_cte] ON e.[EmployeeID] = [EMP_cte].[ManagerID] INNER JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID] ) SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName], [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName' -- Outer select from the CTE FROM [EMP_cte] INNER JOIN [HumanResources].[Employee] e ON [EMP_cte].[ManagerID] = e.[EmployeeID] INNER JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID] ORDER BY [RecursionLevel], [ManagerID], [EmployeeID] OPTION (MAXRECURSION 25) END;
Example of View:
select OBJECT_DEFINITION(object_id('HumanResources.vEmployeeDepartment'))
Output:
CREATE VIEW [HumanResources].[vEmployeeDepartment] AS SELECT e.[EmployeeID] ,c.[Title] ,c.[FirstName] ,c.[MiddleName] ,c.[LastName] ,c.[Suffix] ,e.[Title] AS [JobTitle] ,d.[Name] AS [Department] ,d.[GroupName] ,edh.[StartDate] FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID] INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh ON e.[EmployeeID] = edh.[EmployeeID] INNER JOIN [HumanResources].[Department] d ON edh.[DepartmentID] = d.[DepartmentID] WHERE GETDATE() BETWEEN edh.[StartDate] AND ISNULL(edh.[EndDate], GETDATE());
Example of Table-valued function:
select OBJECT_DEFINITION(object_id('ufnGetContactInformation'))
Output:
CREATE FUNCTION [dbo].[ufnGetContactInformation](@ContactID int) RETURNS @retContactInformation TABLE ( [ContactID] int PRIMARY KEY NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [JobTitle] [nvarchar](50) NULL, [ContactType] [nvarchar](50) NULL ) AS BEGIN DECLARE @FirstName [nvarchar](50), @LastName [nvarchar](50), @JobTitle [nvarchar](50), @ContactType [nvarchar](50); SELECT @ContactID = ContactID, @FirstName = FirstName, @LastName = LastName FROM [Person].[Contact] WHERE [ContactID] = @ContactID; SET @JobTitle = CASE WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e WHERE e.[ContactID] = @ContactID) THEN (SELECT [Title] FROM [HumanResources].[Employee] WHERE [ContactID] = @ContactID) WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc INNER JOIN [Person].[ContactType] ct ON vc.[ContactTypeID] = ct.[ContactTypeID] WHERE vc.[ContactID] = @ContactID) THEN (SELECT ct.[Name] FROM [Purchasing].[VendorContact] vc INNER JOIN [Person].[ContactType] ct ON vc.[ContactTypeID] = ct.[ContactTypeID] WHERE vc.[ContactID] = @ContactID) WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc INNER JOIN [Person].[ContactType] ct ON sc.[ContactTypeID] = ct.[ContactTypeID] WHERE sc.[ContactID] = @ContactID) THEN (SELECT ct.[Name] FROM [Sales].[StoreContact] sc INNER JOIN [Person].[ContactType] ct ON sc.[ContactTypeID] = ct.[ContactTypeID] WHERE [ContactID] = @ContactID) ELSE NULL END; SET @ContactType = CASE WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e WHERE e.[ContactID] = @ContactID) THEN 'Employee' WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc INNER JOIN [Person].[ContactType] ct ON vc.[ContactTypeID] = ct.[ContactTypeID] WHERE vc.[ContactID] = @ContactID) THEN 'Vendor Contact' WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc INNER JOIN [Person].[ContactType] ct ON sc.[ContactTypeID] = ct.[ContactTypeID] WHERE sc.[ContactID] = @ContactID) THEN 'Store Contact' WHEN EXISTS(SELECT * FROM [Sales].[Individual] i WHERE i.[ContactID] = @ContactID) THEN 'Consumer' END; IF @ContactID IS NOT NULL BEGIN INSERT @retContactInformation SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType; END; RETURN; END;
2) Using sys.system_sql_modules view :
We can get the definition of all the system views, procedures by running the below query
select * from sys.system_sql_modules
-- This query will return 1880 rows having 9 Columns. I am showing only one row with two important columns.
Output:
object_id
-1073624922
definition
create procedure sys.sp_MSalreadyhavegeneration (@genguid uniqueidentifier, @subscribernick binary(6), @compatlevel int = 10) -- backward compatibility level, default=Sphinx as declare @nicknames varbinary(1000) declare @offset int declare @retcode int declare @dbname sysname -- Security Checking -- PAL users have access exec @retcode = sys.sp_MSrepl_PAL_rolecheck if (@retcode <> 0) or (@@error <> 0) return 1 if (@genguid is null) begin RAISERROR(14043, 16, -1, '@genguid', 'sp_MSalreadyhavegeneration') return (1) end select @nicknames = nicknames from dbo.MSmerge_genhistory where guidsrc = @genguid if @nicknames is null begin set @dbname= db_name() -- what is the appropriate error to return? RAISERROR(21333, 16, -1, @dbname) return (1) end if @compatlevel < 90 set @subscribernick= substring(@subscribernick,1,4) + 0x0000 declare @replnicklength int set @replnicklength= col_length('sysmergesubscriptions', 'replnickname') set @offset= 1 while @offset < DATALENGTH(@nicknames) begin -- If the subscriber nickname is already in the list just return if substring(@nicknames, @offset, @replnicklength) = @subscribernick return (0) set @offset = @offset + @replnicklength end -- Subscriber nickname is not in the array. Add it so that we won't send this gen -- back down in the return message. (Would waste bandwidth...) -- Append guard byte set @offset = DATALENGTH(@nicknames) if substring(@nicknames, @offset, 1) <> 0xFF begin set @nicknames = @nicknames + 0xFF end if @nicknames = 0x0 set @nicknames = @subscribernick + 0xFF else set @nicknames = @subscribernick + @nicknames update dbo.MSmerge_genhistory set nicknames = @nicknames where guidsrc = @genguid return (0)
We can also get the definition of only one view or procedure as
select definition from sys.system_sql_modules where [object_id] = ( select OBJECT_ID('sys.columns') )
Output:
CREATE VIEW sys.columns AS SELECT id AS object_id, name, colid AS column_id, xtype AS system_type_id, utype AS user_type_id, length AS max_length, prec AS precision, scale, convert(sysname, CollationPropertyFromId(collationid, 'name')) AS collation_name, sysconv(bit, 1 - (status & 1)) AS is_nullable, -- CPM_NOTNULL sysconv(bit, status & 2) AS is_ansi_padded, -- CPM_NOTRIM sysconv(bit, status & 8) AS is_rowguidcol, -- CPM_ROWGUIDCOL sysconv(bit, status & 4) AS is_identity, -- CPM_IDENTCOL sysconv(bit, status & 16) AS is_computed, -- CPM_COMPUTED sysconv(bit, status & 32) AS is_filestream, -- CPM_FILESTREAM sysconv(bit, status & 0x020000) AS is_replicated, -- CPM_REPLICAT sysconv(bit, status & 0x040000) AS is_non_sql_subscribed, -- CPM_NONSQSSUB sysconv(bit, status & 0x080000) AS is_merge_published, -- CPM_MERGEREPL sysconv(bit, status & 0x100000) AS is_dts_replicated, -- CPM_REPLDTS sysconv(bit, status & 2048) AS is_xml_document, -- CPM_XML_DOC xmlns AS xml_collection_id, dflt AS default_object_id, chk AS rule_object_id, sysconv(bit, status & 0x1000000) AS is_sparse, -- CPM_SPARSE sysconv(bit, status & 0x2000000) AS is_column_set -- CPM_SPARSECOLUMNSET FROM sys.syscolpars WHERE number = 0 AND has_access('CO', id) = 1
3) Using sp_helptext procedure :
It displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure. It also returns the definition in multiple rows.
Syntax:
It accepts two parameters; first one is object_id and the second one is computed column.
Examples:
Displaying the definition of a View:
USE AdventureWorks; GO EXEC sp_helptext 'HumanResources.vEmployee'; Output: CREATE VIEW [HumanResources].[vEmployee] AS SELECT e.[EmployeeID] ,c.[Title] ,c.[FirstName] ,c.[MiddleName] ,c.[LastName] ,c.[Suffix] ,e.[Title] AS [JobTitle] ,c.[Phone] ,c.[EmailAddress] ,c.[EmailPromotion] ,a.[AddressLine1] ,a.[AddressLine2] ,a.[City] ,sp.[Name] AS [StateProvinceName] ,a.[PostalCode] ,cr.[Name] AS [CountryRegionName] ,c.[AdditionalContactInfo] FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID] INNER JOIN [HumanResources].[EmployeeAddress] ea ON e.[EmployeeID] = ea.[EmployeeID] INNER JOIN [Person].[Address] a ON ea.[AddressID] = a.[AddressID] INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID] INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
Displaying the definition of a computed column :
The following example displays the definition of the computed column TotalDue on the SalesOrderHeader table in the AdventureWorks database.
USE AdventureWorks; GO sp_helptext @objname = N'AdventureWorks.Sales.SalesOrderHeader', @columnname = TotalDue ; GO Output: (isnull(([SubTotal]+[TaxAmt])+[Freight],(0)))Similarly, we can get the definition of system databases.
Thanks for reading, If you have any query feel free to ask.
No comments:
Post a Comment