http://www.sqlservercentral.com/blogs/sql_awesomesauce/2010/09/13/tip_3A00_-use-system-views-to-compare-column-datatypes/

Printed 2014/12/19 03:48PM

Tip: Use System Views to Compare Column Datatypes

By Jen McCown, 2010/09/13

Let’s say that you have a staging table, that then loads to a destination table in the same database.¬† If you have more than one person working on the process, data types can quickly get out of whack.

So: what’s the easiest way to compare datatypes bewteen two “matching” tables?¬†System views, my friends! Let’s create two tables that are supposed to match:

CREATE TABLE temp1 (
col1 INT
, col2 VARCHAR(10)
, col3 SMALLMONEY
, col4 NVARCHAR(256))
GO
CREATE TABLE temp2 (
col1 BIGINT
, col2 VARCHAR(20)
, col3 SMALLMONEY
, col4 sysname)
GO

Now, we can see all the column information for table Temp1 in the syscolumns table:

SELECT C1.name
, C1.xtype
, C1.xusertype
, C1.[length]
, C1.xprec
, C1.xscale
FROM syscolumns C1
WHERE OBJECT_NAME(id) = 'temp1'

Let’s join syscolumns to itself, and find the columns in Temp1 that have the same name, but different datatype (or precision, length, etc), from those in Temp2:

DECLARE @table1 sysname = 'temp1'
, @table2 sysname = 'temp2'

SELECT C1.name
, C1.xtype
, C1.xusertype
, C1.[length]
, C1.xprec
, C1.xscale

, C2.xtype
, C2.xusertype
, C2.[length]
, C2.xprec
, C2.xscale
FROM syscolumns C1
INNER JOIN syscolumns C2 ON C1.name = c2.name
WHERE
(OBJECT_NAME(C1.id) = @table1
AND OBJECT_NAME(C2.id) = @table2)
AND
( C1.xtype <> C2.xtype
OR C1.xusertype <> C2.xusertype
OR C1.[length] <> C2.[length]
OR C1.xprec <> C2.xprec
OR C1.xscale <> C2.xscale )

Isn’t that just pretty?

Happy days,

Jen McCown

http://www.MidnightDBA.com/Jen


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.