Thursday 4 June 2015

Script to iterate through all selected columns in two databases



Script that does a distinct count and comparison of all columns and all tables across two databases
--list of fields to check, using a table in SQL listing all tables to be checked


Create --drop


Table #Columns


(


id Int Identity(1 , 1)


, ColumnName Varchar(500)


)


Insert #Columns


( ColumnName

)


Select Distinct


C.name

From


sys.columns C

Inner Join sys.tables t

On t.object_id = C.object_id


Inner Join sys.schemas s

On s.schema_id = t.schema_id


Inner Join Process.TableOutcomes TOut

On TOut.TableName = t.name

Where


s.name = 'Reporting'


And TOut.isActive = 1

And C.name <> 'refreshdate'


And C.name Not Like '%&%'


Declare @Maxcolumns Int , @CurrentColumn Int = 1 , @MaxTables int , @CurrentTable Int = 1 , @SQL varchar(max) , @ColumnName varchar(255)





Select


@Maxcolumns = Max(id)


From



#Columns

--create tables, as limited to 1200 columns per table, split into two


Create Table #AllTables3


(


AllTableID Int Identity(1 , 1)


, DBName Varchar(100)


, SchemaName Varchar(255)


, TableName Varchar(255)


, ColumnName Varchar(255)


, TotalRowCount BigInt


)


Create Table #TablesToCompare


(


ID BigInt Identity(1 , 1)


, SchemaName Varchar(255)


, TableName Varchar(255))









--Add first 1000 columns to first table


While @CurrentColumn <= @Maxcolumns

Begin


Insert #TablesToCompare


(SchemaName,TableName)


select distinct s.name

,t.name

from sys.tables t

left join sys.columns C on t.object_id=c.object_id


left join #Columns co on co.ColumnName=c.name

left join sys.schemas s on t.schema_id = s.schema_id


left join Process.TableOutcomes Tot on Tot.TableName=t.name

Where


Tot.isActive=1 and co.id=@CurrentColumn;


select @ColumnName = c.ColumnName

from #Columns C

where c.id = @CurrentColumn






select @MaxTables=MAX(ID) from #TablesToCompare






while @CurrentTable<=@MaxTables

begin


select @SQL = 'insert #AllTables3 (DBName,SchemaName,TableName,ColumnName,TotalRowCount)






select [DBName] = +DB_NAME()


,[SchemaName] = '''+T.SchemaName+'''

,[TableName] = '''+T.TableName+'''

,[ColumnName] = '''+@ColumnName+'''

,count(distinct ['+@ColumnName+']) from '+T.SchemaName+'.'+T.TableName

from #TablesToCompare T

where t.ID = @CurrentTable






use DATABASE1

exec (@SQL)

use DATABASE2

exec (@SQL)

set @CurrentTable=@CurrentTable+1

end





Set @CurrentColumn = @CurrentColumn + 1

set @CurrentTable=1

set @MaxTables=null


set @SQL = null


truncate table #TablesToCompare

End






select * from #AllTables3


where TableName<>'ms_InterestSplit'