Recently I was working on a project that had many different input tables that needed to be filtered into one. I’ve worked on similar projects before and it’s usually not too bad to compare a few tables. But this time, with so many different tables, using my normal approach would have been too time-consuming. I created a more effective method for comparing a large number of different SQL Server tables.
This blog reviews a script used in MSSQL to compare all columns and datatypes per table in an easy-to-use format. Here’s an example of the output:
The first thing to point out is the @Orientation variable; it can be set to either H or V. That is to determine whether column names will be displayed as rows (H) or columns (V). After that, temp tables are needed to store table and column data to keep later queries simple and clean.
#Tables is used for storing all of the tables you wish to compare along with their column information. #Columns is used for storing the combined columns of all of the tables. Be mindful of the number of tables you’re comparing because there is a max column limit to MSSQL table (1024 columns).
Below, #table is filled by selecting the table and column data. Then pull in the length, precision, and scale to see if one table column could be different than another. The DataType column is used for the display of the datatype fill to change it how you like. #Column is simply a distinct select on the values for the columns, which can be switched based on the @orientation variable.
After the temp tables are full, create a physical table to store the values. Since the columns are dynamic based on the comparison, the table needs to be made through dynamic SQL. This could also be done by using another temp table.
Next, the table needs to be filled, but this requires building more Dynamic SQL and even a PIVOT select. If you are not familiar with PIVOT statements in MSSQL or how to create Dynamic ones, please refer to an earlier article: SQL Server PIVOT and UNPIVOT.
You now have an easy format to compare multiple tables and their columns. For the full script, please click here. I hope this helps you as much as it has helped me. I really enjoy writing scripts like this to help myself and anyone else who has similar struggles.