-
Notifications
You must be signed in to change notification settings - Fork 681
/
dbo.usp_UnusedInputParameters.sql
146 lines (118 loc) · 4.87 KB
/
dbo.usp_UnusedInputParameters.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
-----------------------------------------------------------------------------------------------------------------------------
-- Error Trapping: Check If Procedure Already Exists And Drop If Applicable
-----------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID (N'dbo.usp_UnusedInputParameters', N'P') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.usp_UnusedInputParameters
END
GO
-----------------------------------------------------------------------------------------------------------------------------
-- Stored Procedure Details: Listing Of Standard Details Related To The Stored Procedure
-----------------------------------------------------------------------------------------------------------------------------
-- Purpose: Identify All Objects Containing Unused Input Parameters
-- Create Date (MM/DD/YYYY): 11/25/2012
-- Developer: Sean Smith (s(DOT)smith(DOT)sql(AT)gmail(DOT)com)
-- Additional Notes: N/A
-----------------------------------------------------------------------------------------------------------------------------
-- Modification History: Listing Of All Modifications Since Original Implementation
-----------------------------------------------------------------------------------------------------------------------------
-- Description: No Modifications To Date
-- Date (MM/DD/YYYY): N/A
-- Developer: N/A
-- Additional Notes: N/A
-----------------------------------------------------------------------------------------------------------------------------
-- Main Query: Create Procedure
-----------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.usp_UnusedInputParameters
@v_Database AS SYSNAME
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET TEXTSIZE 2147483647
EXECUTE
(
N'
USE [' + @v_Database + N']
SELECT
SCHEMA_NAME (O.[schema_id]) AS [schema_name]
,O.name AS [object_name]
,P.name AS parameter_name
,O.[type] AS object_type
,TYPE_NAME (P.user_type_id) AS data_type
,(CASE P.is_output
WHEN 1 THEN ''Yes''
ELSE ''No''
END) AS is_output
,SQLM.[definition]
FROM
sys.parameters P
INNER JOIN sys.objects O ON O.[object_id] = P.[object_id]
AND O.is_ms_shipped = 0
INNER JOIN sys.sql_modules SQLM ON SQLM.[object_id] = P.[object_id]
LEFT JOIN
(
SELECT
XP.[object_id]
,XP.parameter_id
,XP.name + NCHAR (13) AS parameter_name_modified
,REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (
XSQLM.[definition] + NCHAR (13)
, XP.name + N'' '', XP.name + NCHAR (13))
, XP.name + N'' '', XP.name + NCHAR (13))
, XP.name + N''!'', XP.name + NCHAR (13))
, XP.name + N''"'', XP.name + NCHAR (13))
, XP.name + N''%'', XP.name + NCHAR (13))
, XP.name + N''&'', XP.name + NCHAR (13))
, XP.name + N'''''''', XP.name + NCHAR (13))
, XP.name + N''('', XP.name + NCHAR (13))
, XP.name + N'')'', XP.name + NCHAR (13))
, XP.name + N''*'', XP.name + NCHAR (13))
, XP.name + N''+'', XP.name + NCHAR (13))
, XP.name + N'','', XP.name + NCHAR (13))
, XP.name + N''-'', XP.name + NCHAR (13))
, XP.name + N''.'', XP.name + NCHAR (13))
, XP.name + N''/'', XP.name + NCHAR (13))
, XP.name + N'';'', XP.name + NCHAR (13))
, XP.name + N''<'', XP.name + NCHAR (13))
, XP.name + N''='', XP.name + NCHAR (13))
, XP.name + N''>'', XP.name + NCHAR (13))
, XP.name + N''['', XP.name + NCHAR (13))
, XP.name + N'']'', XP.name + NCHAR (13))
AS definition_modified
FROM
sys.parameters XP
INNER JOIN sys.sql_modules XSQLM ON XSQLM.[object_id] = XP.[object_id]
WHERE
EXISTS
(
SELECT
*
FROM
sys.parameters YP
WHERE
YP.[object_id] = XP.[object_id]
AND YP.parameter_id <> XP.parameter_id
AND LEFT (YP.name, LEN (XP.name)) = XP.name
)
) FN ON FN.[object_id] = P.[object_id] AND FN.parameter_id = P.parameter_id
CROSS APPLY
(
SELECT
ISNULL (FN.parameter_name_modified, P.name) AS parameter_name_compare
,ISNULL (FN.definition_modified, SQLM.[definition]) AS definition_compare
) CV
WHERE
P.parameter_id <> 0
AND CHARINDEX (CV.parameter_name_compare, CV.definition_compare, CHARINDEX (CV.parameter_name_compare, CV.definition_compare) + 1) = 0
ORDER BY
SCHEMA_NAME (O.[schema_id])
,O.name
,P.name
'
)
GO