MSSQL Schema Compare by T SQL

Question

I have 21 DBs on same server which are 'supposed' to have the same schema (limiting schema only to table schema as of now) but they don't. In the environment I am, I can not install any tool/applications as per the compliance rules. The only way I can think of, is to pull up the schema of all the 21 DBs in excel and compare but its tedious.

Can someone guide me in developing a T-SQL which can compare the schema for all 21 DBs and highlight the differences?

These are the columns I need: Table_Name ,Column Name, Data type, Max Length, is_nullable, is_replicated, Primary Key

The following T-sql pulls up this in information for me. I need some idea about how can I compare this information for 21 DBs

SELECT    a.name Table_Name, c.name 'Column Name',
     t.Name 'Data type',
     c.max_length 'Max Length',
     c.is_nullable,a.is_replicated ,
     ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    Sys.tables a inner join      sys.columns c
On A.object_id=C.object_id
INNER JOIN 
     sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
     sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
     sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
     Order by 1,2

I need a starting point

If any of the 7 fields is different for a given table for the 21 DBs - That qualifies as a difference. I am happy if I can put the result for 21 DBs in a temp table and then query the temp table for differences. Something on these lines... — Alok Singh, Jul 29 '16 at 14:23
In Visual Studio there is a Tools>Sql Server>New Schema Comparision which will diff two sources. Might be worth looking into. — Phritzy, Jul 29 '16 at 14:24
@Phritzy - I could have tried that but for some reason my client company wont let me use Visual Studio either. So all I can do is through T sql. Worst case: I will pull all the data in Excel and then compare — Alok Singh, Jul 29 '16 at 14:26
This is not going to work out well. What you have is barely scratching the surface of an actual schema comparison. All you are looking at is tables. What about views, functions, procedures etc....? — Sean Lange, Jul 29 '16 at 14:28
@SeanLange I am only worried about the tables as of now. Sorry - I didn't mention that in my original question. — Alok Singh, Jul 29 '16 at 14:30
@Bridge You are right! I will need those too :) and Indexes too So is it even possible to do this using T SQL? or am I asking too much. — Alok Singh, Jul 29 '16 at 14:40
What you are running into now is the reason there are a few pieces of software that specialize in this. It is NOT simple and gets unbelievably complex very quickly trying to roll your own for this type of thing. Why not just install the free version of VS and save yourself weeks of anguish. There are several other canned applications out there that do this kind of thing far better than a homegrown version. — Sean Lange, Jul 29 '16 at 14:43
What @Sean said. Most third-party tools install on your computer (laptop), connect to the desired (remote) SQL Instances, and go from there. I use Redgate SQL Compare a lot, and even if you're comparing only two databases at a time, at version (high number) updated for SQL 2014, it's pretty darn comprehensive. — Philip Kelley, Jul 29 '16 at 14:45
Redgate SQL Compare is really solid but it is NOT cheap. This discussion has turned this into a question of "what third party software should I use" which is off topic for SO. — Sean Lange, Jul 29 '16 at 14:47
It's possible, I have to do all too often. It does take a bit of work though. — RBarryYoung, Jul 29 '16 at 15:06
I just can't install anything even on my local machine, I don't have administrative privileges. Even downloading is disabled here :D — Alok Singh, Jul 29 '16 at 15:28

score 1 · Accepted Answer · edited Aug 02 '16 at 09:39

I have to do stuff like this all the time under similar constraints, including without the benefit of the professional tools designed for it. Here's one way:

First create a temp table to hold the table information:

CREATE TABLE #DbSchemas(
    DbName nvarchar(128) NULL,
    Table_Name sysname NOT NULL,
    [Column Name] sysname NULL,
    [Data type] sysname NOT NULL,
    [Max Length] smallint NOT NULL,
    is_nullable bit NULL,
    is_replicated bit NULL,
    [Primary Key] bit NOT NULL
)
GO

Then wrap the query you already have in the undocumented system procedure sp_MSforeachdb to execute it on each database, adding the results into the #DbSchemas table:

EXECUTE master.sys.sp_MSforeachdb 'USE [?];
    INSERT INTO #DbSchemas
    SELECT DB_NAME() DbName,   a.name Table_Name, c.name [Column Name],
         t.Name [Data type],
         c.max_length [Max Length],
         c.is_nullable,a.is_replicated ,
         ISNULL(i.is_primary_key, 0) [Primary Key]
    FROM    Sys.tables a inner join      sys.columns c
    On A.object_id=C.object_id
    INNER JOIN 
         sys.types t ON c.user_type_id = t.user_type_id
    LEFT OUTER JOIN 
         sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    LEFT OUTER JOIN 
         sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
         Order by 1,2
    ;
';
go

Now you should be able to query the data from any database in the #DbSchemas table:

select * from #DbSchemas where Dbname = 'master'

Finally, to compare them you can use something like this:

SELECT  * FROM #DbSchemas s1
WHERE   DbName = 'msdb'
  AND NOT EXISTS(
    SELECT  * FROM #DbSchemas s2 
    WHERE   s2.DbName           = 'master'
      AND   s2.Table_Name       = s1.Table_Name
      AND   s2.[Column Name]    = s1.[Column Name]
      AND   s2.[Data type]      = s1.[Data type]
      AND   s2.[Max length]     = s1.[Max length]
      AND   s2.is_nullable      = s1.is_nullable
      AND   s2.is_replicated    = s1.is_replicated
      AND   s2.[Primary Key]    = s1.[Primary Key]
      )

This is exactly what I needed. You are AWESOME – Alok Singh Jul 29 '16 at 15:48 — Alok Singh, Jul 29 '16 at 15:48

MSSQL Schema Compare by T SQL

1 Answers1