MSSQL Utility Procedures for Database Integration

tableInformation.sql

USE [master]


-- The table information custom type
CREATE TYPE TableInfo as table
(
  TABLE_CATALOG nvarchar(128),
  TABLE_SCHEMA  nvarchar(128),
  TABLE_NAME    nvarchar(128),
  TABLE_TYPE    varchar(10)
)


-- getTableInformationFromDB dynamically queries all of TableInfos of a database

------ Examples ------

-- Example Usage:
-- Split tables in db '001' into sets of 100 and get the second set

-- exec getTableInformationFromDB '001', 2


-- Example Usage:
  -- Split tables in db '002' into sets of 200 and get the first set
create or alter proc getTableInformationFromDB(@dbName varchar(30),
                                               @pageNumber int,
                                               @pageSize int = 100)
as
begin

  declare @offsetBy int;
  set @offsetBy = @pageSize * (@pageNumber - 1);

  exec ('USE [' + @dbName + ']; SELECT @allTables = * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' ORDER BY TABLE_NAME
    OFFSET ' + @offsetBy + ' ROWS
    FETCH NEXT ' + @pageSize + ' ROWS ONLY')
end

go

---------


-- getTableDifference gets the table difference between two database
-- It is meant to be used to see what the differences are between '001' and '002'

------ Examples ------

-- exec getTableDifference @db1 = 'Infinity', @db2 = 'PowerPick'

create proc getTableDifference(@db1 varchar(20) = '001',
                               @db2 varchar(20) = '002') as
begin
  declare @tablesInFirstDatabase TableInfo;
  declare @tablesInSecondDatabase TableInfo;


  insert into @tablesInFirstDatabase exec getTableInformationFromDB @dbName = @db1, @pageNumber = 1, @pageSize = 1000;

  insert into @tablesInSecondDatabase exec getTableInformationFromDB @dbName = @db2, @pageNumber = 1, @pageSize = 1000;


  -- All tables that are not in both tables
  (select TABLE_NAME
   from @tablesInFirstDatabase
     except
   select TABLE_NAME
   from @tablesInSecondDatabase)

  union

  (select TABLE_NAME
   from @tablesInSecondDatabase
     except
   select TABLE_NAME
   from @tablesInFirstDatabase)

end

go