/*
**	File:		DCM_scanner.sql
**
**	Summary:	Change the database name on line 60 and then run this script. Best
**				run against a log ship because it can be a little intenive on
**				large databases.
**
**	SQL Server Versions:	2005 onwards
**	----------------------------------------------------------------------------
**	Written by Rainer Unwin (c) 2014
**
**	For more scripts check out http://www.rmuniwn.com
**
**	You may alter this code for your own non-commercial purposes (e.g. in a
**	for-sale commercial tool).
**	You may republish altered code as long as you include this copyright and
**	give due credit, however you must obtain my prior permission before blogging
**	this code.
**
**	this code and information are provided "as is" without warranty of
**	any kind, either expressed or implied, including but not limited
**	to the implied warranties of merchantability and/or fitness for a
**	particular purpose. You use the script at your own risk and should
**	always test in a pre-production or test environment before running
**	in production.
*/

/* Drop any potentially existing temp tables that we don't need */
if object_id(N'tempdb..#page', N'U') is not null drop table #page;
go
if object_id(N'tempdb..#results', N'U') is not null drop table #results;
go
if object_id(N'tempdb..#dbfiles', N'U') is not null drop table #dbfiles;
go

/* Create intermediary and final temp tables to hold data */
create table #page (parentobject varchar(200), object varchar(200), field varchar(200), value varchar(200));
go

create table #results (file_id smallint, start_page int, end_page int);
go

create table #dbfiles (id smallint identity, file_id smallint, pages int, iterations smallint);
go

create clustered index cix_results on #results (file_id, start_page);
go

declare @sql nvarchar(200),
	@params nvarchar(100),
	@database sysname,
	@index smallint,
	@max_files smallint,
	@current_file smallint,
	@max_iterations smallint,
	@current_iteration smallint,
	@current_page int;

/* Set the DB that you want to check here */
set @database = N'MyDatabase';

select @sql = N'dbcc page( ' 
				+ @database 
				+ N', @file_id, @page_id, 3 ) with tableresults',
	@params = N'@file_id smallint, @page_id int';

/* Get all the files for the DB */
insert into #dbfiles (file_id, pages)
select file_id,
	size
from sys.master_files
where database_id = db_id(@database)
	and type = 0
order by file_id;

/* Set the interval */
update #dbfiles
set iterations = pages / 511232;

/* Outer loop vars */
select @max_files = max(id),
	@index = 1
from #dbfiles;

/* Outer loop to go through DB files */
while @index <= @max_files
begin

	/* Inner loop vars */
	select @max_iterations = iterations,
		@current_iteration = 0,
		@current_file = file_id
	from #dbfiles
	where id = @index;

	/* Inner loop to go through DCM pages */
	while @current_iteration <= @max_iterations
	begin
		
		/* Set the current page so we can pass it in as a parameter */
		set @current_page = (6 + (511232 * @current_iteration))

		insert into #page
		exec sp_executesql @sql,
			@params,
			@file_id = @current_file,
			@page_id = @current_page;

		/* Use the results to get the required details */
		insert into #results (file_id, start_page, end_page)
		select substring(field, 2, (charindex(':', field)) - 2) as file_id,
			substring(field, (charindex(':', field)) + 1, charindex(')', field) - charindex(':', field) - 1) as start_page,
			case when charindex('(', field, charindex('-', field)) = 0
				then cast(cast(substring(field, (charindex(':', field)) + 1, charindex(')', field) - charindex(':', field) - 1) as int) + 8 as varchar)
				else substring(field, charindex(':', field, charindex('-', field)) + 1, (charindex(')', field, charindex(':', field, charindex('-', field)) - 2)) - (charindex(':', field, charindex('-', field)) + 1))
			end as end_page
		from #page
		where parentobject like 'diff_map%'
			and value = '    CHANGED'
		option (recompile);
		
		truncate table #page;
		
		set @current_iteration += 1;
	
	end;
	
	set @index += 1;
end;
go

/* Get total page change count */
declare @total_pages bigint;
select @total_pages = sum(pages)
from #dbfiles

select @total_pages as total_pages,
	sum(end_page - start_page) as altered_pages,
	cast(
		(100 * sum(end_page - start_page)) 
		/ (cast(@total_pages as numeric(19, 2))) as numeric(5, 2)
	) as percent_changed
from #results;

/* Get details of what has been changed and where */
select file_id,
	start_page,
	end_page,
	end_page - start_page as altered_pages
from #results
order by file_id,
	start_page,
	altered_pages;
go

/* Don't leave cleanup to chance */
if object_id(N'tempdb..#page', N'U') is not null drop table #page;
go
if object_id(N'tempdb..#results', N'U') is not null drop table #results;
go
if object_id(N'tempdb..#dbfiles', N'U') is not null drop table #dbfiles;
go