March 28, 2012 at 9:19 am
Comparing two tables (on the same database) to find matching column names or missing columns which also shows the following information ā data types, values null or empty columns.
I find this stored procedure (details below), my problem is that it not working. Any MSSQL genius out there who can help or another way to do the same thing?
------------------------------------------------------------------------------
GO
/****** Object: StoredProcedure [dbo].[spCOMPARETABLECOLUMNS] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
CREATE PROCEDURE [dbo].[spCOMPARETABLECOLUMNS]
-- Add the parameters for the stored procedure here
@INSTANCE1 NVARCHAR(35),
@DATABASE1 NVARCHAR(35),
@TABLE1 NVARCHAR(100),
@INSTANCE2 NVARCHAR(35),
@DATABASE2 NVARCHAR(35),
@TABLE2 NVARCHAR(100)
AS
BEGIN
DECLARE @COMMAND NVARCHAR(4000),
@COLUMNS_TABLE1 NVARCHAR(200),
@COLUMNS_TABLE2 NVARCHAR(200),
@NAME NVARCHAR(50),
@IS_NULLABLE VARCHAR(3),
@DATA_TYPE NVARCHAR(128),
@CHARACTER_MAXIMUM_LENGTH INT,
@NUMERIC_PRECISION INT ,
@NUMERIC_PRECISION_RADIX INT,
@NUMERIC_SCALE TINYINT,
@DATETIME_PRECISION SMALLINT,
@TABLE NVARCHAR(50),
@COUNT INT
SET @COUNT = 1
SET @INSTANCE1 =
CASE
WHEN (@INSTANCE1 IS NOT NULL) THEN '[' + RTRIM(@INSTANCE1) + '].'
WHEN (@INSTANCE1 IS NULL) THEN ''
END
SET @DATABASE1 =
CASE
WHEN (@DATABASE1 IS NOT NULL) THEN '[' + RTRIM(@DATABASE1) + '].'
WHEN (@DATABASE1 IS NULL) THEN ''
END
SET @INSTANCE2 =
CASE
WHEN (@INSTANCE2 IS NOT NULL) THEN '[' + RTRIM(@INSTANCE2) + '].'
WHEN (@INSTANCE2 IS NULL) THEN ''
END
SET @DATABASE2 =
CASE
WHEN (@DATABASE2 IS NOT NULL) THEN '[' + RTRIM(@DATABASE2) + '].'
WHEN (@DATABASE2 IS NULL) THEN ''
END
SET @COLUMNS_TABLE1 = RTRIM(@INSTANCE1) + RTRIM(@DATABASE1) + '[INFORMATION_SCHEMA].[COLUMNS] '
SET @COLUMNS_TABLE2 = RTRIM(@INSTANCE2) + RTRIM(@DATABASE2) + '[INFORMATION_SCHEMA].[COLUMNS] '
PRINT 'WHAT IS DIFFERENT BETWEEN ' + @TABLE1 + ' AND ' + @TABLE2
PRINT ''
PRINT ''
SELECT @COMMAND =
'DECLARE c CURSOR FOR
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION,
IS_NULLABLE
FROM '
+ @COLUMNS_TABLE1 + '
WHERE
TABLE_NAME = ' + CHAR(39) + @TABLE1 + CHAR(39) + '
EXCEPT
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION,
IS_NULLABLE
FROM '
+ @COLUMNS_TABLE2 + '
WHERE
TABLE_NAME = ' + CHAR(39) + @TABLE2 + CHAR(39)
BEGIN TRY
EXEC(@COMMAND);
END TRY
BEGIN CATCH
PRINT 'ERROR_MESSAGE = ' + ERROR_MESSAGE() + ' ERROR LINE = ' + STR(ERROR_LINE())
PRINT ''
END CATCH
OPEN c
FETCH NEXT FROM
c
INTO
@NAME,
@DATA_TYPE,
@CHARACTER_MAXIMUM_LENGTH,
@NUMERIC_PRECISION,
@NUMERIC_PRECISION_RADIX,
@NUMERIC_SCALE,
@DATETIME_PRECISION,
@IS_NULLABLE
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
PRINT 'FETCH ' + STR(@COUNT)
SET @COUNT = @COUNT + 1
PRINT 'COLUMN: ' +
@NAME + ', ' +
@DATA_TYPE + ', ' +
CASE
WHEN @CHARACTER_MAXIMUM_LENGTH IS NULL THEN '0'
ELSE CAST(@CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(10))
END
+ ', ' +
CASE
WHEN @NUMERIC_PRECISION IS NULL THEN '0'
ELSE CAST(@NUMERIC_PRECISION AS NVARCHAR(10))
END
+ ', ' +
CASE
WHEN @NUMERIC_PRECISION_RADIX IS NULL THEN '0'
ELSE CAST(@NUMERIC_PRECISION_RADIX AS NVARCHAR(10))
END
+ ', ' +
CASE
WHEN @NUMERIC_SCALE IS NULL THEN '0'
ELSE CAST(@NUMERIC_SCALE AS NVARCHAR(10))
END
+ ', ' +
CASE
WHEN @DATETIME_PRECISION IS NULL THEN '0'
ELSE CAST(@DATETIME_PRECISION AS NVARCHAR(10))
END
+ ', ' +
@IS_NULLABLE
PRINT ''
END TRY
BEGIN CATCH
PRINT 'ERROR: ' + ERROR_MESSAGE()
PRINT ''
END CATCH
FETCH NEXT FROM
c
INTO
@NAME,
@DATA_TYPE,
@CHARACTER_MAXIMUM_LENGTH,
@NUMERIC_PRECISION,
@NUMERIC_PRECISION_RADIX,
@NUMERIC_SCALE,
@DATETIME_PRECISION,
@IS_NULLABLE
END
CLOSE c
DEALLOCATE c
PRINT 'WHAT IS DIFFERENT BETWEEN ' + @TABLE2 + ' AND ' + @TABLE1
PRINT ''
PRINT ''
SELECT @COMMAND =
'DECLARE c CURSOR FOR
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION,
IS_NULLABLE
FROM '
+ @COLUMNS_TABLE2 + '
WHERE
TABLE_NAME = ' + CHAR(39) + @TABLE2 + CHAR(39) + '
EXCEPT
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION,
IS_NULLABLE
FROM '
+ @COLUMNS_TABLE1 + '
WHERE
TABLE_NAME = ' + CHAR(39) + @TABLE1 + CHAR(39)
BEGIN TRY
EXEC(@COMMAND);
END TRY
BEGIN CATCH
PRINT 'ERROR_MESSAGE = ' + ERROR_MESSAGE() + ' ERROR LINE = ' + STR(ERROR_LINE())
PRINT ''
END CATCH
OPEN c
FETCH NEXT FROM
c
INTO
@NAME,
@DATA_TYPE,
@CHARACTER_MAXIMUM_LENGTH,
@NUMERIC_PRECISION,
@NUMERIC_PRECISION_RADIX,
@NUMERIC_SCALE,
@DATETIME_PRECISION,
@IS_NULLABLE
SET @COUNT = 1
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
PRINT 'FETCH ' + STR(@COUNT)
SET @COUNT = @COUNT + 1
PRINT 'COLUMN: ' +
@NAME + ', ' +
@DATA_TYPE + ', ' +
CASE
WHEN @CHARACTER_MAXIMUM_LENGTH IS NULL THEN '0'
ELSE CAST(@CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(10))
END
+ ', ' +
CASE
WHEN @NUMERIC_PRECISION IS NULL THEN '0'
ELSE CAST(@NUMERIC_PRECISION AS NVARCHAR(10))
END
+ ', ' +
CASE
WHEN @NUMERIC_PRECISION_RADIX IS NULL THEN '0'
ELSE CAST(@NUMERIC_PRECISION_RADIX AS NVARCHAR(10))
END
+ ', ' +
CASE
WHEN @NUMERIC_SCALE IS NULL THEN '0'
ELSE CAST(@NUMERIC_SCALE AS NVARCHAR(10))
END
+ ', ' +
CASE
WHEN @DATETIME_PRECISION IS NULL THEN '0'
ELSE CAST(@DATETIME_PRECISION AS NVARCHAR(10))
END
+ ', ' +
@IS_NULLABLE
PRINT ''
END TRY
BEGIN CATCH
PRINT 'ERROR: ' + ERROR_MESSAGE()
END CATCH
FETCH NEXT FROM
c
INTO
@NAME,
@DATA_TYPE,
@CHARACTER_MAXIMUM_LENGTH,
@NUMERIC_PRECISION,
@NUMERIC_PRECISION_RADIX,
@NUMERIC_SCALE,
@DATETIME_PRECISION,
@IS_NULLABLE
END
CLOSE c
DEALLOCATE c
---------------------------------------------------------------------
http://www.sqlserverclub.com/articles/how-to-compare-the-columns-of-two-sql-server-tables.aspx
March 28, 2012 at 11:48 am
RedGate's SQL Compare does the wonders š
The tool well worth every penny/shilling.
~Leon
March 28, 2012 at 5:03 pm
Leon Orlov-255445 (3/28/2012)
RedGate's SQL Compare does the wonders šThe tool well worth every penny/shilling.
~Leon
at my shop we had to track DB changes from one version of a product we use to the new version (still dont know what the reasoning was for changing things) and used redgate's sql compare. worked wonderfully.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 28, 2012 at 5:15 pm
I prefer SQL Compare as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 28, 2012 at 5:16 pm
azdeji (3/28/2012)
my problem is that it not working.
How is it not working?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 29, 2012 at 12:19 am
azdeji (3/28/2012)
----------------------------------------------
my problem is that it not working.
although you have both tables in same server / database, you have to pass values for all procedure parameters (@INSTANCE1, @DATABASE1 etc). don't pass NULL for any, then it will work
or you can make small change in code to work with NULL values if tables are in same database
March 29, 2012 at 4:01 am
This was removed by the editor as SPAM
March 29, 2012 at 5:15 am
Thanks all for your help!
A friend send me this SQL scripts but Iām not sure which website he got from, anyway it does the job see below -
SELECT * FROM (
SELECT * FROM DB1.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableA'
) a FULL OUTER JOIN (
SELECT * FROM DB2.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableA'
) b ON a.COLUMN_NAME=b.COLUMN_NAME
WHERE a.COLUMN_NAME IS NULL OR b.COLUMN_NAME IS NULL
OR a.DATA_TYPE<>b.DATA_TYPE OR a.IS_NULLABLE<>b.IS_NULLABLE
OR a.CHARACTER_MAXIMUM_LENGTH<>b.CHARACTER_MAXIMUM_LENGTH
OR a.NUMERIC_PRECISION<>b.NUMERIC_PRECISION OR a.NUMERIC_SCALE<>b.NUMERIC_SCALE
OR a.COLLATION_NAME<>b.COLLATION_NAME -- and maybe some other columns
Hope it useful
March 29, 2012 at 8:28 am
Anyone know how to reoder the columns or remove some from the result set without affecting the column matching, I'm getting 46 columns back from the result.
Thanks
March 29, 2012 at 5:39 pm
I used this code to find the columns that are present in the OtherDB, but missing in the current DB.
You could use this to add more checks in the inner select - say datatype and column length stuff (just borrow them from your friend's script) and maybe order by table name and by column name to get a nicer list. In my case it was only a handful of columns so I didn't really care about that.
SELECT * FROM OtherDB.INFORMATION_SCHEMA.COLUMNS c1
WHERE NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS c2 WHERE c1.TABLE_NAME=c2.TABLE_NAME AND c2.COLUMN_NAME=c1.COLUMN_NAME)
March 30, 2012 at 1:00 am
March 30, 2012 at 1:24 am
@anthony.green
Wow, nice tool indeed š
March 30, 2012 at 2:20 pm
Nice tool
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 1, 2015 at 12:15 am
--Get matched column names between two tables in the same database
CREATE PROCEDURE GetMatchedColumns_TwoTable
@Table1 Varchar (50), @Table2 Varchar(50)
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM
(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@Table1) t1
INNER JOIN
(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@Table2) t2
ON t1.TABLE_NAME !=t2.TABLE_NAME
AND t1.COLUMN_NAME=t2.COLUMN_NAME
AND t1.DATA_TYPE=t2.DATA_TYPE
END
--Get matched columns between one table and all other tables in the same database
CREATE PROCEDURE GetMatchedColumns_AllTable
@Table1 Varchar(50)
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM
(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@Table1) t1
CROSS APPLY
(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS t2
WHERE t1.TABLE_NAME !=t2.TABLE_NAME
AND t1.COLUMN_NAME=t2.COLUMN_NAME
AND t1.DATA_TYPE=t2.DATA_TYPE) t3
END
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply