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:
Summary List - containing schema/object names and any stored extended properties. Ideal for documentation
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