Refresh processes

Usually there is some kind of process that the DBA is involved in to move data from prod and put it into test environments.  If there is data in tables that drives the environment, you may want to 'persist' that data.  In order to do that effectively, I think it is important and helpful to have a way to verify that table A and table B are identical.

Below is how I accomplish that currently:

ALTER PROCEDURE usp_CompareTables @table1 VARCHAR(100), @table2 VARCHAR(100)
AS
BEGIN
  -- return 1 if identical 0 if not
  DECLARE @sql NVARCHAR(MAX);
  DECLARE @count INT;
  DECLARE @parmDefinition NVARCHAR(200) = '@countOUT INT OUTPUT';
  set @sql = 'select @countOUT = count(*) from (
  (select * from ' + @table1 + '
  except
  select * from ' + @table2 + ' )
  union all
  (select * from ' + @table2 + '
  except
  select * from ' + @table1 +')) a'
 BEGIN TRY 
  -- try to see if the tables are identical
  exec sp_executesql @sql, @parmDefinition, @countOUT=@count OUTPUT
 END TRY
 BEGIN CATCH
  -- if we errored out then the datatypes don't match or the tables are not 
  -- alike.. Or maybe the DBA is comparing a table to a giraffe
  RETURN 0 
 END CATCH

 IF @count = 0
  -- if we have nothing in the list we are identical
  return 1
 ELSE
  -- if not we got a problem
  return 0
END 
GO

Past that you will need something to truncate and load.  If there are foreign keys, things get messy -- but I accomplish that through the following code:

IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Constraints]') AND type in (N'U'))
BEGIN
 DROP TABLE Constraints
END
  create table Constraints
  (
 ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 PKTABLE_QUALIFIER VARCHAR(50),
 PKTABLE_OWNER   VARCHAR(50),
 PKTABLE_NAME   VARCHAR(50),
 PKCOLUMN_NAME    VARCHAR(50),
 FKTABLE_QUALIFIER VARCHAR(50),
 FKTABLE_OWNER     VARCHAR(50),
 FKTABLE_NAME      VARCHAR(50),
 FKCOLUMN_NAME     VARCHAR(50),
 KEY_SEQ           INT,
 UPDATE_RULE       INT,
 DELETE_RULE       INT,
 FK_NAME           VARCHAR(256),
 PK_NAME           VARCHAR(256),
 DEFERRABILITY     INT
   )
GO
declare @database varchar(50)
declare @table varchar(50)
declare @sql   varchar(max)

-- Really could just use a while loop.. but cursors work too.
declare fk_cursor cursor for 
select  SourceDatabase, SourceTable from PersistedItems

OPEN fk_cursor
FETCH NEXT FROM fk_cursor into @database, @table



WHILE (@@FETCH_STATUS = 0)
BEGIN
 select @SQL = 'use ' + @database + '; INSERT INTO [Database].[dbo].[Constraints] exec sp_fkeys ''' + @table + ''';'
 exec (@sql)
 FETCH NEXT FROM fk_cursor into @database, @table
END

close fk_cursor
deallocate fk_cursor
GO

-- Drop Constraints
use Database
GO

declare @database varchar(50)
declare @fktablename varchar(50)
declare @fkname varchar(50)
declare @sql   varchar(max)

declare drop_cursor cursor for 
select  PKTABLE_QUALIFIER, FKTABLE_NAME, FK_NAME from Constraints

OPEN drop_cursor

FETCH NEXT FROM drop_cursor into @database, @fktablename, @fkname

WHILE (@@FETCH_STATUS = 0)
BEGIN
 select @SQL = 'use ' + @database + '; ALTER TABLE ' + QUOTENAME(@fktablename) + ' DROP CONSTRAINT ' + QUOTENAME(@fkname) + ';' from Constraints; 
 exec (@sql)
 FETCH NEXT FROM drop_cursor into @database, @fktablename, @fkname
END

close drop_cursor
deallocate drop_cursor
GO
use Database
GO

IF object_id('usp_RestoreTable') IS NULL
    EXEC ('create procedure dbo.usp_RestoreTable as select 1');
GO

ALTER PROCEDURE usp_RestoreTable
@SourceTableName NVARCHAR(100),
@PersistedTableName NVARCHAR(100),
@Database NVARCHAR (100),
@isIdentity BIT
AS
BEGIN

 DECLARE @SQL nvarchar(MAX) = ''
 DECLARE @Columns nvarchar(MAX) = ''
 CREATE TABLE #Columns
 (
  ColID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  ColName varchar(100)
 )


 SELECT @SQL = 'INSERT INTO #Columns SELECT COLUMN_NAME FROM [' + @Database + '].INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = ''' + @SourceTableName + ''' ORDER BY ORDINAL_POSITION'

 EXEC (@SQL)
 SELECT @SQL = ''

 SELECT @Columns += '[' + ColName + '], ' FROM #Columns
 SELECT @Columns = LEFT(@Columns, len(@Columns) - 1)

 DROP TABLE #Columns

 SELECT @SQL = 'TRUNCATE TABLE ' + @Database + '..' + @SourceTableName
 EXEC (@SQL)
 SELECT @SQL = 'USE ' + @Database + '; '
 IF (1 = @isIdentity) SELECT @SQL = @SQL + 'SET IDENTITY_INSERT ' + @SourceTableName + ' ON; ';
 SELECT @SQL = @SQL + 'INSERT INTO [dbo].[' + @SourceTableName + '] (' + @Columns + ') '
       + 'Select ' + @Columns + ' FROM [Database].[dbo].[' + @PersistedTableName + ']; '
 IF (1 = @isIdentity) SELECT @SQL = @SQL + 'SET IDENTITY_INSERT ' + @SourceTableName + ' OFF; ';

 exec (@SQL)

END
GO

--Move the data and verify
USE Database
GO
DECLARE @SourceTableName NVARCHAR(100),
@PersistedTableName NVARCHAR(100),
@Database NVARCHAR (100),
@isIdentity BIT,
@SourceTableFullName NVARCHAR(100),
@Matches BIT

DECLARE tandl_cursor CURSOR FOR
select SourceDatabase, SourceTable, PersistedTable, isIdentity from PersistedItems
OPEN tandl_cursor

FETCH NEXT FROM tandl_cursor into @Database, @SourceTableName, @PersistedTableName, @isIdentity

WHILE (@@FETCH_STATUS = 0)
BEGIN
 exec usp_RestoreTable @SourceTableName,@PersistedTableName,@Database,@isIdentity
 SELECT @SourceTableFullName = @Database + '..' + @SourceTableName
     exec @Matches = usp_CompareTables @table1 = @SourceTableFullName, @table2 = @PersistedTableName
 update PersistedItems 
 SET IsPostPersisted = @Matches 
 where SourceDatabase = @Database and SourceTable = @SourceTableName and PersistedTable = @PersistedTableName 
 FETCH NEXT FROM tandl_cursor into @Database, @SourceTableName, @PersistedTableName, @isIdentity
END

close tandl_cursor
deallocate tandl_cursor
GO

-- Create the FKeys
use Database
GO

declare @database varchar(50)
declare @fktablename varchar(50)
declare @fkname varchar(256)
declare @fkcolumnname varchar(50)
declare @pktablename varchar(50)
declare @pkcolumnname varchar (256)
declare @sql   varchar(max)

declare create_cursor cursor for 
select  PKTABLE_QUALIFIER, FKTABLE_NAME, FK_NAME, FKCOLUMN_NAME, PKTABLE_NAME, PKCOLUMN_NAME from Constraints

OPEN create_cursor

FETCH NEXT FROM create_cursor into @database, @fktablename, @fkname, @fkcolumnname, @pktablename, @pkcolumnname


WHILE (@@FETCH_STATUS = 0)
BEGIN
 select @SQL = 'USE ' + @database + '; '
    + 'ALTER TABLE ' + QUOTENAME(@fktablename) 
       + ' ADD CONSTRAINT ' + QUOTENAME(@fkname) 
    + ' FOREIGN KEY (' + QUOTENAME(@fkcolumnname) 
    + ') REFERENCES ' 
    + QUOTENAME(@pktablename) +
     '(' + QUOTENAME(@pkcolumnname) 
  + ');' FROM Constraints;
 
 exec (@sql)
 FETCH NEXT FROM create_cursor into @database, @fktablename, @fkname, @fkcolumnname, @pktablename, @pkcolumnname
END

close create_cursor
deallocate create_cursor
GO

This way I can simply add items to a driver table, and have the code handle the rest.