Control Structure & Data

[farzinmonsef]

Ctl

[farzinmonsef]

Controls

[farzinmonsef]

chkStucture_All_table_in_Two_DB.sql

GO /\ Object: StoredProcedure [dbo].[chkStucture_All_table_in_Two_DB] Script Date: 6/16/2016 12:51:10 **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

-- ============================================= -- Author: Farzin Monsef -- Create date: June 14 2016 -- Description: For Controling ALL Table Stucture -- on TWO DB -- Log the differences in Table _tbl_structure -- =============================================

-- exec chkTableStructure ALTER PROCEDURE [dbo].[chkStucture_All_table_in_Two_DB] AS BEGIN IF OBJECT_ID('dbo._tbl_structure', 'U') IS NOT NULL begin DROP Table dbo._tbl_structure end create table dbo._tbl_structure( column_name sysname,column_default nvarchar(4000), data_type nvarchar(128), character_maximum_length int, character_octet_length int, numeric_precision tinyint, numeric_scale int, is_nullable varchar(3), Description varchar(255) ); -- truncate table _tbl_structure

--insert into dbo._tbl_structure(column_name,column_default, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, is_nullable, Description)
--      exec   [dbo].[usp_check_two_table_stucture] 'Import_Control'

Declare @i int = 0;
DECLARE @table_name varchar(255) = '';
DECLARE AllTables CURSOR FOR  
    SELECT table_name FROM INFORMATION_SCHEMA.TABLES 

OPEN AllTables
FETCH NEXT FROM AllTables into @table_name;  
WHILE @@FETCH_STATUS = 0  
   BEGIN  
        --set @i = @i + 1
        print @table_name;
        insert into dbo._tbl_structure(column_name,column_default, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, is_nullable, Description)
            exec   [dbo].[chkStucture_two_table_in_Two_DB] @table_name   --DB1=CheckWatch with DB2=CheckWatch_SchemaOnly
        --if @i > 10 
        --      Break
        FETCH NEXT FROM AllTables into @table_name;  
   END;  
CLOSE AllTables;  
DEALLOCATE AllTables;  

END

[farzinmonsef]

chkStucture_two_table_in_One_DB.sql

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

-- ============================================= -- Author: Farzin Monsef -- Create date: June 14 2016 -- Description: For Controling Tale Stucture on TWO tables -- in ONE DATABASE -- return the differences -- ============================================= -- Exec usp_check___OneDB_two_table_stucture 'CheckOfficialSold', 'CheckOfficialSold_compare' alter PROCEDURE [dbo].[chkStucture_two_table_in_One_DB] @tblName1 nvarchar(255), @tblName2 nvarchar(255), @reportCommonFields bit = 0 AS BEGIN

    SELECT      column_name,
    '',--column_default, 
    data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, is_nullable, 'In '+ @tblName1
    FROM        INFORMATION_SCHEMA.columns WHERE table_name = @tblName1
    EXCEPT
    SELECT      column_name,
    '',--column_default, 
    data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, is_nullable, 'In '+ @tblName1
    FROM        INFORMATION_SCHEMA.columns WHERE table_name = @tblName2
    UNION ALL   -----------------------------------------------------------------------------------
    SELECT      column_name,
    '',--column_default, 
    data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, is_nullable, 'In '+ @tblName2
    FROM        INFORMATION_SCHEMA.columns WHERE table_name = @tblName2
    EXCEPT
    SELECT      column_name,
    '',--column_default, 
    data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, is_nullable, 'In '+ @tblName2
    FROM        INFORMATION_SCHEMA.columns WHERE table_name=@tblName1

END

[farzinmonsef]

chkStucture_two_table_in_Two_DB.sql

USE [...] GO /\ Object: StoredProcedure [dbo].[chkAllTableStructure_in_Two_DB] Script Date: 6/16/2016 12:46:13 **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

-- ============================================= -- Author: Farzin Monsef -- Create date: June 14 2016 -- Description: For Controling Tale Stucture -- on TWO tables -- on TWO DB -- Log the differences in Table _tbl_structure -- =============================================

-- exec chkTableStructure alter PROCEDURE [dbo].[chkStucture_two_table_in_Two_DB] @tblName1 nvarchar(255), --@tblName2 nvarchar(255), @reportCommonFields bit = 0 AS BEGIN SELECT column_name, '', --column_default, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, is_nullable, 'CheckWatch_SchemaOnly In '+ @tblName1 FROM CheckWatch_SchemaOnly.INFORMATION_SCHEMA.columns WHERE table_name = @tblName1 EXCEPT SELECT column_name, '', --column_default, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, is_nullable, 'CheckWatch_SchemaOnly In '+ @tblName1 FROM INFORMATION_SCHEMA.columns WHERE table_name = @tblName1 UNION ALL ----------------------------------------------------------------------------------- SELECT column_name, '', --column_default, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, is_nullable, 'In '+ @tblName1 FROM INFORMATION_SCHEMA.columns WHERE table_name = @tblName1 EXCEPT SELECT column_name, '', --column_default, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, is_nullable, 'In '+ @tblName1 FROM CheckWatch_SchemaOnly.INFORMATION_SCHEMA.columns WHERE table_name=@tblName1 END

[farzinmonsef]

chkData_All.sql

USE [.......] GO -- ============================================= -- Author: Farzin Monsef -- Create date: June 8 2016

-- Description: For Comparing Data Accordingly in

-- Log in Check_RESULT -- =============================================

/\ Object: StoredProcedure [dbo].[_utilRefreshExecuteRole] Script Date: 6/15/2016 5:11:17 **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Exec chkData_All '2016-06-06' alter PROCEDURE [dbo].[chkData_All] @selectDate date AS BEGIN Exec [dbo].[chkData_Check_compare] @selectDate

END

[farzinmonsef]

checkData_Check_compare.sql

USE [........] GO -- ============================================= -- Author: Farzin Monsef -- Create date: June 8 2016 -- Description: For -- Description: For Comparing Data Accordingly in

-- Log in Check_RESULT -- ============================================= /\ Object: StoredProcedure [dbo].[_utilRefreshExecuteRole] Script Date: 6/15/2016 5:11:17 **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

--Exec [dbo].[chkIn_Check_compare] '2016-06-03'

alter PROCEDURE [dbo].[chkData_Check_compare] @selectDate date AS BEGIN --DROP TABLE [dbo].[Check_Result]; --CREATE TABLE [dbo].[Check_Result]( -- [Fld1] varchar(50) null, -- --[Fld2] [bigint] NULL, -- --[Fld3] [bigint] NULL, -- [Fld4] [datetime] NULL, -- [Fld5] [bigint] NULL

--) ON [PRIMARY];

insert into Check_Result (Fld1 --,[Fld2] --,[Fld3] ,[Fld4] ,[Fld5] ) SELECT 'Check' --,[Fld2] --,[Fld3] ,[Fld4] ,[Fld5]

FROM [dbo].[Check] WHERE convert(date,Fld4)=@selectDate EXCEPT SELECT 'Check' --,[Fld2] --,[Fld3] ,[Fld4] ,[Fld5]

FROM [dbo].[Check_Result] WHERE convert(date,Fld4)= @selectDate

UNION ALL ----------------------------------------------------------------------------------- SELECT 'Check_compare' --,[Fld2] --,[Fld3] ,[Fld4] ,[Fld5]

  --   select count(*)

FROM [dbo].[Check_compare] WHERE convert(date,Fld4)=@selectDate EXCEPT SELECT 'Check_compare' --,[Fld2] --,[Fld3] ,[Fld4] ,[Fld5]

  --   select count(*)

FROM [dbo].[Check] WHERE convert(date,Fld4)=@selectDate;

--declare @selectDate date; --set @selectDate='2016-06-14'; declare @tempCntr int; set @tempCntr=(select count(_) FROM Check_compare WHERE convert(date,Fld4)=@selectDate); print 'from Checkcompare on ' + cast(@selectDate as varchar(20))+' = '+cast(@tempCntr as varchar(20)); set @tempCntr=(select count() FROM Check WHERE convert(date,Fld4)=@selectDate); print 'from Check on '+ cast(@selectDate as varchar(20))+' = '+cast(@tempCntr as varchar(20)); set @tempCntr=(select count(*) from Check_result); print 'from Check_result ' + cast(@selectDate as varchar(20))+' = '+cast(@tempCntr as varchar(20));

End