As a SQL Server developer, it is not uncommon to inherit a large database with complex structures and schemas. In such scenarios, it can be challenging to identify any changes that have been made to the database, especially when it comes to newly added columns.

Recently, I received a question on social media from someone facing a similar situation. They had a database with pairs of tables – one archive table and one current table. Due to neglect, the organization had stopped archiving data for a while. To make matters worse, they had made changes to the schema of the current table without updating the corresponding archive table. The problem was to identify the newly added columns in the current table that did not exist in the archive table.

Fortunately, SQL Server provides a solution to this problem using the sys.columns catalog views. These views allow us to retrieve information about the columns in a table.

Let’s take a look at an example to understand how we can use the sys.columns catalog views to identify the newly added columns:

USE AdventureWorks2012;

CREATE TABLE ArchiveTable (
    ID INT,
    Col1 VARCHAR(10),
    Col2 VARCHAR(100),
    Col3 VARCHAR(100)
);

CREATE TABLE CurrentTable (
    ID INT,
    Col1 VARCHAR(10),
    Col2 VARCHAR(100),
    Col3 VARCHAR(100),
    ExtraCol INT
);

-- Columns in ArchiveTable but not in CurrentTable
SELECT name AS ColumnName
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'ArchiveTable'
EXCEPT
SELECT name AS ColumnName
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'CurrentTable';

-- Columns in CurrentTable but not in ArchiveTable
SELECT name AS ColumnName
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'CurrentTable'
EXCEPT
SELECT name AS ColumnName
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'ArchiveTable';

DROP TABLE ArchiveTable;
DROP TABLE CurrentTable;

In the above example, we create two tables – ArchiveTable and CurrentTable. The CurrentTable has an extra column compared to the ArchiveTable. By using the sys.columns catalog views and comparing the column names, we can identify the newly added column.

Running the above query will return the name of the extra column in the CurrentTable. This provides a simple and native T-SQL solution to the problem.

While this solution may not be the most elegant, it effectively solves the problem at hand. As a SQL Server developer, it is important to be familiar with the various catalog views and system functions available in SQL Server, as they can greatly simplify tasks like identifying changes in database structures.

Next time you encounter a similar situation, remember to leverage the power of sys.columns catalog views to identify newly added columns in SQL Server.