-- Sample SQL scripts for creating stored procedure "test_db"
if exists( select 1 from sysobjects
where name = 'test_db'
and type = 'P'
and (sysstat & 7) = 4 )
drop proc test_db
go
create procedure test_db ( @db_name varchar(255),
@index_flag int = 1,
@datachg_threshold float = 80.0,
@rowcnt_threshold int = 10,
@pagecnt_threshold int = 0,
@step_value int = 20,
@consumers int = 0,
@spercent int = 10 )
as
begin
print 'test_db( @db_name = %1!, @index_flag = %2!, datachg_threshold = %3!,
rowcnt_threshold = %4!, pagecnt_threshold = %5!, step_value = %6!, consumers = %7!,
sampling_rate = %8! )', @db_name, @index_flag, @datachg_threshold,
@rowcnt_threshold, @pagecnt_threshold, @step_value, @consumers, @spercent
-- supported syntax:
-- update statistics <table_name>
-- [using step values]
-- [with consumers = consumers],
-- [sampling = spercent percent]
--
-- update index statistics <table_name>
-- [using step values]
-- [with consumers = consumers],
-- [sampling = spercent percent]
-- Example:
-- update statistics authors with sampling = 5 percent
--
declare @updt_cmd varchar(600)
, @status int
, @err int
, @sp_name varchar(30)
, @msgno int
, @table_name varchar(255)
, @tbl_id int
, @max_id int
, @type char(2)
, @row_cnt int
, @db_id int
, @db_quoted_name varchar(255)
-- check for required input values
-- must have a db name
select @db_name = ltrim(rtrim(@db_name))
if( @db_name is NULL )
begin
raiserror 19048, @sp_name, 'Database Name'
return -@@error
end
-- Get database quoted name
exec @err = sp_jst_get_object_quoted_name @db_name, @db_quoted_name out
if( @err < 0 )
return @err
-- Find all the tables in this database and check each table to see
-- if it's statistics need updating.
select @tbl_id = 100
, @status = 0
, @db_id = db_id(@db_name)
select @max_id = MAX(id)
from sysobjects
where type in ("U ","S ")
while( @tbl_id <= @max_id )
begin
if( @tbl_id = @max_id )
begin
break
end
select @tbl_id = MIN(id)
from sysobjects
where id > @tbl_id and ( type = "U "
and name not like 'spt_%')
--or (type = "S " and name not like 'spt_%') )
--select @row_cnt = rowcnt from systabstats where id = @tbl_id
-- and indid=0
select @row_cnt = row_count( @db_id, @tbl_id )
select @table_name = name from sysobjects where id = @tbl_id
--print 'table %1! has %2! rows', @table_name, @row_cnt
if( @row_cnt >= @rowcnt_threshold )
begin
select @table_name = name from sysobjects where id = @tbl_id
--call the update statistic stored proc
select @updt_cmd = @db_quoted_name
+ "..sp_jst_update_statistics "
print 'executing: %1!,%2!,%3!,%4!,%5!,%6!,%7!,%8!,%9!',
@updt_cmd,
@table_name,
@datachg_threshold,
@rowcnt_threshold,
@pagecnt_threshold,
@consumers,
@step_value,
@spercent,
@index_flag
exec @err = @updt_cmd @table_name=@table_name
, @datachg_threshold=@datachg_threshold
, @rowcnt_threshold=@rowcnt_threshold
, @pagecnt_threshold=@pagecnt_threshold
, @consumers=@consumers
, @step_value=@step_value
, @spercent=@spercent
, @index_flag=@index_flag
if( @err != 0 )
select @status = @err
end
end
--print 'status is %1!', @status
return @status
end
go
if exists( select 1 from sysobjects
where name = 'test_db'
and type = 'P'
and (sysstat & 7) = 4 )
begin
print 'created stored procedure test_db'
grant execute on test_db to public
end
go
