/******************************************************************************
 SCRIPT:	dbcheck.sql
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.

 OWNER:		RonMart

 PURPOSE:	Creates a diff report from the output of dbcheck.

******************************************************************************/
set nocount on
set quoted_identifier on

use SQL7Upgrade
GO

dump tran SQL7Upgrade with no_log
GO

/******************************************************************************
 SCRIPT:	cleanSQL7Upgrade.sql

 OWNER:		RonMart

 PURPOSE:	Clears out any user tables that may be laying around. This is
		done to avoid autogrow, since SQL7Upgrade should be large enough
		for the work done in the remainder of this script.
******************************************************************************/
DECLARE @tbname nvarchar(256)

declare tbname_cursor cursor
local forward_only
for
select name from sysobjects where type = 'U'
and name not in 
('objects', 'columns', 'indexes', 'fkeys', 'check6x', 'exclude', 'check70')

OPEN tbname_cursor
  
FETCH NEXT FROM tbname_cursor 
INTO @tbname

WHILE @@fetch_status = 0
BEGIN

	execute('drop table ' + @tbname)

	FETCH NEXT FROM tbname_cursor INTO @tbname
END	

CLOSE tbname_cursor
DEALLOCATE tbname_cursor

dump tran SQL7Upgrade with no_log
GO

/******************************************************************************
 SCRIPT:	update_objects.sql

 OWNER:		MattDo

 PURPOSE:	update the SQL7Upgrade.dbo.objects table to get the correct object 
		ID's set
******************************************************************************/
/* BEGIN LOCALIZABLE STRINGS */
print 'Running Update Objects...'
declare @tloginmap_missing nvarchar(128)
set @tloginmap_missing = 'master..t_loginmap was not found, so some logins may be mapped incorrectly'
/* END LOCALIZABLE STRINGS */

-- Update the owner names from the login map
if (select name from master..sysobjects where name = 't_loginmap' and type = 'U') is not null
begin
	UPDATE objects SET owner = tl.ntname 
		FROM master..t_loginmap tl 
		WHERE tl.sqlname = objects.owner

	UPDATE indexes SET owner = tl.ntname 
		FROM master..t_loginmap tl 
		WHERE tl.sqlname = indexes.owner

	UPDATE fkeys SET fkowner = tl.ntname
		FROM master..t_loginmap tl 
		WHERE tl.sqlname = fkeys.fkowner

	UPDATE fkeys SET pkowner = tl.ntname
		FROM master..t_loginmap tl 
		WHERE tl.sqlname = fkeys.pkowner
end
else
	print @tloginmap_missing
GO

DECLARE @dbname nvarchar(256), @message nvarchar(255)

declare dbname_cursor cursor
local forward_only
for
select distinct(dbname) from SQL7Upgrade.dbo.objects o 

OPEN dbname_cursor
  
FETCH NEXT FROM dbname_cursor 
INTO @dbname

WHILE @@fetch_status = 0
BEGIN
	select @message='use '+@dbname+' UPDATE o set o.newid = so.id from SQL7Upgrade.dbo.objects o, sysobjects so, sysusers su '
	select @message=@message+'where o.dbname='''+@dbname+''' '+'and so.uid=su.uid and o.owner=su.name and o.objname=so.name and o.type=so.type'
	execute( @message)

	FETCH NEXT FROM dbname_cursor INTO @dbname
END	

CLOSE dbname_cursor
DEALLOCATE dbname_cursor
	
GO

/******************************************************************************
 SCRIPT:	exists_on_source_only.sql

 OWNER:		MattDo

 PURPOSE:	Lists all of the objects that did not transfer over 
		successfully from the source server
		
******************************************************************************/
if exists(select name from SQL7Upgrade..sysobjects where name='rpt6xOnly')
	drop table SQL7Upgrade.[dbo].[rpt6xOnly]

dump tran SQL7Upgrade with no_log

GO

/* BEGIN LOCALIZABLE STRINGS */
print 'Running exists on source only...'
declare @dbname 	nvarchar(64)
declare @objectname 	nvarchar(64)
declare @ownername 	nvarchar(64)
declare @typename 	nvarchar(64)

set @dbname	 	= 'Database Name'
set @objectname 	= 'Object Name'
set @ownername 		= 'Owner Name'
set @typename 		= 'Type'
/* END LOCALIZABLE STRINGS */

exec('CREATE TABLE SQL7Upgrade.[dbo].[rpt6xOnly] ([' 
	+ @dbname + '] [nvarchar] (30) NOT NULL ,['
	+ @typename + '] [nvarchar] (30) NOT NULL ,['
	+ @objectname + '] [nvarchar] (30) NOT NULL ,['
	+ @ownername  + '] [nvarchar] (30) NOT NULL)')

GO

insert SQL7Upgrade.dbo.rpt6xOnly select o.dbname, o.type, o.objname, o.owner
from SQL7Upgrade.dbo.objects o 
where o.newid=0 and o.objname not in (select distinct(ex.objname) from SQL7Upgrade.dbo.exclude ex 
	where ex.dbname=o.dbname and ex.owner=o.owner and ex.objname=o.objname and ex.type=o.type) 
order by dbname

GO


/******************************************************************************
 SCRIPT:	columns2.sql

 OWNER:		Richard Waymire & RonMart

 PURPOSE:	
		
******************************************************************************/
/* BEGIN LOCALIZABLE STRINGS */
print 'Running Columns check...'

declare @databasename 	nvarchar(64)
declare @tablename 	nvarchar(64)
declare @columnname 	nvarchar(64)

set @databasename 	= 'Database Name'
set @tablename 		= 'Table Name'
set @columnname 	= 'Column Name'
/* END LOCALIZABLE STRINGS */

dump tran SQL7Upgrade with no_log

if exists(select name from SQL7Upgrade..sysobjects where name='tmpColumns')
	drop table SQL7Upgrade.[dbo].[tmpColumns]

CREATE TABLE SQL7Upgrade.[dbo].[tmpColumns] (
	[dbname] [sysname] NOT NULL ,
	[tabname] [sysname] NOT NULL ,
	[id] [int] NOT NULL ,
	[colid] [tinyint] NOT NULL ,
	[colname] [sysname] NOT NULL ,
	[datatype] [tinyint] NOT NULL ,
	[nullable] [tinyint] NOT NULL)

if exists(select name from SQL7Upgrade..sysobjects where name='rptColumns6x')
	drop table SQL7Upgrade.[dbo].[rptColumns6x]

exec('CREATE TABLE SQL7Upgrade.[dbo].[rptColumns6x] ([' 
	+ @databasename + '][nvarchar] (30) NOT NULL ,['
	+ @tablename + '] [nvarchar] (30) NOT NULL ,['
	+ @columnname + '] [nvarchar] (30) NOT NULL)')

if exists(select name from SQL7Upgrade..sysobjects where name='rptColumns70')
	drop table SQL7Upgrade.[dbo].[rptColumns70]

exec('CREATE TABLE SQL7Upgrade.[dbo].[rptColumns70] ([' 
	+ @databasename + '][nvarchar] (30) NOT NULL ,['
	+ @tablename + '] [nvarchar] (30) NOT NULL ,['
	+ @columnname + '] [nvarchar] (30) NOT NULL)')

GO

-- Next, open a cursor on sysdatabases & loop through each database.

declare @dbname nvarchar(256)
declare dbcursor cursor for
select distinct(dbname) from SQL7Upgrade.dbo.objects

open dbcursor
--look at each database
fetch dbcursor into @dbname
while @@fetch_status = 0
	begin
		delete SQL7Upgrade.dbo.tmpColumns
		insert SQL7Upgrade.dbo.tmpColumns select * from SQL7Upgrade.dbo.columns where dbname = @dbname
		
		--look up each column within a db
		exec ('delete SQL7Upgrade.dbo.tmpColumns from ' + @dbname + '..syscolumns sc, SQL7Upgrade.dbo.objects o,'+
			' SQL7Upgrade.dbo.tmpColumns c where o.newid = sc.id   and c.id = o.id' +
			' and c.colid = sc.colid and c.colname = sc.name and c.datatype = sc.type' +
			' and ((c.nullable = 0 and sc.isnullable = 0)' +
			' or (c.nullable = 8 and sc.isnullable = 1))')

		exec ('delete SQL7Upgrade.dbo.tmpColumns from SQL7Upgrade.dbo.exclude ex , SQL7Upgrade.dbo.objects o 
				where ex.dbname=o.dbname and ex.owner=o.owner and ex.objname=o.objname and ex.type=o.type and tmpColumns.id=o.id') 
		
		--clean out old system datatypes that match their 7.0 equivalent
		exec ('delete SQL7Upgrade.dbo.tmpColumns from ' + @dbname + '..syscolumns sc, SQL7Upgrade.dbo.objects o,'+
			' SQL7Upgrade.dbo.tmpColumns c where o.newid = sc.id   and c.id = o.id' +
			' and c.colid = sc.colid'+
			' and ((c.nullable = 0 and sc.isnullable = 0)' +
			' or (c.nullable = 8 and sc.isnullable = 1))'+
			' and (((c.datatype = 108) and (sc.type = 63))'+
			' or ((c.datatype = 110) and (sc.type = 60))'+
			' or ((c.datatype = 38) and (sc.type = 56))'+
			' or ((c.datatype = 38) and (sc.type = 52))'+
			' or ((c.datatype = 38) and (sc.type = 48))'+
			' or ((c.datatype = 109) and (sc.type = 62))'+
			' or ((c.datatype = 106) and (sc.type = 55))'+
			' or ((c.datatype = 61) and (sc.type = 61)))')

		--OK, so anything left in SQL7Upgrade.dbo.tmpColumns wasn't added correctly
		insert rptColumns6x select dbname, tabname, colname from SQL7Upgrade.dbo.tmpColumns 

		--reprime SQL7Upgrade.dbo.tmpColumns
		delete SQL7Upgrade.dbo.tmpColumns
		insert SQL7Upgrade.dbo.tmpColumns select * from SQL7Upgrade.dbo.columns
		update SQL7Upgrade.dbo.tmpColumns set id = o.newid from SQL7Upgrade.dbo.objects o, SQL7Upgrade.dbo.tmpColumns c where o.id = c.id and o.dbname = @dbname
		--now, any columns we added to existing tables.


		exec ('insert SQL7Upgrade.dbo.rptColumns70 select '''+ @dbname +''', so.name, sc.name  from ' + @dbname + '..syscolumns sc, ' +
		      @dbname + '..sysobjects so where sc.id in ' +
		      '(select newid from objects where type = ''U'' and dbname = ''' +@dbname+ ''') and colid not in (select c.colid ' +
		      'from tmpColumns c where c.id = sc.id ) and so.id = sc.id')

		fetch next from dbcursor into @dbname
	end
close dbcursor
deallocate dbcursor

drop table SQL7Upgrade.[dbo].[tmpColumns]
GO

/******************************************************************************
 SCRIPT:	Indexes.sql

 OWNER:		Richard Waymire & RonMart

 PURPOSE:	
		
******************************************************************************/
/* BEGIN LOCALIZABLE STRINGS */
print 'Running Indexes check...'

declare @databasename 	nvarchar(64)
declare @ownername 	nvarchar(64)
declare @tablename 	nvarchar(64)
declare @indexname 	nvarchar(64)
declare @columnname 	nvarchar(64)

set @databasename 	= 'Database Name'
set @ownername 		= 'Owner Name'
set @tablename 		= 'Table Name'
-- NOTE: The following has to be localized twice in this section using the same identical name
set @indexname 		= 'Index Name'
set @columnname 	= 'Column Name'
/* END LOCALIZABLE STRINGS */

dump tran SQL7Upgrade with no_log

if exists(select name from SQL7Upgrade..sysobjects where name='rptIndexes6x')
	drop table SQL7Upgrade.[dbo].[rptIndexes6x]

exec('CREATE TABLE SQL7Upgrade.[dbo].[rptIndexes6x] ([' 
	+ @databasename + '] [nvarchar] (30) NOT NULL ,['
	+ @ownername + '] [nvarchar] (30) NOT NULL ,['
	+ @tablename + '] [nvarchar] (30) NOT NULL ,['
	+ @indexname + '] [nvarchar] (30) NOT NULL ,['
	+ @columnname + '] [nvarchar] (30) NOT NULL)')

if exists(select name from SQL7Upgrade..sysobjects where name='rptIndexes70')
	drop table SQL7Upgrade.[dbo].[rptIndexes70]

exec('CREATE TABLE SQL7Upgrade.[dbo].[rptIndexes70] ([' 
	+ @databasename + '] [nvarchar] (30) NOT NULL ,['
	+ @ownername + '] [nvarchar] (30) NOT NULL ,['
	+ @tablename + '] [nvarchar] (30) NOT NULL ,['
	+ @indexname + '] [nvarchar] (128) NOT NULL ,['
	+ @columnname + '] [nvarchar] (128) NOT NULL)')

if exists (select name from SQL7Upgrade.dbo.sysobjects where name = 'tmpIndexes')
	drop table SQL7Upgrade.dbo.tmpIndexes

CREATE TABLE SQL7Upgrade.[dbo].[tmpIndexes] (
	[dbname] [sysname] NOT NULL ,
	[owner] [sysname] NOT NULL ,
	[tabname] [sysname] NOT NULL ,
	[indexname] [sysname] NOT NULL ,
	[colname] [sysname] NOT NULL ,
	[ordinal] [tinyint] NOT NULL ,
	[uniq] [int] NOT NULL ,
	[cluster] [int] NOT NULL ,
	[pk] [int] NOT NULL ,
	[uc] [int] NOT NULL 
)

if exists (select name from SQL7Upgrade.dbo.sysobjects where name = 'tmpIndexes2')
	drop table SQL7Upgrade.dbo.tmpIndexes2

CREATE TABLE SQL7Upgrade.[dbo].[tmpIndexes2] (
	[dbname] [sysname] NOT NULL ,
	[owner] [sysname] NOT NULL ,
	[tabname] [sysname] NOT NULL ,
	[indexname] [sysname] NOT NULL ,
	[colname] [sysname] NOT NULL ,
	[ordinal] [tinyint] NOT NULL ,
	[uniq] [int] NOT NULL ,
	[cluster] [int] NOT NULL ,
	[pk] [int] NOT NULL ,
	[uc] [int] NOT NULL 
) 

if exists (select name from SQL7Upgrade.dbo.sysobjects where name = 'tmpIndexes3')
	drop table SQL7Upgrade.dbo.tmpIndexes3

CREATE TABLE SQL7Upgrade.[dbo].[tmpIndexes3] (
	dbname sysname not null,
	id int not null,
	indid int not null,
	key_col sysname not null,
	key_seq smallint not null
)

if exists(select name from SQL7Upgrade..sysobjects where name='vupg_indexes')
	drop view [dbo].[vupg_indexes]

GO

/* BEGIN LOCALIZABLE STRINGS */
-- NOTE: The following has to be localized twice in this section using the same identical name
declare @indexname 	nvarchar(64)
-- This MUST match the previous @indexname string
set @indexname 		= 'Index Name'
/* END LOCALIZABLE STRINGS */

declare @dbname nvarchar(256)
declare @id int
declare @indid tinyint
declare @i int
declare @thiskey sysname
declare @key sysname
declare @objname sysname
declare @ownername sysname

declare dbcursor cursor for
select distinct(dbname) from SQL7Upgrade.dbo.objects

open dbcursor
--look at each database
fetch dbcursor into @dbname
while @@fetch_status = 0
	begin
		-- POPULATE tmpIndexes3
		print @dbname

		exec ('declare ind_curs insensitive cursor for select ''' + 
			@dbname + ''', su.name, si.id, si.indid, so.name from ' + @dbname + ' .dbo.sysindexes si,' +
			@dbname + '.dbo.sysobjects so, ' + @dbname + '.dbo.sysusers su ' +
			'where si.indid > 0 and si.indid < 255 and si.id > 100 ' +
			' and si.id = so.id and so.uid = su.uid order by si.id, si.indid')
		open ind_curs
		fetch ind_curs into @dbname, @ownername, @id, @indid, @objname

		-- Now check out each index, figure out its keys.
		while @@fetch_status >= 0
			begin
			-- First we'll figure out what the keys are.
				print @objname
				select @objname = @dbname+'.' + @ownername + '.'+@objname
				select @key = index_col(@objname, @indid, 1),
					@i = 2, @thiskey = index_col(@objname, @indid, 2)

				if @key is not null
					insert tmpIndexes3 values (@dbname, @id, @indid, @key, 1)

				while (@thiskey is not null )
					begin
						insert tmpIndexes3 values (@dbname, @id, @indid, @thiskey, @i)
						set @i = @i + 1
						select @thiskey = index_col(@objname, @indid, @i)
					end
				-- Next index
				fetch ind_curs into @dbname, @ownername, @id, @indid, @objname
			end
		close ind_curs
		deallocate ind_curs
		--DONE POPULATING tmpIndexes3
		exec ('create view vupg_indexes as SELECT ''' + @dbname + ''' dbname, ' +
		      'u.name owner, o.name tablename, x.name indexname,' +
		      'ti.key_col, ti.key_seq sequence,' +
                      '(x.status & 2)/2 UNIQUENESS,(x.status & 16)/16 CLUSTER, ' +
	 	      '(x.status & 2048)/2048 PK,(x.status & 4096)/4096 UNQ_CONSTR '+
		      'FROM ' + @dbname + '..sysindexes x, ' + @dbname + '..syscolumns c, ' +
			@dbname + '..sysobjects o, tmpIndexes3 ti, ' + @dbname + '..sysusers u ' +
			' WHERE  x.id = o.id AND x.id = c.id AND o.uid = u.uid ' +
	  	      'AND c.name  = ti.key_col and c.id = ti.id and ti.id = o.id and ti.indid = x.indid ' +
			' AND x.indid > 0 and x.indid < 255 ' +
                      'AND o.id > 100 ')

		-- populate tmpIndexes with sql 7 indexes
		exec ('insert tmpIndexes select * from vupg_indexes where key_col IS NOT NULL')				

		-- populate tmpIndexes2 with a copy of the 6.5 info
		insert tmpIndexes2 select * from indexes where dbname = @dbname

		-- delete correctly created indexes from indexes table.
		delete tmpIndexes2 from tmpIndexes2 i, tmpIndexes i2
		where i.dbname = i2.dbname
		  and i.owner = i2.owner
		  and i.tabname = i2.tabname
		  and i.indexname = i2.indexname
		  and i.colname = i2.colname
		  and i.ordinal = i2.ordinal
		  and i.uniq = i2.uniq
		  and i.cluster = i2.cluster
		  and i.pk = i2.pk
		  and i.uc = i2.uc

		-- delete correctly created Primary Keys & Unique Constraints from indexes table.
		delete tmpIndexes2 from tmpIndexes2 i, tmpIndexes i2
		where i.dbname = i2.dbname
		  and i.owner = i2.owner
		  and i.tabname = i2.tabname
		  and i.colname = i2.colname
		  and i.ordinal = i2.ordinal
		  and i.uniq = i2.uniq
		  and i.cluster = i2.cluster
		  and i.pk = i2.pk
		  and i.uc = i2.uc
		  and (i.indexname like 'PK%' 
		   or i.indexname like 'UQ%')

		-- get rid of quasi system tables
		delete tmpIndexes2 from tmpIndexes2 i
		where i.dbname = 'master'
		  and (i.tabname = 'spt_values'
		       or i.tabname = 'spt_provider_types')

		-- added by petersad to indexes are removed that are in the exclude table
		delete tmpIndexes2 from tmpIndexes2 i, exclude e
		where i.dbname = 'master'
		   and e.objname = i.indexname
		  
		insert SQL7Upgrade.[dbo].[rptIndexes6x] select dbname,owner,tabname,indexname,colname from tmpIndexes2 where dbname = @dbname

		-- now, clean up tmpIndexes (7.0) info where it matches 6.5
		delete tmpIndexes
		from indexes i, tmpIndexes i2
		where i.dbname = i2.dbname
		  and i.owner = i2.owner
		  and i.tabname = i2.tabname
		  and i.indexname = i2.indexname
		  and i.colname = i2.colname
		  and i.ordinal = i2.ordinal
		  and i.uniq = i2.uniq
		  and i.cluster = i2.cluster
		  and i.pk = i2.pk
		  and i.uc = i2.uc

		-- and again, account for primary keys
		delete tmpIndexes
		from indexes i, tmpIndexes i2
		where i.dbname = i2.dbname
		  and i.owner = i2.owner
		  and i.tabname = i2.tabname
		  and i.colname = i2.colname
		  and i.ordinal = i2.ordinal
		  and i.uniq = i2.uniq
		  and i.cluster = i2.cluster
		  and i.pk = i2.pk
		  and i.uc = i2.uc
		  and (i.indexname like 'PK%'
		   or i.indexname like 'UQ%')

		-- get rid of quasi system tables
		delete tmpIndexes from tmpIndexes i
		where i.dbname = 'master'
		  and (i.tabname = 'spt_values')



		-- get rid of autostat system tables
		delete tmpIndexes from tmpIndexes i
		where i.dbname = 'master'
		  and (i.indexname in 
				(select name from master..sysindexes si
		                  where si.status & 0x20 = 0x20)
                       )

		insert SQL7Upgrade.[dbo].[rptIndexes70] select dbname,owner,tabname,indexname,colname from tmpIndexes where dbname = @dbname

		-- Fixed by petersad; bug# 226318
		exec ('delete SQL7Upgrade.[dbo].[rptIndexes70] where [' + @indexname + '] in (''unc1sysarticleupdates'',''unc1sysschemaarticles'',''unc1MSpub_identity_range'')')


		-- loop clean up
		delete tmpIndexes
		delete tmpIndexes2
		delete tmpIndexes3 -- prime the table
		drop view vupg_indexes
		fetch next from dbcursor into @dbname
	END -- while loop
-- all done, clean up
CLOSE dbcursor
DEALLOCATE dbcursor

DROP TABLE tmpIndexes
DROP TABLE tmpIndexes2
drop table tmpIndexes3

GO

/******************************************************************************
 SCRIPT:	FKeys.sql

 OWNER:		Richard Waymire & RonMart

 PURPOSE:	
		
******************************************************************************/
/* BEGIN LOCALIZABLE STRINGS */
print 'Running Foreign Key check...'

declare @databasename 	nvarchar(64)
declare @fkowner 	nvarchar(64)
declare @fktable 	nvarchar(64)
declare @pkowner 	nvarchar(64)
declare @pktable 	nvarchar(64)

set @databasename 	= 'Database Name'
set @fkowner  		= 'Foreign Key Owner'
set @fktable  		= 'Foreign Key Table'
set @pkowner	 	= 'Primary Key Owner'
set @pktable	 	= 'Primary Key Table'
/* END LOCALIZABLE STRINGS */

dump tran SQL7Upgrade with no_log

if exists(select name from SQL7Upgrade..sysobjects where name='rptFKey6x')
	drop table SQL7Upgrade.[dbo].[rptFKey6x]

exec('CREATE TABLE SQL7Upgrade.[dbo].[rptFKey6x] ([' 
	+ @databasename + '] [nvarchar] (30) NOT NULL ,['
	+ @fkowner + '] [nvarchar] (30) NOT NULL ,['
	+ @fktable + '] [nvarchar] (30) NOT NULL ,['
	+ @pkowner + '] [nvarchar] (30) NOT NULL ,['
	+ @pktable + '] [nvarchar] (30) NOT NULL)')

if exists(select name from SQL7Upgrade..sysobjects where name='rptFKey70')
	drop table SQL7Upgrade.[dbo].[rptFKey70]

exec('CREATE TABLE SQL7Upgrade.[dbo].[rptFKey70] ([' 
	+ @databasename + '] [nvarchar] (30) NOT NULL ,['
	+ @fkowner + '] [nvarchar] (30) NOT NULL ,['
	+ @fktable + '] [nvarchar] (30) NOT NULL ,['
	+ @pkowner + '] [nvarchar] (30) NOT NULL ,['
	+ @pktable + '] [nvarchar] (30) NOT NULL)')

if exists (select name from SQL7Upgrade.dbo.sysobjects where name = 'tmpFKeys')
	drop table tmpFKeys

CREATE TABLE SQL7Upgrade.[dbo].[tmpFKeys] (
	[dbname] [sysname] NOT NULL ,
	[fkowner] [sysname] NOT NULL ,
	[fktabname] [sysname] NOT NULL ,
	[pkowner] [sysname] NOT NULL ,
	[pktabname] [sysname] NOT NULL ,
	[fkey1] [tinyint] NOT NULL ,
	[fkey2] [tinyint] NOT NULL ,
	[fkey3] [tinyint] NOT NULL ,
	[fkey4] [tinyint] NOT NULL ,
	[fkey5] [tinyint] NOT NULL ,
	[fkey6] [tinyint] NOT NULL ,
	[fkey7] [tinyint] NOT NULL ,
	[fkey8] [tinyint] NOT NULL ,
	[fkey9] [tinyint] NOT NULL ,
	[fkey10] [tinyint] NOT NULL ,
	[fkey11] [tinyint] NOT NULL ,
	[fkey12] [tinyint] NOT NULL ,
	[fkey13] [tinyint] NOT NULL ,
	[fkey14] [tinyint] NOT NULL ,
	[fkey15] [tinyint] NOT NULL ,
	[fkey16] [tinyint] NOT NULL ,
	[rkey1] [tinyint] NOT NULL ,
	[rkey2] [tinyint] NOT NULL ,
	[rkey3] [tinyint] NOT NULL ,
	[rkey4] [tinyint] NOT NULL ,
	[rkey5] [tinyint] NOT NULL ,
	[rkey6] [tinyint] NOT NULL ,
	[rkey7] [tinyint] NOT NULL ,
	[rkey8] [tinyint] NOT NULL ,
	[rkey9] [tinyint] NOT NULL ,
	[rkey10] [tinyint] NOT NULL ,
	[rkey11] [tinyint] NOT NULL ,
	[rkey12] [tinyint] NOT NULL ,
	[rkey13] [tinyint] NOT NULL ,
	[rkey14] [tinyint] NOT NULL ,
	[rkey15] [tinyint] NOT NULL ,
	[rkey16] [tinyint] NOT NULL 
) 
GO

if exists (select name from SQL7Upgrade.dbo.sysobjects where name = 'tmpFKeys2')
	drop table tmpFKeys2

CREATE TABLE SQL7Upgrade.[dbo].[tmpFKeys2] (
	[dbname] [sysname] NOT NULL ,
	[fkowner] [sysname] NOT NULL ,
	[fktabname] [sysname] NOT NULL ,
	[pkowner] [sysname] NOT NULL ,
	[pktabname] [sysname] NOT NULL ,
	[fkey1] [tinyint] NOT NULL ,
	[fkey2] [tinyint] NOT NULL ,
	[fkey3] [tinyint] NOT NULL ,
	[fkey4] [tinyint] NOT NULL ,
	[fkey5] [tinyint] NOT NULL ,
	[fkey6] [tinyint] NOT NULL ,
	[fkey7] [tinyint] NOT NULL ,
	[fkey8] [tinyint] NOT NULL ,
	[fkey9] [tinyint] NOT NULL ,
	[fkey10] [tinyint] NOT NULL ,
	[fkey11] [tinyint] NOT NULL ,
	[fkey12] [tinyint] NOT NULL ,
	[fkey13] [tinyint] NOT NULL ,
	[fkey14] [tinyint] NOT NULL ,
	[fkey15] [tinyint] NOT NULL ,
	[fkey16] [tinyint] NOT NULL ,
	[rkey1] [tinyint] NOT NULL ,
	[rkey2] [tinyint] NOT NULL ,
	[rkey3] [tinyint] NOT NULL ,
	[rkey4] [tinyint] NOT NULL ,
	[rkey5] [tinyint] NOT NULL ,
	[rkey6] [tinyint] NOT NULL ,
	[rkey7] [tinyint] NOT NULL ,
	[rkey8] [tinyint] NOT NULL ,
	[rkey9] [tinyint] NOT NULL ,
	[rkey10] [tinyint] NOT NULL ,
	[rkey11] [tinyint] NOT NULL ,
	[rkey12] [tinyint] NOT NULL ,
	[rkey13] [tinyint] NOT NULL ,
	[rkey14] [tinyint] NOT NULL ,
	[rkey15] [tinyint] NOT NULL ,
	[rkey16] [tinyint] NOT NULL 
) 
GO

declare @dbname nvarchar(256)

declare dbcursor cursor for
select distinct(dbname) from SQL7Upgrade.dbo.objects

open dbcursor
--look at each database
fetch dbcursor into @dbname
while @@fetch_status = 0
	begin
		if exists (select name from SQL7Upgrade.dbo.sysobjects where name = 'vupg_fkview')
			drop view vupg_fkview

		--create the view
		exec ('create view vupg_fkview as select ''' + @dbname + ''' fkdbname,' + 
		      ' u.name fk_owner, o.name fk_table, u2.name pk_owner, o2.name pk_table,' +
		      'fkey1,fkey2,fkey3,fkey4,fkey5,fkey6,fkey7,fkey8,fkey9,fkey10,fkey11,fkey12,' +
		      'fkey13,fkey14,fkey15,fkey16,rkey1,rkey2,rkey3,rkey4,rkey5,rkey6,rkey7,' + 
		      'rkey8,rkey9,rkey10,rkey11,rkey12,rkey13,rkey14,rkey15,rkey16 from '+
		      @dbname + '..sysconstraints c, ' + @dbname + '..sysobjects o, ' + @dbname +
		      '..sysreferences r, ' + @dbname + '..sysobjects o2, ' + @dbname + 
		      '..sysusers u, ' + @dbname + '..sysusers u2 where r.constid = c.constid ' +
		      'and o.id = c.id and r.rkeyid = o2.id and u.uid = o.uid and u2.uid = o2.uid')

		-- populate the tmpFKeys table with 7.0 data
		insert SQL7Upgrade.dbo.tmpFKeys select * from vupg_fkview
		-- populate the tmpFKeys2 table with 6.5 data
		insert SQL7Upgrade.dbo.tmpFKeys2 select * from SQL7Upgrade.dbo.fkeys where dbname = @dbname
		-- delete matching rows from fkeys

		delete SQL7Upgrade.dbo.tmpFKeys2
		from SQL7Upgrade.dbo.tmpFKeys2 k1, tmpFKeys k2
		where k1.dbname = k2.dbname
		  and k1.fkowner = k2.fkowner
		  and k1.fktabname = k2.fktabname
		  and k1.pkowner = k2.pkowner
		  and k1.pktabname = k2.pktabname
		  and k1.fkey1 = k2.fkey1
		  and k1.fkey2 = k2.fkey2
		  and k1.fkey3 = k2.fkey3
		  and k1.fkey4 = k2.fkey4
		  and k1.fkey5 = k2.fkey5
		  and k1.fkey6 = k2.fkey6
		  and k1.fkey7 = k2.fkey7
		  and k1.fkey8 = k2.fkey8
		  and k1.fkey9 = k2.fkey9
		  and k1.fkey10 = k2.fkey10
		  and k1.fkey11 = k2.fkey11
		  and k1.fkey12 = k2.fkey12
		  and k1.fkey13 = k2.fkey13
		  and k1.fkey14 = k2.fkey14
		  and k1.fkey15 = k2.fkey15
		  and k1.fkey16 = k2.fkey16
		  and k1.rkey1 = k2.rkey1
		  and k1.rkey2 = k2.rkey2
		  and k1.rkey3 = k2.rkey3
		  and k1.rkey4 = k2.rkey4
		  and k1.rkey5 = k2.rkey5
		  and k1.rkey6 = k2.rkey6
		  and k1.rkey7 = k2.rkey7
		  and k1.rkey8 = k2.rkey8
		  and k1.rkey9 = k2.rkey9
		  and k1.rkey10 = k2.rkey10
		  and k1.rkey11 = k2.rkey11
		  and k1.rkey12 = k2.rkey12
		  and k1.rkey13 = k2.rkey13
		  and k1.rkey14 = k2.rkey14
		  and k1.rkey15 = k2.rkey15
		  and k1.rkey16 = k2.rkey16
		-- so, anything left in keys weren't created in 7.0
		--select * from SQL7Upgrade.dbo.fkeys where dbname = @dbname
		insert SQL7Upgrade.dbo.rptFKey6x select dbname,fkowner,fktabname,pkowner,pktabname 
		from SQL7Upgrade.dbo.tmpFKeys2 where dbname = @dbname

		-- delete matching rows from tmpFKeys
		delete SQL7Upgrade.dbo.tmpFKeys
		from fkeys k1, fkeys k2
		where k1.dbname = k2.dbname
		  and k1.fkowner = k2.fkowner
		  and k1.fktabname = k2.fktabname
		  and k1.pkowner = k2.pkowner
		  and k1.pktabname = k2.pktabname
		  and k1.fkey1 = k2.fkey1
		  and k1.fkey2 = k2.fkey2
		  and k1.fkey3 = k2.fkey3
		  and k1.fkey4 = k2.fkey4
		  and k1.fkey5 = k2.fkey5
		  and k1.fkey6 = k2.fkey6
		  and k1.fkey7 = k2.fkey7
		  and k1.fkey8 = k2.fkey8
		  and k1.fkey9 = k2.fkey9
		  and k1.fkey10 = k2.fkey10
		  and k1.fkey11 = k2.fkey11
		  and k1.fkey12 = k2.fkey12
		  and k1.fkey13 = k2.fkey13
		  and k1.fkey14 = k2.fkey14
		  and k1.fkey15 = k2.fkey15
		  and k1.fkey16 = k2.fkey16
		  and k1.rkey1 = k2.rkey1
		  and k1.rkey2 = k2.rkey2
		  and k1.rkey3 = k2.rkey3
		  and k1.rkey4 = k2.rkey4
		  and k1.rkey5 = k2.rkey5
		  and k1.rkey6 = k2.rkey6
		  and k1.rkey7 = k2.rkey7
		  and k1.rkey8 = k2.rkey8
		  and k1.rkey9 = k2.rkey9
		  and k1.rkey10 = k2.rkey10
		  and k1.rkey11 = k2.rkey11
		  and k1.rkey12 = k2.rkey12
		  and k1.rkey13 = k2.rkey13
		  and k1.rkey14 = k2.rkey14
		  and k1.rkey15 = k2.rkey15
		  and k1.rkey16 = k2.rkey16
		-- anything left in tmpFKeys was created new on 7.0
		insert SQL7Upgrade.dbo.rptFKey70 select dbname,fkowner,fktabname,pkowner,pktabname from SQL7Upgrade.dbo.tmpFKeys
		-- clean up
		delete SQL7Upgrade.dbo.tmpFKeys
		delete SQL7Upgrade.dbo.tmpFKeys2
		fetch next from dbcursor into @dbname
	END
CLOSE dbcursor
DEALLOCATE dbcursor

DROP TABLE SQL7Upgrade.dbo.tmpFKeys
DROP TABLE SQL7Upgrade.dbo.tmpFKeys2

GO

/******************************************************************************
 SCRIPT:	exists_on_dest_only.sql

 OWNER:		MattDo & RonMart

 PURPOSE:	This code retrieves the objects in each database that exist 
		on destination server but not the source server and are not 
		part of the EXCLUDE list

******************************************************************************/
/* BEGIN LOCALIZABLE STRINGS */
print 'Running exists on dest only...'

declare @dbname 	nvarchar(64)
declare @objectname 	nvarchar(64)
declare @ownername 	nvarchar(64)
declare @typename 	nvarchar(64)

set @dbname	 	= 'Database Name'
set @objectname 	= 'Object Name'
set @ownername 		= 'Owner Name'
set @typename 		= 'Type'
/* END LOCALIZABLE STRINGS */

if exists(select name from SQL7Upgrade..sysobjects where name='rpt70Only')
	drop table SQL7Upgrade.[dbo].[rpt70Only]

dump tran SQL7Upgrade with no_log

exec('CREATE TABLE SQL7Upgrade.[dbo].[rpt70Only] ([' 
	+ @dbname + '] [nvarchar] (30) NOT NULL ,['
	+ @typename + '] [nvarchar] (30) NOT NULL ,['
	+ @objectname + '] [nvarchar] (128) NOT NULL ,['
	+ @ownername  + '] [nvarchar] (30) NOT NULL)')

GO

DECLARE @dbname nvarchar(256), @message nvarchar(700)

declare dbname_cursor cursor
local forward_only
for
select distinct(dbname) from SQL7Upgrade.dbo.objects

OPEN dbname_cursor
  
FETCH NEXT FROM dbname_cursor 
INTO @dbname

WHILE @@fetch_status = 0
BEGIN
	select @message='use '+@dbname+' insert SQL7Upgrade.dbo.rpt70Only select '''+@dbname+''', so.type, so.name, su.name from sysobjects so, sysusers su '
	select @message=@message+'where su.uid=so.uid and (ObjectProperty(so.id,''IsMSShipped'')=0) ' 
	select @message=@message+'and so.name not in (select ex.objname from SQL7Upgrade.dbo.exclude ex where ex.dbname='''
	select @message=@message+@dbname+''' and ex.owner=su.name and ex.objname=so.name and ex.type=so.type) ' 
	select @message=@message+' and so.id not in (select o.newid from SQL7Upgrade.dbo.objects o) AND so.type in (''P'',''U'',''V'',''TR'')'
	execute(@message)

	FETCH NEXT FROM dbname_cursor INTO @dbname
END	

CLOSE dbname_cursor
DEALLOCATE dbname_cursor
GO

/******************************************************************************
 SCRIPT:	checks.sql

 OWNER:		MattDo

 PURPOSE:	Returns all of tables in each database converted that have 
		differences in row counts between the source 6.x server and 
		the destination 7.0 server.
		
******************************************************************************/
dump tran SQL7Upgrade with no_log

/* BEGIN LOCALIZABLE STRINGS */
print 'Running rowcount (and checksum if necessary)...'

DECLARE @dbnamehdr nchar(14), 
	@tabnamehdr nchar(10), 
	@c6xrcnt nvarchar(20), 
	@c70rcnt nvarchar(20), 
	@c6xchk nvarchar(20), 
	@c70chk nvarchar(20)

set @dbnamehdr	= 'Database Name'
set @tabnamehdr	= 'Table Name'
set @c6xrcnt	= '6.5 Row Count'
set @c70rcnt	= '8.0 Row Count'
set @c6xchk	= '6.5 Data Checksum'
set @c70chk	= '8.0 Data Checksum'
/* END LOCALIZABLE STRINGS */

if exists(select name from sysobjects where name='rptRowCount' and type='U')
	drop table SQL7Upgrade.[dbo].[rptRowCount]

execute( 'create table SQL7Upgrade.[dbo].[rptRowCount] (['+@dbnamehdr+'] [nvarchar] (30) NOT NULL, ['+@tabnamehdr+'] [nvarchar] (30) NOT NULL, ['+
	@c6xrcnt+'] int NOT NULL, ['+@c70rcnt+'] int NOT NULL)'	)

if exists(select name from sysobjects where name='rptCheckSum' and type='U')
	drop table SQL7Upgrade.[dbo].[rptCheckSum]

execute( 'create table SQL7Upgrade.[dbo].[rptCheckSum] (['+@dbnamehdr+'] [nvarchar] (30) NOT NULL, ['+@tabnamehdr+'] [nvarchar] (30) NOT NULL, ['+
	@c6xchk+'] int NOT NULL, ['+@c70chk+'] int NOT NULL)'	)

GO

if exists(select name from sysobjects where name='row_and_chksum' and type='P')
	drop procedure row_and_chksum
go

create procedure [dbo].[row_and_chksum] @chktype int=0
as
BEGIN
	declare @dbname nvarchar(256), @message nvarchar(500)

	declare dbname_cursor cursor
	local forward_only
	for
	select distinct(dbname) from SQL7Upgrade.dbo.objects o 

	OPEN dbname_cursor
  
	FETCH NEXT FROM dbname_cursor INTO @dbname

	WHILE @@fetch_status = 0
	BEGIN
		/* Dynamically build the query the returns the rowcount differences for tables in each database */
		insert SQL7Upgrade.dbo.rptRowCount select o.dbname, o.objname, c6x.rowcnt, c70.rowcnt 
		from SQL7Upgrade.dbo.objects o, SQL7Upgrade.dbo.check6x c6x, SQL7Upgrade.dbo.check70 c70 
		where o.dbname=@dbname and o.id=c6x.id and o.newid=c70.id 
		and o.dbname = c6x.dbname and o.dbname = c70.dbname 
		and c6x.rowcnt<>c70.rowcnt and o.objname not in (select ex.objname from SQL7Upgrade.dbo.exclude ex 
		where ex.dbname=o.dbname and ex.owner=o.owner and ex.objname=o.objname and ex.type=o.type) 
		order by o.dbname

		if (@chktype=1)
		begin
			/* Dynamically build the query the returns the checksum differences for tables in each database */
	   		insert SQL7Upgrade.dbo.rptCheckSum select o.dbname, o.objname, c6x.chksum, c70.chksum
			   from SQL7Upgrade.dbo.objects o, SQL7Upgrade.dbo.check6x c6x, SQL7Upgrade.dbo.check70 c70
			   where o.dbname=@dbname and o.id=c6x.id and o.newid=c70.id
			   and o.dbname = c6x.dbname and o.dbname = c70.dbname 
			   and c6x.chksum<>c70.chksum and o.objname not in (select ex.objname from SQL7Upgrade.dbo.exclude ex 
			   where ex.dbname=o.dbname and ex.owner=o.owner and ex.objname=o.objname and ex.type=o.type) 
			   order by o.dbname

		end
		FETCH NEXT FROM dbname_cursor INTO @dbname
	END	

	CLOSE dbname_cursor
	DEALLOCATE dbname_cursor
END
GO

-- TODO: Add logic to not do checksums
exec row_and_chksum 1
GO

drop proc row_and_chksum

GO

/******************************************************************************
 SCRIPT:	report1.sql

 OWNER:		RonMart

 PURPOSE:	Build the report tables

******************************************************************************/
if exists (select name from SQL7Upgrade.dbo.sysobjects where name = 'tmpSummary' and type = 'U')
	drop table SQL7Upgrade.dbo.tmpSummary
GO

CREATE TABLE SQL7Upgrade.[dbo].[tmpSummary] (
	[report] [sysname] NOT NULL ,
	[title] nvarchar(256),
	[rowcount] [int])

GO

/* BEGIN LOCALIZABLE STRINGS */
print 'Generating report tables...'

declare @title_rptIndexes6x	nvarchar(256)
declare @title_rptIndexes70	nvarchar(256)
declare @title_rptColumns6x	nvarchar(256)
declare @title_rptColumns70	nvarchar(256)
declare @title_rptFKey6x	nvarchar(256)
declare @title_rptFKey70	nvarchar(256)
declare @title_rpt6xOnly	nvarchar(256)
declare @title_rpt70Only	nvarchar(256)
declare @title_rptRowCount	nvarchar(256)
declare @title_rptCheckSum	nvarchar(256)

set @title_rptIndexes6x	= 'Indexes in 6.5, but not in 8.0'
set @title_rptIndexes70	= 'Indexes in 8.0, but not in 6.5'
set @title_rptColumns6x	= 'Columns in 6.5, but not in 8.0'
set @title_rptColumns70	= 'Columns in 8.0, but not in 6.5'
set @title_rptFKey6x	= 'Foreign Keys in 6.5, but not in 8.0'
set @title_rptFKey70	= 'Foreign Keys in 8.0, but not in 6.5'
set @title_rpt6xOnly	= 'Objects in 6.5, but not in 8.0'
set @title_rpt70Only	= 'Objects in 8.0, but not in 6.5'
set @title_rptRowCount	= 'Row Count Differences'
set @title_rptCheckSum	= 'Checksum Differences'
/* END LOCALIZABLE STRINGS */

dump tran SQL7Upgrade with no_log

-- Add the report names
insert SQL7Upgrade.[dbo].[tmpSummary] select name,NULL,NULL from SQL7Upgrade.dbo.sysobjects where type = 'U' and name like('rpt%')

-- Add the titles
exec ('update SQL7Upgrade.[dbo].[tmpSummary] set [title] = '''+@title_rptIndexes6x+''' where [report] = ''rptIndexes6x''')
exec ('update SQL7Upgrade.[dbo].[tmpSummary] set [title] = '''+@title_rptIndexes70+''' where [report] = ''rptIndexes70''')

exec ('update SQL7Upgrade.[dbo].[tmpSummary] set [title] = '''+@title_rptColumns6x+''' where [report] = ''rptColumns6x''')
exec ('update SQL7Upgrade.[dbo].[tmpSummary] set [title] = '''+@title_rptColumns70+''' where [report] = ''rptColumns70''')

exec ('update SQL7Upgrade.[dbo].[tmpSummary] set [title] = '''+@title_rptFKey6x+''' where [report] = ''rptFKey6x''')
exec ('update SQL7Upgrade.[dbo].[tmpSummary] set [title] = '''+@title_rptFKey70+''' where [report] = ''rptFKey70''')

exec ('update SQL7Upgrade.[dbo].[tmpSummary] set [title] = '''+@title_rpt6xOnly+''' where [report] = ''rpt6xOnly''')
exec ('update SQL7Upgrade.[dbo].[tmpSummary] set [title] = '''+@title_rpt70Only+''' where [report] = ''rpt70Only''')

exec ('update SQL7Upgrade.[dbo].[tmpSummary] set [title] = '''+@title_rptRowCount+''' where [report] = ''rptRowCount''')
exec ('update SQL7Upgrade.[dbo].[tmpSummary] set [title] = '''+@title_rptCheckSum+''' where [report] = ''rptCheckSum''')

GO

-- Create the reports
declare @tbname nvarchar(256)
declare tbcursor cursor for
select name from SQL7Upgrade.dbo.sysobjects where type = 'U' and name like('rpt%')
open tbcursor
fetch tbcursor into @tbname
while @@fetch_status = 0
	begin
		exec('update SQL7Upgrade.[dbo].[tmpSummary] set [rowcount] = (select count(*) from '+@tbname+') where [report] ='''+@tbname+'''')
		fetch tbcursor into @tbname
	end
close tbcursor
deallocate tbcursor

dump tran SQL7Upgrade with no_log

/* BEGIN LOCALIZABLE STRINGS */
print 'Script Complete'
/* END LOCALIZABLE STRINGS */

GO