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
