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

1     
2     /* Sccsid = %Z% generic/include/%M% %I% %G%  */
3     
4     /*
5     ** Messages for "sp_showpsexe"		????
6     **
7     ** 17260, "Can't run %1! from within a transaction."
8     ** 18264, "No SQL Server process with specified ID exists"  
9     */
10    
11    create procedure sp_showpsexe
12    
13        @spid smallint = NULL /* ASE process id */
14    
15    as
16        declare @msg varchar(1024),
17            @user_name varchar(30)
18    
19        /*
20        **  If we're in a transaction, disallow this since it might make recovery
21        **  impossible.
22        */
23        IF @@trancount > 0
24        BEGIN
25            /*
26            ** 17260, "Can't run %1! from within a transaction."
27            */
28            exec sp_getmessage 17260, @msg output
29            print @msg, "sp_showpsexe"
30            return (1)
31        END
32        ELSE
33        BEGIN
34            /* Use TSQL mode of unchained transactions */
35            set chained off
36        END
37    
38        /* Dont use any "Dirty Reads" */
39        set transaction isolation level 1
40    
41        /*
42        ** Check to see that the input params are correct
43        */
44    
45        /* Check that the supplied spid is ok */
46        IF (@spid is not NULL)
47        BEGIN
48            IF @spid not in (select spid from master..sysprocesses)
49            BEGIN
50                /*
51                ** 18264, "No SQL Server process with specified ID exists."
52                */
53                exec sp_getmessage 18264, @msg output
54                print @msg
55                return (1)
56            END
57        END
58    
59        /* Create Tables to hold results */
60        CREATE table #shtsk_restab
61        (
62    
63            spid smallint,
64    
65            appl_name varchar(30) null,
66            login_name varchar(30) null,
67            exec_class varchar(30) null,
68            current_priority varchar(10),
69            task_affinity varchar(30) null,
70            instanceid smallint null)
71    
72        /* Now look into Sysprocesses Table */
73        DECLARE task_info cursor for
74        select spid, suid, program_name, execlass, priority, affinity, cmd
75    
76        from master..sysprocesses
77    
78        DECLARE @task smallint,
79            @id int,
80            @applname varchar(30),
81            @class_str varchar(30),
82            @pri_str varchar(10),
83            @affinity_str varchar(30),
84            @cmd_name varchar(30),
85            @instanceid smallint
86    
87        OPEN task_info
88    
89        FETCH task_info into @task, @id, @applname, @class_str, @pri_str,
90            @affinity_str, @cmd_name
91    
92    
93        WHILE (@@sqlstatus != 2)
94        BEGIN
95            select @user_name = suser_name(@id)
96            IF ((@cmd_name != "HK WASH") AND
97                    (@cmd_name != "HK GC") AND
98                    (@cmd_name != "HK CHORES") AND
99                    ((select name from master..syslogins where (suid = @id)) is NULL))
100           BEGIN
101               FETCH task_info into @task, @id, @applname, @class_str,
102                   @pri_str, @affinity_str, @cmd_name
103   
104               continue
105           END
106   
107           IF ((@spid is NULL) OR (@spid = @task))
108           BEGIN
109               IF ((@@kernelmode = 'process') AND (@affinity_str = "ANYENGINE"))
110                   select @affinity_str = "NONE"
111   
112               insert into #shtsk_restab
113               values (@task, @applname, @user_name,
114                   @class_str, @pri_str, @affinity_str,
115   
116                   NULL)
117   
118           END
119   
120           /* Fetch the next row from sysprocesses table */
121   
122           FETCH task_info into @task, @id, @applname, @class_str, @pri_str,
123               @affinity_str, @cmd_name
124   
125       END
126   
127   
128       /* 
129       ** Now print out all the results we got.
130       ** For SMP, omit instanceid column. 
131       */
132   
133       select spid, appl_name, login_name, exec_class,
134           current_priority, task_affinity
135       from #shtsk_restab
136   
137   
138       /* Cleanup */
139       CLOSE task_info
140       deallocate cursor task_info
141       drop table #shtsk_restab
142       return (0)
143   

exec sp_procxmode 'sp_showpsexe', 'AnyMode'
go

Grant Execute on sp_showpsexe to public
go

DEFECTS
TNOI 4 Table with no index master..sysprocesses	master..sysprocesses
CUNU 3 Cursor not updated: cursor should contain 'for read only' clause task_info	74
MGTP 3 Grant to public master..syslogins
MGTP 3 Grant to public master..sysprocesses
MGTP 3 Grant to public sybsystemprocs..sp_showpsexe
MNER 3 No Error Check should check return value of exec	28
MNER 3 No Error Check should check return value of exec	53
MNER 3 No Error Check should check @@error after insert	112
MUCO 3 Useless Code Useless Brackets	30
MUCO 3 Useless Code Useless Brackets	46
MUCO 3 Useless Code Useless Brackets	55
MUCO 3 Useless Code Useless Brackets	93
MUCO 3 Useless Code Useless Brackets	96
MUCO 3 Useless Code Useless Brackets	99
MUCO 3 Useless Code Useless Brackets	107
MUCO 3 Useless Code Useless Brackets	109
MUCO 3 Useless Code Useless Brackets	142
MUIN 3 Column created using implicit nullability	60
QISO 3 Set isolation level	39
VUNU 3 Variable is not used @instanceid	85
CUPD 2 Updatable Cursor Marker (updatable by default)	74
MRST 2 Result Set Marker	133
MSUB 2 Subquery Marker	99
MTR1 2 Metrics: Comments Ratio Comments: 27%	11
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 12dec - 3exi + 2	11
MTR3 2 Metrics: Query Complexity Complexity: 52	11

DEPENDENCIES

PROCS AND TABLES USED reads table master..sysprocesses (1)   reads table master..syslogins (1)   calls proc sybsystemprocs..sp_getmessage      reads table sybsystemprocs..sysusermessages      reads table master..sysmessages (1)      reads table master..syslanguages (1)      calls proc sybsystemprocs..sp_validlang         reads table master..syslanguages (1)   read_writes table tempdb..#shtsk_restab (1)