1
2 /*
3 ** Messages for "sp_flushstats"
4 **
5 ** 17460, "Object must be in the current database."
6 ** 17461, "Object does not exist in this database."
7 */
8
9 create procedure sp_flushstats
10 @objname varchar(767) = NULL /* object name we're after */
11 as
12
13 declare @objid int
14 declare @dbid int
15
16 if @@trancount = 0
17 begin
18 set chained off
19 end
20
21 set transaction isolation level 1
22
23 set nocount on
24
25 select @dbid = db_id()
26
27 if @objname is NULL
28 begin
29
30 declare cs cursor for
31 select O.id
32 from sysobjects O
33 where O.sysstat & 15 in (1, 3)
34 open cs
35 fetch cs into @objid
36
37 set switch on print_output_to_client with no_info
38
39 while @@sqlstatus = 0
40 begin
41 dbcc flushstats(@dbid, @objid)
42 fetch cs into @objid
43 end
44
45 set switch off print_output_to_client with no_info
46
47 close cs
48 deallocate cursor cs
49 return (0)
50 end
51
52 /*
53 ** Make sure the @objname is local to the current database.
54 */
55 if @objname like "%.%.%" and
56 substring(@objname, 1, charindex(".", @objname) - 1) != db_name()
57 begin
58 /* 17460, "Object must be in the current database." */
59 raiserror 17460
60 return (1)
61 end
62
63 /*
64 ** Now check to see if the @objname is in sysobjects.
65 */
66 select @objid = object_id(@objname)
67
68 if not exists (select *
69 from sysobjects
70 where id = @objid
71 and sysstat & 15 in (1, 3))
72 begin
73 /* 17461, "No table as specified exists in this database." */
74 raiserror 17461
75 return (1)
76 end
77
78 set switch on print_output_to_client with no_info
79
80 dbcc flushstats(@dbid, @objid)
81
82 set switch off print_output_to_client with no_info
83
84 return (0)
85
exec sp_procxmode 'sp_flushstats', 'AnyMode'
go
Grant Execute on sp_flushstats to public
go
CUNU 3 Cursor not updated: cursor should contain 'for read only' clause cs
CUPD 2 Updatable Cursor Marker (updatable by default)