Database | Proc | Application | Created | Links |
sybsystemprocs | sp_indsuspect ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 4 /* 5 ** Messages from sysmessages 6 ** 7 ** 17860, "Suspect indexes in database %1!:" 8 ** 17861, "There are no suspect indexes in database %1!." 9 ** 17862, "Table must be in the 'current' database." 10 ** 17863, "There is no table named %1! in the 'current' database." 11 ** 17864, "Suspect indexes on table %1!:" 12 ** 17865, "There are no suspect indexes on table %1!." 13 */ 14 15 create procedure sp_indsuspect 16 @tab_name varchar(767) = NULL /* if NULL, check all tables in db */ 17 AS 18 19 declare @msg varchar(1024) /* used for message to user */ 20 declare @msg2 varchar(30) /* used for table/db name */ 21 declare @ind_suspect smallint /* value of IND_SUSPECT bit */ 22 23 24 if @@trancount = 0 25 begin 26 set chained off 27 end 28 29 set transaction isolation level 1 30 31 set nocount on 32 33 /* 34 ** Specify the bit mask as -32768, not in hex notation, for portability 35 ** to byte-swapped platforms. This could break on a 1's complement 36 ** platform, but there isn't really a better solution. SQL handles 37 ** hex numbers as binary data, not as numeric values as in C. 38 */ 39 40 select @ind_suspect = - 32768 41 42 if (@tab_name is null) 43 begin 44 /* 45 ** No table name specified, sp search for any 46 ** suspected indexes in the entire database. 47 */ 48 if exists (select * from sysindexes i 49 where (i.status & @ind_suspect) != 0) 50 begin 51 /* 17860, "Suspect indexes in database %1!:" */ 52 exec sp_getmessage 17860, @msg output 53 select @msg2 = db_name() 54 print @msg, @msg2 55 56 select 57 "Own.Tab.Ind (Obj_ID, Ind_ID)" = 58 u.name + "." + o.name + '.' + i.name + 59 ' (' + convert(varchar, o.id) + ', ' + 60 convert(varchar, i.indid) + ')' 61 from 62 sysobjects o, 63 sysindexes i, 64 sysusers u 65 where 66 o.id = i.id 67 and o.uid = u.uid 68 and (i.status & @ind_suspect) != 0 69 70 exec sp_autoformat @fulltabname = #temp1, 71 @selectlist = "'Own.Tab.Ind (Obj_ID, Ind_ID)' = name" 72 end 73 else 74 begin 75 /* 17861, "There are no suspect indexes in database %1!." */ 76 exec sp_getmessage 17861, @msg output 77 select @msg2 = db_name() 78 print @msg, @msg2 79 end 80 end 81 else 82 begin 83 /* 84 ** User has specified a table. 85 ** Check to see that the table name is local to the current database. 86 */ 87 if (@tab_name like '%.%.%') 88 begin 89 /* 17862, "Table must be in the 'current' database." */ 90 raiserror 17862 91 return (1) 92 end 93 94 /* 95 ** Now, see if the table actually exists. 96 */ 97 if (object_id(@tab_name) is null) 98 begin 99 /* 17863, "There is no table named %1! in the 'current' database." */ 100 raiserror 17863, @tab_name 101 return (2) 102 end 103 104 /* 105 ** Now, check for any suspect indexes on this table. 106 */ 107 if exists (select * from sysindexes i 108 where i.id = object_id(@tab_name) 109 and (i.status & @ind_suspect) != 0) 110 begin 111 /* 17864, "Suspect indexes on table %1!:" */ 112 exec sp_getmessage 17864, @msg output 113 print @msg, @tab_name 114 115 select 116 "Own.Tab.Ind (Obj_ID, Ind_ID)" = 117 u.name + "." + o.name + '.' + i.name + 118 ' (' + convert(varchar, o.id) + ', ' + 119 convert(varchar, i.indid) + ')' 120 from 121 sysobjects o, 122 sysindexes i, 123 sysusers u 124 where 125 o.id = i.id 126 and o.uid = u.uid 127 and o.id = object_id(@tab_name) 128 and (i.status & @ind_suspect) != 0 129 end 130 else 131 begin 132 /* 17865, "There are no suspect indexes on table %1!." */ 133 exec sp_getmessage 17865, @msg output 134 print @msg, @tab_name 135 end 136 end 137 return (0) 138
exec sp_procxmode 'sp_indsuspect', 'AnyMode' go Grant Execute on sp_indsuspect to public go
RESULT SETS | |
sp_indsuspect_rset_002 | |
sp_indsuspect_rset_001 |
DEPENDENCIES |
PROCS AND TABLES USED reads table sybsystemprocs..sysindexes ![]() calls proc sybsystemprocs..sp_getmessage ![]() reads table sybsystemprocs..sysusermessages ![]() reads table master..sysmessages (1) ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysusers ![]() reads table sybsystemprocs..sysobjects ![]() calls proc sybsystemprocs..sp_autoformat ![]() reads table master..systypes (1) ![]() reads table master..syscolumns (1) ![]() read_writes table tempdb..#colinfo_af (1) calls proc sybsystemprocs..sp_autoformat ![]() reads table tempdb..syscolumns (1) ![]() reads table tempdb..systypes (1) ![]() calls proc sybsystemprocs..sp_namecrack ![]() |