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
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]
)