CREATE proc dbo.init_index_operational_stats
as
--
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights. 
-- Use of included script samples are subject to the terms specified at 
-- http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- 
-- create indexstats table if it doesn't exist, otherwise truncate
--
set nocount on
if not exists (select 1 from dbo.sysobjects where 
id=object_id(N'[dbo].[indexstats]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
    create table dbo.indexstats (
        database_id smallint NOT NULL
        ,object_id int NOT NULL
        ,index_id int NOT NULL
        ,partition_number int NOT NULL
        ,leaf_insert_count bigint NOT NULL
        ,leaf_delete_count bigint NOT NULL
        ,leaf_update_count bigint NOT NULL
        ,leaf_ghost_count bigint NOT NULL
        ,nonleaf_insert_count bigint NOT NULL
        ,nonleaf_delete_count bigint NOT NULL
        ,nonleaf_update_count bigint NOT NULL
        ,leaf_allocation_count bigint NOT NULL
        ,nonleaf_allocation_count bigint NOT NULL
        ,leaf_page_merge_count bigint NOT NULL
        ,nonleaf_page_merge_count bigint NOT NULL
        ,range_scan_count bigint NOT NULL
        ,singleton_lookup_count bigint NOT NULL
        ,forwarded_fetch_count bigint NOT NULL
        ,lob_fetch_in_pages bigint NOT NULL
        ,lob_fetch_in_bytes bigint NOT NULL
        ,lob_orphan_create_count bigint NOT NULL
        ,lob_orphan_insert_count bigint NOT NULL
        ,row_overflow_fetch_in_pages bigint NOT NULL
        ,row_overflow_fetch_in_bytes bigint NOT NULL
        ,column_value_push_off_row_count bigint NOT NULL
        ,column_value_pull_in_row_count bigint NOT NULL
        ,row_lock_count bigint NOT NULL
        ,row_lock_wait_count bigint NOT NULL
        ,row_lock_wait_in_ms bigint NOT NULL
        ,page_lock_count bigint NOT NULL
        ,page_lock_wait_count bigint NOT NULL
        ,page_lock_wait_in_ms bigint NOT NULL
        ,index_lock_promotion_attempt_count bigint NOT NULL
        ,index_lock_promotion_count bigint NOT NULL
        ,page_latch_wait_count bigint NOT NULL
        ,page_latch_wait_in_ms bigint NOT NULL
        ,page_io_latch_wait_count bigint NOT NULL
        ,page_io_latch_wait_in_ms bigint NOT NULL
        ,now datetime default getdate())
else 	truncate table dbo.indexstats
go
 
add_column
create proc dbo.add_column (
            @add_stmt varchar(500) output,
            @find varchar(100)=NULL,
            @cols_containing varchar(500)=NULL,	
            @col_stmt varchar(max))
as
--
-- This stored procedure is provided "AS IS" with no warranties, and 
-- confers no rights. 
-- Use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- @add_stmt is the result passed back to the caller
-- @find is a keyword from @cols_containing
-- @cols_containing is the list of keywords to include in the report
-- @col_stmt is the statement that will be compared with @find. 
--			If @col_stmt contains @find, include this statement. 
--			set @add_stmt = @col_stmt
-- 
declare @length int, @strindex int, @EOS bit
if @cols_containing is NULL 
    begin
        select @add_stmt=@col_stmt
        return
    end
select @add_stmt = '', @EOS = 0

while @add_stmt is not null and @EOS = 0 
            @dbid=-1,
    select @strindex = charindex(',',@cols_containing)
    if @strindex = 0
            select @find = @cols_containing, @EOS = 1
    else
    begin
        select @find = substring(@cols_containing,1,@strindex-1)
        select @cols_containing =      
            substring(@cols_containing,
                      @strindex+1,
                      datalength(@cols_containing) - @strindex)
    end
    select @add_stmt=case
--when @cols_containing is NULL then NULL
    when charindex(@find,@col_stmt) > 0 then NULL
    else ''
    end
end
--- NULL indicates this statement is to be passed back through out parm 
@add_stmt
if @add_stmt is NULL select @add_stmt=@col_stmt
go
