I recently had a situation where I needed to compare table columns between the production table and a test/development table.  Chances are if I needed to do it someone else will to.

I like using the EXCEPT SET OPERATOR.  Some people might prefer to use an OUTER JOIN to determine the column differences.  With the EXCEPT operator the sequence of the queries are important.  Think of it as “What’s in the first query that’s not in the second query?”.  Reversing the order of the queries will return a NULL result set.

For this posting I’ll use AdventureWorks.  Let’s first create a table that’s the same as the [HumanResources].[Department] table, except we’ll add the column CreatedDate.

CREATE TABLE [HumanResources].[Department_2](
    [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [GroupName] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Department_DepartmentID_2] PRIMARY KEY CLUSTERED
(
    [DepartmentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now we can check for the newly added column:

SELECT name FROM syscolumns WHERE OBJECT_NAME(id) = 'Department_2'
EXCEPT
SELECT name FROM syscolumns WHERE OBJECT_NAME(id) = 'Department'

Which produces our expected output:

Of course syscolumns also has the data type information in it so it’s not a huge leap to take this same concept to identify columns that do not have the same data type.

Happy SQL-ing!