Database	Proc	Application	Created	Links
sybsystemprocs	sp_monitor_deadlock_count_by		31 Aug 14	Defects Dependencies

1     
2     /*
3     ** sp_monitor_deadlock_count_by
4     **
5     **	Process the @output option, if supplied. We support only a few types
6     **	of output options, mainly to generate summary data. The caller tells
7     **	us what options to support. Check if @output is one of them, and
8     **	generate the required frequency data.
9     **
10    ** Parameters:
11    **	@deadlock_tabname	- Table containing deadlock event data.
12    **	@dbname			- DB where @deadlock_tabname exists.
13    **	@temp_tablename		- #temp table created in caller for processing.
14    **	@output_count_by_date
15    **	@output_count_by_appln
16    **	@output_count_by_object
17    **	@output_count_by_date_object
18    **				- String constants for various output modes.
19    **	@output			- Use supplied output mode argument.
20    **
21    ** Returns:
22    **	0	- If all went well.
23    **	!= 0	- Otherwise.
24    {
25    */
26    create procedure sp_monitor_deadlock_count_by(
27        @deadlock_tabname varchar(30)
28        , @dbname varchar(30)
29        , @temp_tablename varchar(30)
30        , @output_count_by_date varchar(30)
31        , @output_count_by_appln varchar(30)
32        , @output_count_by_object varchar(30)
33        , @output_count_by_date_object varchar(30)
34        , @output varchar(30)
35    ) as
36        begin
37            declare @sqlstmt varchar(256)
38                , @mdacfg_retval int
39                , @retval int
40                , @insert varchar(6)
41                , @select varchar(6)
42    
43            -- We only proces 'count by ' output options here. Anything else
44            -- might be a valid option, but we don't check for that here.
45            -- 
46            if ((@output IS NULL) OR (@output NOT LIKE "%count by%"))
47                return 0
48    
49            -- ==================================================================
50            -- Process the output modes, displaying summary only if so
51            -- requested. Then bail.
52            -- For all output modes, cache the monitor table info into a #temp
53            -- table, doing the GROUP BY while generating the #temp table.
54            -- Then autoformat the results from that #temp for readability.
55            --
56            if (@output NOT IN (@output_count_by_date
57                        , @output_count_by_appln
58                        , @output_count_by_object
59                        , @output_count_by_date_object
60                    ))
61            begin
62                select @sqlstmt = "'" + @output_count_by_date + "'"
63                    + ", '" + @output_count_by_appln + "'"
64                    + ", '" + @output_count_by_object + "'"
65                    + ", '" + @output_count_by_date_object + "'"
66    
67                raiserror 18640, "output", @output, @sqlstmt
68                return 1
69            end
70    
71            select @insert = "INSERT"
72                , @select = "SELECT"
73    
74            if (@output = @output_count_by_date)
75            begin
76                print " "
77                exec sp_getmessage 19637, @sqlstmt output
78                print @sqlstmt, 'ResolveDate', @dbname, @deadlock_tabname
79                print " "
80    
81                -- Create an empty template table for future inserts.
82                create table #freq_by_resolvedate(
83                    ResolveDate date
84                    , Frequency int
85                )
86                select @sqlstmt = @insert
87                    + " #freq_by_resolvedate "
88                    + @select
89                    + " CONVERT(DATE, ResolveTime)"
90                    + ", COUNT(DISTINCT DeadlockID)"
91                    + " FROM " + @temp_tablename
92                    + " GROUP BY CONVERT(DATE, ResolveTime)"
93    
94                exec @retval = sp_exec_SQL @sqlstmt, @output
95    
96                if (@retval = 0)
97                    exec sp_autoformat
98                        @fulltabname = #freq_by_resolvedate
99                        , @orderbyclause = "order by ResolveDate asc"
100           end
101   
102           else if (@output = @output_count_by_appln)
103           begin
104               print " "
105               exec sp_getmessage 19637, @sqlstmt output
106               print @sqlstmt, 'HeldApplName', @dbname, @deadlock_tabname
107               print " "
108   
109               -- Create an empty template table for future inserts.
110               create table #freq_by_applname(
111                   ApplName varchar(30)
112                   , Frequency int
113               )
114   
115               select @sqlstmt = @insert + " #freq_by_applname "
116                   + @select
117                   + " HeldApplName "
118                   + ", COUNT(DISTINCT DeadlockID)"
119                   + " FROM " + @temp_tablename
120                   + " GROUP BY HeldApplName"
121   
122               exec @retval = sp_exec_SQL @sqlstmt, @output
123   
124               if (@retval = 0)
125                   exec sp_autoformat
126                       @fulltabname = #freq_by_applname
127                       , @orderbyclause = "order by HeldApplName asc"
128           end
129   
130           else if (@output = @output_count_by_object)
131           begin
132   
133               print " "
134               exec sp_getmessage 19637, @sqlstmt output
135               print @sqlstmt, 'DBName, ObjectName', @dbname, @deadlock_tabname
136               print " "
137   
138               -- Create an empty template table for future inserts.
139               select DBName = db_name()
140                   , ObjectName = o.name
141                   , Frequency = convert(int null, 0)
142               into #freq_by_name
143               from master.dbo.sysobjects o
144               where 1 = 0
145   
146               -- Only generate the columns we really need to generate the
147               -- group by counts into a #temptable.
148               --
149               select @sqlstmt = @insert + " #freq_by_name "
150                   + @select
151                   + " ObjectDBName"
152                   + ", ObjectName"
153                   + ", count(DeadlockID)"
154                   + " FROM " + @temp_tablename
155                   + " GROUP BY ObjectDBName, ObjectName"
156   
157               exec @retval = sp_exec_SQL @sqlstmt, @output
158   
159               if (@retval = 0)
160                   exec sp_autoformat
161                       @fulltabname = #freq_by_name
162                       , @orderbyclause = "order by 1, 2"
163           end
164   
165           else if (@output = @output_count_by_date_object)
166           begin
167   
168               print " "
169               exec sp_getmessage 19637, @sqlstmt output
170               print @sqlstmt, 'ResolveDate, DBName, ObjectName'
171                   , @dbname, @deadlock_tabname
172               print " "
173   
174               -- Create an empty template table for future inserts.
175               select ResolveDate = CONVERT(DATE, o.crdate)
176                   , DBName = db_name()
177                   , ObjectName = o.name
178                   , Frequency = convert(int null, 0)
179               into #freq_by_date_name
180               from master.dbo.sysobjects o
181               where 1 = 0
182   
183               -- Only generate the columns we really need to generate the
184               -- group by counts into a #temptable.
185               --
186               select @sqlstmt = @insert + " #freq_by_date_name "
187                   + @select
188                   + " CONVERT(DATE, ResolveTime)"
189                   + ", ObjectDBName"
190                   + ", ObjectName"
191                   + ", count(DeadlockID) "
192                   + " FROM " + @temp_tablename
193                   + " GROUP BY "
194                   + "CONVERT(DATE, ResolveTime), ObjectDBName, ObjectName"
195   
196               exec @retval = sp_exec_SQL @sqlstmt, @output
197   
198               if (@retval = 0)
199                   exec sp_autoformat
200                       @fulltabname = #freq_by_date_name
201                       , @orderbyclause = "order by 1, 2, 3"
202           end
203   
204           -- Single return point for all "count by" options.
205           -- Any error from sp_exec_SQL will be returned back via this stmt.
206           return @retval
207   
208       end -- }
209



exec sp_procxmode 'sp_monitor_deadlock_count_by', 'AnyMode'
go

Grant Execute on sp_monitor_deadlock_count_by to public
go

DEFECTS
MBPA 6 Not a parameter of this proc sybsystemprocs..sp_autoformat: @orderbyclause	99
MBPA 6 Not a parameter of this proc sybsystemprocs..sp_autoformat: @orderbyclause	127
MBPA 6 Not a parameter of this proc sybsystemprocs..sp_autoformat: @orderbyclause	162
MBPA 6 Not a parameter of this proc sybsystemprocs..sp_autoformat: @orderbyclause	201
MTYP 4 Assignment type mismatch null = varchar(24)	99
MTYP 4 Assignment type mismatch null = varchar(25)	127
MTYP 4 Assignment type mismatch null = varchar(13)	162
MTYP 4 Assignment type mismatch null = varchar(16)	201
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname	98
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname	126
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname	161
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname	200
MGTP 3 Grant to public master..sysobjects
MGTP 3 Grant to public sybsystemprocs..sp_monitor_deadlock_count_by
MNER 3 No Error Check should check return value of exec	77
MNER 3 No Error Check should check return value of exec	97
MNER 3 No Error Check should check return value of exec	105
MNER 3 No Error Check should check return value of exec	125
MNER 3 No Error Check should check return value of exec	134
MNER 3 No Error Check should check @@error after select into	139
MNER 3 No Error Check should check return value of exec	160
MNER 3 No Error Check should check return value of exec	169
MNER 3 No Error Check should check @@error after select into	175
MNER 3 No Error Check should check return value of exec	199
MUCO 3 Useless Code Useless Brackets in create proc	26
MUCO 3 Useless Code Useless Begin-End Pair	36
MUCO 3 Useless Code Useless Brackets	46
MUCO 3 Useless Code Useless Brackets	56
MUCO 3 Useless Code Useless Brackets	74
MUCO 3 Useless Code Useless Brackets	96
MUCO 3 Useless Code Useless Brackets	102
MUCO 3 Useless Code Useless Brackets	124
MUCO 3 Useless Code Useless Brackets	130
MUCO 3 Useless Code Useless Brackets	159
MUCO 3 Useless Code Useless Brackets	165
MUCO 3 Useless Code Useless Brackets	198
MUIN 3 Column created using implicit nullability	82
MUIN 3 Column created using implicit nullability	110
QCTC 3 Conditional Table Creation	82
QCTC 3 Conditional Table Creation	110
QCTC 3 Conditional Table Creation	139
QCTC 3 Conditional Table Creation	175
QPNC 3 No column in condition	144
QPNC 3 No column in condition	181
VUNU 3 Variable is not used @mdacfg_retval	38
MTR1 2 Metrics: Comments Ratio Comments: 30%	26
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 11dec - 2exi + 2	26
MTR3 2 Metrics: Query Complexity Complexity: 69	26