Comparing SQL Server table schemas with PowerShell

As a SQL Server DBA or SQL Server developer sometimes is necessary to know whether two tables have equal schemas or not. For example, a few months ago I had to consolidate two SQL Server instances in just one. One of the main problems were the collisions between Databases and Tables. I found out that both instances had Databases with equal name and the same thing happened with tables inside those databases. When consolidating databases is very important to make sure that users and apps will find the same schema they were used to find before consolidation, so in order to consolidate databases it will be necessary to find tables with different schemas, merge them and solve conflicts.

SQL Server provides a tool to compare schemas between databases, the tool comes with visual Studio or SQL Server Data tools with Database Projects. That could be very useful to identify schema differences among objects from both databases and see what tables have to be merged. So that’s it, if we have a tool that provides us that functionality, why do we need PowerShell? The answer to this question is quite surprising, it happens that the tool compares schemas based on the T-SQL Code, which means that the comparison is made by using Strings. Let’s see an example to introduce the issue.

If we create in SQL Server two databases with one table per database as it is showed in the following script:


The databases and tables look like follows:


Figure 1-Database comparison-

As it is showed in Figure 1, both Tables look exactly the same, they have:

  • – Equal Name
  • – Equal number of columns
  • – Equal name of columns
  • – Equal number of keys
  • – Equal columns in every key


The only different thing is the name of the key, because for the first table was created automatically and in the second one it was created manually specifying a name. Does it really matter? In this case the name of the key is not relevant as long as the columns are the same. Well, if we compare it with Visual Studio 2012 we get the following results:


Figure 2- Database Comparison-

The figure 2 shows the result. According to the result the tables are not equals because of a name. This is not the logic intended for us, because when we want to consolidate tables the only thing that matters is that the table will have a primary key clustered by Id column, the name does not make a difference. So this method is going to show us a lot of false positives when looking for tables with different schemas. The same thing happens with some third party tools, I’ve tried to compare objects with them and got the same result. For this example we are working with one database and only one table per database for a better understanding of the issue, but imagine when you are working with 100 database and an average of 70 or 80 tables per database, you need to make sure that you are no getting false positives identifying tables with different schemas. So here is when PowerShell comes up to save the day.

Using SMO we are able to create a function to compare schemas. The function looks like follows:

This is a personalized function and three main blocks are checked in order to determine whether two tables have the same schema or not:

1. Columns: In this section we check the number of columns, the name of the columns, the data types for every column, if is part of a primary key, if is part of a foreign key, if is a computed column and so on.

2. Checks: In this section all checks defined in a table are compared. As we explained before, the name doesn’t matter, the only thing that matters is the check definition text and the columns involved.

3. Indexes: In this sections all the indexes are compared. The index name doesn’t make a difference, we only check for index type, columns and so on.

In order to test this PowerShell function we will run a main program to test it:

The result that we get by running the main program is:


Figure 3-Execution result-

Now we are getting True as a result, which means that both tables are equals in terms of schema. If we change the schema of one of the test tables we will get a different result. Let’s say we change the primary for one of the tables:


Now the table schemas look like follows:


Figure 4- New Table schemas-

The schemas have changed as it is showed in Figure 4. The primary key of the table TestTabe in TableCompare2 Database has two keys instead of one. If we execute again our function the result is:


Figure 5- Result with different schemas-

Now we get False because the schemas are different, so our function is working as intended. As you can see, once again PowerShell shows us its power and allows us to solve a problem easily.

Note: Is very important to remark that this function only compares Columns, Checks and indexes. The main reason is because the function was created to solve a given problem but it could be extended to compare more object types like triggers, users and so on.

About Enrique Puig

Enrique Puig is an expert in relational databases and SQL Server platform. He has a strong Academic background in computer science. During the last five years Enrique has dedicated all his efforts to SQL Server relational databases, datawarehousing, performance tunings, high availability and all kind of projects reated with SQL Server: Writing white papers for Microsoft, delivering sessions at top level Microsoft events and working with leading companies around the world. Currently He works as a SQL Server Database Administrator and he also is a PowerShell enthusiast. In addtion he has a personal bog where he publish about SQL Server weekly (

One thought on “Comparing SQL Server table schemas with PowerShell

  1. John

    Thanks, right in time when I want to do exactly the same thing.

    I gave it a try but get

    Error: The schema dbo doesn’t contain any table called TestTable.

    when trying to run it.

    Drilling it down, I found that “$S1.databases[$bd1]” is able to return something while “$S1.databases[$bd1].Tables[$TableName1]” returns nothing.

    Both database TableCompare and database TableCompare2 are newly created as instructed above, with “Command(s) completed successfully”.

    What else I’m missing?


Comments are closed.