3 Tips for Rolling Your Own SQL Compare Tool with Database Metadata

In most cases a database vendor’s default SQL Comparison tool will do the job, but every once in a while I need a little more (e.g. a tool embedded as part of a continuous deployment process).  Luckily I’ve found that it’s not difficult to build my own customized SQL comparison tool if I follow these steps:

Get The Table Metadata

In most SQL database systems you can find schema information using SQL itself. In SQLServer:

SELECT name AS tableName
FROM sys.sysobjects
WHERE (xtype = 'U')
ORDER BY tableName

In Oracle the command is:

SELECT owner, table_name
FROM all_tables
ORDER BY table_name

Get the Column Metadata

After you have the tables you want to know information about the structure of each table. In SQL Server:

SELECT sys.syscolumns.name AS column_name, sys.systypes.name AS datatype,
sys.syscolumns.length AS LENGTH
FROM sys.sysobjects INNER JOIN
sys.syscolumns ON sys.sysobjects.id = sys.syscolumns.id INNER JOIN
sys.systypes ON sys.syscolumns.xtype = sys.systypes.xtype
WHERE (sys.sysobjects.xtype = 'U') AND (sys.systypes.name 'sysname') AND
(sys.sysobjects.name = 'tableName')
ORDER BY sys.syscolumns.colid

In this case ‘tableName’ is the name of the table you are interested in.

Find the Diffs

Keep in mind your client app might need to do a little work to filter out differences between the order of columns, size of columns, or column names.  This can all be handled using standard data structures like a hash table that all modern languages provide out of the box.

Lastly, think carefully about the alert mechanism you build into your application. There are many valid reasons for two database schemas to be different, for instance as part of a larger system-wide refactoring.

Knowing how and when to use database metadata opens up a world of options for the informed hacker.