1
2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */
3
4 /*
5 ** Messages for "sp_listsuspect_object"
6 **
7 ** 17260, "Can't run %1! from within a transaction."
8 */
9
10 create procedure sp_listsuspect_object
11 @dbname varchar(30) = NULL
12 as
13 declare @dbid int, /* dbid of the database */
14 @attrib_id int,
15 @action int,
16 @object_type varchar(2),
17 @msg varchar(250),
18 @sptlang int,
19 @state int,
20 @old_state int,
21 @susgran int,
22 @class int,
23 @whichone int /* which language? */
24
25 select @action = 2 /* modify entry */
26 select @class = 10
27 select @attrib_id = 3 /* attribute is SUSPECT OBJECTS */
28 select @object_type = 'D'
29
30 if @@trancount = 0
31 begin
32 set chained off
33 end
34
35 set transaction isolation level 1
36
37 select @sptlang = @@langid, @whichone = 0
38
39 if @@langid != 0
40 begin
41 if not exists (
42 select * from master.dbo.sysmessages where error
43 between 17050 and 17069
44 and langid = @@langid)
45 select @sptlang = 0
46 end
47 /*
48 ** Check for input params - dbname, objectid, and options
49 */
50 if @dbname is null
51 select @dbname = db_name()
52
53 /*
54 ** Verify the database name and get the @dbid
55 */
56 select @dbid = db_id(@dbname)
57
58 /*
59 ** If @dbname not found, say so.
60 */
61 if @dbid is NULL
62 begin
63 /*
64 ** 17421, "No such database -- run sp_helpdb to list databases."
65 */
66 raiserror 17421
67 return (1)
68 end
69
70 select "DBName" = convert(varchar(20), @dbname),
71 "Objid" = convert(varchar(12), int_value),
72 "Object" = convert(varchar(15), object_name(object_info1, @dbid)),
73 "Index" = convert(varchar(4), object_info2),
74 "Access" = substring(char_value, 1, 9)
75 from master.dbo.sysattributes
76 where class = @class AND
77 attribute = @attrib_id AND
78 object_type = @object_type AND
79 object = @dbid
80
81 return (0)
82
exec sp_procxmode 'sp_listsuspect_object', 'AnyMode'
go
Grant Execute on sp_listsuspect_object to public
go
DEFECTS |
MINU 4 Unique Index with nullable columns master..sysattributes |
master..sysattributes |
MINU 4 Unique Index with nullable columns master..sysmessages |
master..sysmessages |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
44 |
QTYP 4 Comparison type mismatch smallint = int |
44 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
76 |
QTYP 4 Comparison type mismatch smallint = int |
76 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
77 |
QTYP 4 Comparison type mismatch smallint = int |
77 |
MGTP 3 Grant to public master..sysattributes |
|
MGTP 3 Grant to public master..sysmessages |
|
MGTP 3 Grant to public sybsystemprocs..sp_listsuspect_object |
|
MUCO 3 Useless Code Useless Brackets |
67 |
MUCO 3 Useless Code Useless Brackets |
81 |
QISO 3 Set isolation level |
35 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique (error, dlevel, langid) Intersection: {error, langid} |
42 |
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: {object_type, object, attribute, class} |
76 |
VNRD 3 Variable is not read @action |
25 |
VNRD 3 Variable is not read @whichone |
37 |
VNRD 3 Variable is not read @sptlang |
45 |
VUNU 3 Variable is not used @msg |
17 |
VUNU 3 Variable is not used @state |
19 |
VUNU 3 Variable is not used @old_state |
20 |
VUNU 3 Variable is not used @susgran |
21 |
MRST 2 Result Set Marker |
70 |
MSUB 2 Subquery Marker |
41 |
MTR1 2 Metrics: Comments Ratio Comments: 25% |
10 |
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 7dec - 2exi + 2 |
10 |
MTR3 2 Metrics: Query Complexity Complexity: 34 |
10 |