Compare 2 database tables

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CompareDBTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_CompareDBTables]
GO

CREATE PROC sp_CompareDBTables
@targetDB sysname,
@Summary bit = 0,
@Detail bit = 1
--****************************************
--@targetDB: we'll compare the current database with this one
--@Summary: do we want summary output?
--@Detail: do we want detail output?

--calls sp_CompareTables for each table in the current table

--Note: this SP can "live" in the master DB
--*****************************************/
AS
IF DB_ID(@targetDB) IS NULL
BEGIN
RAISERROR('The target database cannot be found.', 10, 1) WITH NOWAIT
END

DECLARE @SQL varchar(4000)

SET @SQL = 'exec sp_CompareTables ''?'', ''' + @targetDB + '.?'', @Summary = ' + convert(varchar(1) ,@Summary) + ', @Detail = ' + convert(varchar(1) ,@Detail)

EXEC sp_msforeachtable @SQL

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

A WordPress.com Website.

Up ↑

%d bloggers like this: