DatabaseProcApplicationCreatedLinks
sybsystemprocssp_showcontrolinfo  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = %Z% generic/sproc/%M% %I% %G%  */
3     
4     /*
5     ** Messages for "sp_showcontrolinfo"		????
6     ** 
7     ** 17260, "Can't run %1! from within a transaction."
8     ** 18276, "'%1!" is not a valid object type."
9     ** 18291, "The parameter value '%1!' is invalid."
10    */
11    
12    create procedure sp_showcontrolinfo
13        @object_type varchar(2) = NULL, /* type of object */
14        @object_name varchar(255) = NULL, /* name of object */
15        @spid int = NULL /* SQL Server process id */
16    as
17    
18        declare @attrib_id int,
19            @user_name varchar(255),
20            @upcase_str varchar(2)
21    
22        select @attrib_id = 0 /* initialization */
23    
24        /*
25        **  If we're in a transaction, disallow this since it might make recovery
26        **  impossible.
27        */
28        IF @@trancount > 0
29        BEGIN
30            /*
31            ** 17260, "Can't run %1! from within a transaction."
32            */
33            raiserror 17260, "sp_showcontrolinfo"
34            return (1)
35        END
36        ELSE
37        BEGIN
38            /* Use TSQL mode of unchained transactions */
39            set chained off
40        END
41    
42        /* Dont use any "Dirty Reads" */
43        set transaction isolation level 1
44    
45        /* convert object_type to upper case */
46        IF (@object_type is not NULL)
47            select @object_type = upper(@object_type)
48    
49        /*
50        ** Check to see that the input params are correct
51        */
52        IF @object_type not in (NULL, 'AP', 'LG', 'PR', 'PS', 'EG', 'DF', 'SV')
53        BEGIN
54            /*
55            ** 18276,"'%1!' is not a valid object type." 
56            */
57            raiserror 18276, @object_type
58            return (1)
59        END
60    
61        IF ((@object_type = 'DF') AND ((@object_name is not NULL) OR (@spid is not NULL)))
62        BEGIN
63            /* 18291, "The parameter value '%1!' is invalid." */
64            raiserror 18291, "for object name or spid"
65            return 1
66        END
67    
68        /* 
69        ** Create Table to hold results
70        ** For SDC, there is one extra column, instance id; For SMP, column value null.
71        */
72        CREATE table #shcinfo_res
73        (type varchar(2),
74            object_name varchar(255) null,
75            scope varchar(255) null,
76            exec_class varchar(255) null,
77            engine_group varchar(255) null,
78            engines varchar(390) null,
79            spid int null,
80            attribute varchar(255) null,
81            attribute_value varchar(255) null,
82            instanceid int null)
83    
84        /* 
85        ** Now look into Sysattributes Table...
86        ** Note that this sp looks into the sysattributes table of the current db.
87        */
88        DECLARE appl_info cursor for
89        select object_type, object, object_info1, object_info3,
90            object_cinfo, int_value, convert(varchar(416), char_value)
91        from sysattributes
92        where (class = 6)
93    
94        DECLARE @type varchar(2),
95            @obj int,
96            @info1 int,
97            @info3 int,
98            @cinfo varchar(255),
99            @intval int,
100           @charval varchar(768),
101           @eng_str varchar(768),
102           @eng_num varchar(10),
103           @eng_list varchar(768),
104           @len int,
105           @term int
106   
107       OPEN appl_info
108   
109       FETCH appl_info into @type, @obj, @info1, @info3, @cinfo, @intval, @charval
110       WHILE (@@sqlstatus != 2)
111       BEGIN
112           /*
113           ** Get the definitions for the given object type or all
114           ** object types in the sysattributes table.
115           */
116           IF ((@object_type is NULL) OR (@object_type = @type))
117   
118               /* Look for class bindings for an user */
119   
120               IF (@type = "LG")
121               BEGIN
122                   select @user_name = (select name from
123                               master..syslogins where (suid = @obj))
124                   IF ((@object_name is NULL) OR
125                           (@object_name = @user_name))
126                   BEGIN
127                       insert into #shcinfo_res
128                       values (@type, @user_name,
129                           @cinfo, @charval, NULL,
130                           NULL, NULL, NULL, NULL, @info3)
131                   END
132               END
133   
134               /* Look for class bindings for an application */
135   
136               ELSE IF (@type = "AP")
137               BEGIN
138                   IF ((@object_name is NULL) OR
139                           (@object_name = @cinfo))
140                   BEGIN
141                       IF (@info1 >= 0)
142                           select @user_name =
143                                   (select name from
144                                       master..syslogins
145                                   where (suid = @info1))
146   
147                       insert into #shcinfo_res
148                       values (@type, @cinfo,
149                           @user_name, @charval,
150                           NULL, NULL, NULL,
151                           NULL, NULL, @info3)
152                   END
153               END
154   
155               /* Look for class bindings for a stored proc */
156   
157               ELSE IF (@type = "PR")
158               BEGIN
159                   IF ((@object_name is NULL) OR
160                           (@object_name = @cinfo))
161                   BEGIN
162                       select @user_name =
163                               (select name from sysusers
164                               where (uid = @info1))
165                       insert into #shcinfo_res
166                       values (@type, @cinfo, @user_name,
167                           @charval, NULL, NULL, NULL,
168                           NULL, NULL, @info3)
169                   END
170               END
171   
172               /* Look for Session level attribute definitions */
173   
174               ELSE IF (@type = "PS")
175               BEGIN
176                   IF ((@spid is NULL) OR
177                           (@spid = @info1))
178                   BEGIN
179                       insert into #shcinfo_res
180                       values (@type, NULL, NULL, NULL,
181                           NULL, NULL, @info1, @cinfo,
182                           @charval, @info3)
183                   END
184               END
185   
186               /* Look for engine attribute values */
187   
188               ELSE IF (@type = "EG")
189               BEGIN
190                   IF ((@object_name is NULL) OR
191                           (@object_name = @cinfo))
192                   BEGIN
193   
194                       /* format the list of engines */
195                       select @len = 0
196                       select @term = charindex(":", @charval)
197   
198                       IF (@term != 0)
199                       BEGIN
200                           select @eng_str =
201                               rtrim(@charval)
202                           while (@len <
203                                   char_length(@eng_str))
204                           BEGIN
205                               select @eng_num =
206                                   substring(@eng_str,
207                                       (@len + 1), (@term - 1))
208   
209                               IF (@len = 0)
210                                   select
211                                       @eng_list =
212                                       ltrim(@eng_num)
213                               ELSE
214                                   select
215                                       @eng_list =
216                                       @eng_list +
217                                       "," +
218                                       ltrim(@eng_num)
219                               select @len = @len +
220                                   @term
221                           END
222                       END
223                       ELSE
224                       BEGIN
225                           select @eng_list = @charval
226                       END
227   
228                       /*
229                       ** For SMP, instanceid column is null
230                       ** For SDC, it shows instance id.
231                       */
232                       insert into #shcinfo_res
233                       values (@type, NULL, NULL, NULL,
234                           @cinfo, @eng_list, NULL,
235                           NULL, NULL, @info3)
236                   END
237               END
238   
239               /* Look for default class attributes */
240               ELSE IF (@type = "DF")
241               BEGIN
242                   insert into #shcinfo_res
243                   values (@type, NULL, NULL, @charval,
244                       NULL, NULL, NULL, NULL, NULL, @info3)
245               END
246   
247               /* Look for service task bindings */
248               ELSE IF (@type = "SV")
249               BEGIN
250                   insert into #shcinfo_res
251                   values (@type, @cinfo, NULL, @charval,
252                       NULL, NULL, NULL, NULL, NULL, @info3)
253               END
254   
255           /* Fetch the next row from sysattributes table */
256           FETCH appl_info into @type, @obj, @info1, @info3, @cinfo,
257               @intval, @charval
258       END
259   
260       /* Now print out all the results we got */
261       IF (@@kernelmode = 'process')
262           exec sp_autoformat #shcinfo_res
263       ELSE
264           exec sp_autoformat #shcinfo_res, 'type, object_name, scope, exec_class, threadpool = engine_group, spid, attribute, attribute_value, instanceid'
265   
266       /* Cleanup */
267       close appl_info
268       deallocate cursor appl_info
269       drop table #shcinfo_res
270       return (0)
271   


exec sp_procxmode 'sp_showcontrolinfo', 'AnyMode'
go

Grant Execute on sp_showcontrolinfo to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MTYP 4 Assignment type mismatch exec_class: varchar(255) = varchar(768) 129
 MTYP 4 Assignment type mismatch exec_class: varchar(255) = varchar(768) 149
 MTYP 4 Assignment type mismatch exec_class: varchar(255) = varchar(768) 167
 MTYP 4 Assignment type mismatch attribute_value: varchar(255) = varchar(768) 182
 MTYP 4 Assignment type mismatch engines: varchar(390) = varchar(768) 234
 MTYP 4 Assignment type mismatch exec_class: varchar(255) = varchar(768) 243
 MTYP 4 Assignment type mismatch exec_class: varchar(255) = varchar(768) 251
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 262
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 264
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 92
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause appl_info 89
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public sybsystemprocs..sp_showcontrolinfo  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after insert 127
 MNER 3 No Error Check should check @@error after insert 147
 MNER 3 No Error Check should check @@error after insert 165
 MNER 3 No Error Check should check @@error after insert 179
 MNER 3 No Error Check should check @@error after insert 232
 MNER 3 No Error Check should check @@error after insert 242
 MNER 3 No Error Check should check @@error after insert 250
 MNER 3 No Error Check should check return value of exec 262
 MNER 3 No Error Check should check return value of exec 264
 MUCO 3 Useless Code Useless Brackets 34
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 58
 MUCO 3 Useless Code Useless Brackets 61
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 136
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 174
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 190
 MUCO 3 Useless Code Useless Brackets 198
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 209
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 248
 MUCO 3 Useless Code Useless Brackets 261
 MUCO 3 Useless Code Useless Brackets 270
 MUIN 3 Column created using implicit nullability 72
 QISO 3 Set isolation level 43
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class}		 92
 VNRD 3 Variable is not read @attrib_id 22
 VUNU 3 Variable is not used @upcase_str 20
 CUPD 2 Updatable Cursor Marker (updatable by default) 89
 MSUB 2 Subquery Marker 122
 MSUB 2 Subquery Marker 143
 MSUB 2 Subquery Marker 163
 MTR1 2 Metrics: Comments Ratio Comments: 25% 12
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 29 = 31dec - 4exi + 2 12
 MTR3 2 Metrics: Query Complexity Complexity: 112 12

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_autoformat  
   reads table tempdb..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   reads table tempdb..systypes (1)  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_namecrack  
reads table master..syslogins (1)  
writes table tempdb..#shcinfo_res (1) 
reads table sybsystemprocs..sysusers  
reads table sybsystemprocs..sysattributes