Get Database Code Object Metadata

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author:Phil Factor/ChillyDBA

Date: 5 Jun 2020

Description

A small collection of queries to produce database-level reports on stored procedures, functions and views. These should mostly run as-is but can be easily customized.

There are 2 main types of report queries:

    1. Summary List - containing schema/object names and any stored extended properties. Ideal for documentation

    2. Detail List - containing schema/object names plus object type, component columns and code. Can be used for documentation, but also valuable for use when constructing Dynamic SQL queries

Where there are multiple variants on the same query, one will utilize the INFORMATION_SCHEMA views and the other the Dynamic Management Views. This is generally for Detail type queries as some detail levels are only available in one source

Code

Get a summary of all database executable code objects:

/* The Executables */

--all executables (procedures, functions etc) in the database

SELECT

ob.name AS Executable_Name

,REPLACE(LOWER(ob.type_desc), '_', ' ') AS Executable_Type

,COALESCE(EP.value, '') AS Documentation

FROM sys.objects AS ob

LEFT OUTER JOIN sys.extended_properties AS EP

ON EP.major_id = ob.object_id

AND EP.name = 'MS_Description' --the microsoft convention

WHERE OBJECTPROPERTY(ob.object_id, 'IsExecuted') = 1;


Get a summary of all Stored Procedures:

/* The Stored Procedures */

--all stored procedures in the database

SELECT

ob.name AS Stored_Procedure_Name

,COALESCE(EP.value, '') AS Documentation

FROM sys.objects AS ob

LEFT OUTER JOIN sys.extended_properties AS EP

ON EP.major_id = ob.object_id

AND EP.minor_id = 0

AND EP.name = 'MS_Description' --the microsoft convention

WHERE OBJECTPROPERTY(ob.object_id, 'IsProcedure') = 1;


Get a summary of all Views:

/* The Views */

--all views in the database

SELECT

ob.name AS View_Name

,COALESCE(ep.value, '') AS Documentation

FROM sys.objects AS ob

LEFT OUTER JOIN sys.extended_properties AS ep

ON ep.major_id = ob.object_id

AND ep.class = 1

AND ep.minor_id = 0

WHERE OBJECTPROPERTY(ob.object_id,'IsView')= 1


Get a summary of all Functions (INLINE):


/* The Inline Functions */

--all inline functions in the database

SELECT

ob.name AS Inline_function_Name

,COALESCE(EP.value, '') AS Documentation

FROM sys.objects AS ob

LEFT OUTER JOIN sys.extended_properties AS EP

ON EP.major_id = ob.object_id

AND EP.name = 'MS_Description' --the microsoft convention

WHERE OBJECTPROPERTY(ob.object_id,'IsInlineFunction')= 1;


Get a summary of all Functions (SCALAR):

/* The Scalar Functions */

--all scalar functions in the database

SELECT

ob.name AS Scalar_Function_Name

,COALESCE(EP.value, '') AS Documentation

FROM sys.objects AS ob

LEFT OUTER JOIN sys.extended_properties AS EP

ON EP.major_id = ob.object_id

AND EP.name = 'MS_Description' --the microsoft convention

WHERE OBJECTPROPERTY(ob.object_id, 'IsScalarFunction') = 1;



Get a summary of all Functions (TABLE VALUED)::


/* The TVFs*/

--all table-valued functions in the database

SELECT

ob.name AS Table_Valued_Function_Name

,COALESCE(EP.value, '') AS Documentation

FROM sys.objects AS ob

LEFT OUTER JOIN sys.extended_properties AS EP

ON EP.major_id = ob.object_id

AND EP.name = 'MS_Description' --the microsoft convention

WHERE OBJECTPROPERTY(ob.object_id, 'IsTableFunction') = 1;


Get a summary of all Stored Procedures:

/* The Stored Procedures */

--all stored procedures in the database

SELECT

ob.name AS Stored_Procedure_Name

,COALESCE(EP.value, '') AS Documentation

FROM sys.objects AS ob

LEFT OUTER JOIN sys.extended_properties AS EP

ON EP.major_id = ob.object_id

AND EP.minor_id = 0

AND EP.name = 'MS_Description' --the microsoft convention

WHERE OBJECTPROPERTY(ob.object_id, 'IsProcedure') = 1;


Get a list of Parameters for one or many database code objects:



-- get all parameters for all or one objects


DECLARE

@ObjectName SYSNAME


SELECT

-- @ObjectName = NULL

@ObjectName = 'My_First_SP'



SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,OBJECT_SCHEMA_NAME(obj.object_ID) AS Schema_Name

,OBJECT_NAME(obj.object_ID) AS Object_Name

,OBJECT_SCHEMA_NAME(obj.object_ID) +'.' + OBJECT_NAME(obj.object_ID) AS Combined_Name

,type_desc AS Object_Type

,Parameter_ID AS Parameter_ID

,p.name AS Parameter_Name

,t.name AS Parameter_DataType

, CASE

WHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN '(' --we fetch the length of the data

+ CASE

WHEN p.max_length=-1 THEN 'MAX'

ELSE CONVERT(VARCHAR(4), --get the length

CASE

WHEN t.name IN ('nchar','nvarchar') THEN p.max_length/2

ELSE p.max_length

END )

END +')'

WHEN t.name IN ('decimal','numeric') --we need scale and precision

THEN '('+ convert(VARCHAR(4),p.precision)+',' + convert(VARCHAR(4),p.Scale)+')'

ELSE ''

END AS Parameter_DataType_Modifier

FROM sys.all_objects obj -- from all the objects (system and database)

INNER JOIN sys.parameters p --to get all the parameters

ON p.object_ID=obj.object_ID

INNER JOIN sys.types t --to get the details of the types

ON p.user_type_id=t.user_type_id

WHERE obj.object_ID = ISNULL(object_id(@ObjectName), obj.object_ID)

ORDER BY

Object_Name

,Parameter_ID


Search the code of all Database code objects:

DECLARE @SearchString NVARCHAR(MAX)


SELECT @SearchString = 'CURSOR'



SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,type_desc AS Object_Type

,COALESCE(OBJECT_SCHEMA_NAME(object_id) + '.','') + CONVERT(CHAR(32),OBJECT_NAME(object_ID)) AS Object_Name

,'...'+SUBSTRING(definition, SearchHit - 20,120) +'...' AS Object_Code_Extract

FROM

(

SELECT

type_desc

,definition

,o.object_ID

,charindex(@SearchString,definition) AS SearchHit

FROM sys.SQL_modules m

INNER JOIN sys.objects o

ON o.object_ID=m.object_ID

) AS f

WHERE SearchHit > 0

ORDER BY

Object_Type

,Object_Name