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 SELECTob.name AS Executable_Name,REPLACE(LOWER(ob.type_desc), '_', ' ') AS Executable_Type,COALESCE(EP.value, '') AS DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS EPON EP.major_id = ob.object_idAND 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 SELECTob.name AS Stored_Procedure_Name,COALESCE(EP.value, '') AS DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS EPON EP.major_id = ob.object_idAND EP.minor_id = 0AND 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 SELECTob.name AS View_Name,COALESCE(ep.value, '') AS DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS epON ep.major_id = ob.object_idAND ep.class = 1AND ep.minor_id = 0WHERE OBJECTPROPERTY(ob.object_id,'IsView')= 1

Get a summary of all Functions (INLINE):

/* The Inline Functions */--all inline functions in the database SELECTob.name AS Inline_function_Name,COALESCE(EP.value, '') AS DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS EPON EP.major_id = ob.object_idAND 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 SELECTob.name AS Scalar_Function_Name,COALESCE(EP.value, '') AS DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS EPON EP.major_id = ob.object_idAND 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 SELECTob.name AS Table_Valued_Function_Name,COALESCE(EP.value, '') AS DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS EPON EP.major_id = ob.object_idAND 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 SELECTob.name AS Stored_Procedure_Name,COALESCE(EP.value, '') AS DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS EPON EP.major_id = ob.object_idAND EP.minor_id = 0AND 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 SYSNAMESELECT-- @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, CASEWHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN '(' --we fetch the length of the data + CASEWHEN p.max_length=-1 THEN 'MAX'ELSE CONVERT(VARCHAR(4), --get the length CASEWHEN t.name IN ('nchar','nvarchar') THEN p.max_length/2ELSE p.max_lengthEND )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_ModifierFROM 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_IDINNER JOIN sys.types t --to get the details of the types ON p.user_type_id=t.user_type_idWHERE obj.object_ID = ISNULL(object_id(@ObjectName), obj.object_ID)ORDER BYObject_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_ExtractFROM(SELECTtype_desc,definition,o.object_ID,charindex(@SearchString,definition) AS SearchHitFROM sys.SQL_modules mINNER JOIN sys.objects oON o.object_ID=m.object_ID) AS fWHERE SearchHit > 0ORDER BYObject_Type,Object_Name