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:


-- get all code objects containing the supplied search string


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





ċ
Andy Hughes,
Jun 5, 2020, 12:13 PM
ċ
Andy Hughes,
Jun 5, 2020, 12:13 PM
ċ
Andy Hughes,
Jun 5, 2020, 12:13 PM
ċ
Andy Hughes,
Jun 5, 2020, 12:13 PM
ċ
Andy Hughes,
Jun 5, 2020, 12:13 PM
ċ
Andy Hughes,
Jun 5, 2020, 12:13 PM
ċ
Andy Hughes,
Jun 5, 2020, 12:13 PM
ċ
Andy Hughes,
Jun 5, 2020, 12:13 PM
Comments