Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
?-- =============================================
-- Description: Returns the user defined functions and stored procs descriptions and CREATE parameters (for documentation).
-- See http://www.sqlservercentral.com/articles/T-SQL/179129/?utm_source=SSC&utm_medium=pubemail
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE [dbo].[uspRptDetUspUfnDictionary]
@objectNames varchar (MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
SELECT ObjectName
, [definition] AS TotalObjectDefinition
, CASE
WHEN charIndexDESCRIPTION < charIndexCREATE THEN
SUBSTRING([definition], charIndexDESCRIPTION, charIndexCREATE - charIndexDESCRIPTION)
ELSE
[definition]
END AS ObjectDescription
, CASE
WHEN charIndexCREATE < charIndexAS THEN
SUBSTRING([definition], charIndexCREATE, charIndexAS - charIndexCREATE)
ELSE
[definition]
END AS ObjectStatement
FROM (SELECT O.[name] AS ObjectName
, SM.[definition] AS [definition]
--Need to search based on case sensitivity to improve discerning the proper charindex points
, CHARINDEX('Description', SM.[definition] COLLATE sql_latin1_general_cp1_cs_as) AS charIndexDESCRIPTION
, CHARINDEX('CREATE', SM.[definition] COLLATE sql_latin1_general_cp1_cs_as) AS charIndexCREATE
, CHARINDEX('AS', SM.[definition] COLLATE sql_latin1_general_cp1_cs_as) AS charIndexAS
FROM sys.sql_modules AS SM
JOIN sys.objects AS O ON SM.[object_id] = O.[object_id]
WHERE O.[name] Not Like 'sp_%'
AND o.[name] Not Like 'fn_%'
AND (ISNULL(o.[name], 0) In(SELECT [Value] FROM ufnSplit(',', @objectNames)) OR @objectNames IS NULL)
) AS tTable
ORDER BY 1;
END TRY
BEGIN CATCH
PRINT 'There was an error getting the user defined stored procedure and function meta description'+ CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH
END