Database Schema

Here are some helpful queries to compare two databases at schema level to identify the newly added, modified or deleted database objects. Below illustration uses ExistingDB and LatestDB as two databases. LatestDB refers to latest and greatest whereas ExistingDB refers to existing PROD database.

ADDED Tables: Query to identify newly added tables in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED TABLES'
SELECT schema_name(schema_id), name FROM [@latestDB].sys.tables
EXCEPT
SELECT schema_name(schema_id), name FROM [@existingDB].sys.tables
ORDER BY schema_name(schema_id)
PRINT N'END OF Identifying ADDED TABLES'

DELETED Tables: Query to identify deleted tables in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED TABLES'
SELECT schema_name(schema_id), name FROM [@existingDB].sys.tables
EXCEPT
SELECT schema_name(schema_id), name FROM [@latestDB].sys.tables
ORDER BY schema_name(schema_id)
PRINT N'END OF Identifying DELETED TABLES'

ADDED Columns: Query to identify newly added columns in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED COLUMNS'
SELECT tab.SchemaName, tab.tablename, col.name as columnname FROM [@latestDB].sys.columns col
INNER JOIN (SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@latestDB].sys.tables
          INTERSECT
          SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@existingDB].sys.tables) tab
ON col.object_id = tab.object_id
EXCEPT
SELECT tab.SchemaName, tab.tablename, col.name as columnname FROM [@existingDB].sys.columns col
INNER JOIN (SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@existingDB].sys.tables
          INTERSECT
          SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@latestDB].sys.tables) tab
ON col.object_id = tab.object_id
PRINT N'END OF Identifying ADDED COLUMNS'

DELETED Columns: Query to identify deleted columns in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED COLUMNS'
SELECT tab.SchemaName, tab.tablename, col.name as columnname FROM [@existingDB].sys.columns col
INNER JOIN (SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@existingDB].sys.tables
          INTERSECT
          SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@latestDB].sys.tables) tab
ON col.object_id = tab.object_id
EXCEPT
SELECT tab.SchemaName, tab.tablename, col.name as columnname FROM [@latestDB].sys.columns col
INNER JOIN (SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@latestDB].sys.tables
          INTERSECT
          SELECT object_id, schema_name(schema_id) SchemaName, name as tablename FROM [@existingDB].sys.tables) tab
ON col.object_id = tab.object_id
PRINT N'END OF Identifying DELETED COLUMNS'

ADDED Procedures: Query to identify newly added procedures in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED PROCEDURES'
SELECT schema_name(schema_id) as SchemaName, prc.name as ProcedureName FROM [@latestDB].sys.procedures prc
EXCEPT
SELECT schema_name(schema_id) as SchemaName, prc.name as ProcedureName FROM [@existingDB].sys.procedures prc
ORDER BY SchemaName, ProcedureName
PRINT N'END OF Identifying ADDED PROCEDURES'

DELETED Procedures: Query to identify deleted procedures in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED PROCEDURES'
SELECT schema_name(schema_id) as SchemaName, prc.name as ProcedureName FROM [@existingDB].sys.procedures prc
EXCEPT
SELECT schema_name(schema_id) as SchemaName, prc.name as ProcedureName FROM [@latestDB].sys.procedures prc
ORDER BY SchemaName, ProcedureName
PRINT N'END OF Identifying DELETED PROCEDURES'

MODIFIED Procedures: Query to identify modified procedures in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying MODIFIED PROCEDURES'
SELECT schema_name(prc.schema_id) as SchemaName, prc.name as ProcedureName, prc.modify_date FROM [@latestDB].sys.procedures prc
JOIN [@existingDB].sys.procedures prc2 on prc.name = prc2.name AND object_definition(prc.object_id) <> object_definition(prc2.object_id)

SELECT schema_name(prc.schema_id) as SchemaName, prc.name as ProcedureName, prc.modify_date FROM [@existingDB].sys.procedures prc
JOIN [@latestDB].sys.procedures prc2 on prc.name = prc2.name AND object_definition(prc.object_id) <> object_definition(prc2.object_id)
PRINT N'END OF Identifying MODIFIED PROCEDURES'

ADDED Views: Query to identify newly added views in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED VIEWS'
SELECT schema_name(schema_id) as SchemaName, vie.name as ViewName FROM [@latestDB].sys.views vie
EXCEPT
SELECT schema_name(schema_id) as SchemaName, vie.name as ViewName FROM [@existingDB].sys.views vie
ORDER BY SchemaName, ViewName
PRINT N'END OF Identifying ADDED VIEWS'

DELETED Views: Query to identify deleted views in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED VIEWS'
SELECT schema_name(schema_id) as SchemaName, vie.name as ViewName FROM [@existingDB].sys.views vie
EXCEPT
SELECT schema_name(schema_id) as SchemaName, vie.name as ViewName FROM [@latestDB].sys.views vie
ORDER BY SchemaName, ViewName
PRINT N'END OF Identifying DELETED VIEWS'

ADDED Indexes: Query to identify newly added indexes in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED INDEXES'
SELECT i.Name as IndexName, OBJECT_NAME(i.object_ID) as TableName, c.Name as ColumnName, i.type_desc FROM [@latestDB].sys.indexes i
INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN [@latestDB].sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
EXCEPT
SELECT i.Name as IndexName, OBJECT_NAME(i.object_ID) as TableName, c.Name as ColumnName, i.type_desc FROM [@existingDB].sys.indexes i
INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN [@existingDB].sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
ORDER BY TableName, ColumnName
PRINT N'END OF Identifying ADDED INDEXES'

DELETED Indexes: Query to identify deleted indexes in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED INDEXES'
SELECT i.Name as IndexName, OBJECT_NAME(i.object_ID) as TableName, c.Name as ColumnName, i.type_desc FROM [@existingDB].sys.indexes i
INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN [@existingDB].sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
EXCEPT
SELECT i.Name as IndexName, OBJECT_NAME(i.object_ID) as TableName, c.Name as ColumnName, i.type_desc FROM [@latestDB].sys.indexes i
INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN [@latestDB].sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
PRINT N'END OF Identifying DELETED INDEXES'

ADDED Triggers: Query to identify newly added triggers in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED TRIGGERS'
SELECT object_name(parent_id) as TableName, trg.name as TriggerName FROM [@latestDB].sys.triggers trg
EXCEPT
SELECT object_name(parent_id) as TableName, trg.name as TriggerName FROM [@existingDB].sys.triggers trg
ORDER BY TableName, TriggerName
PRINT N'END OF Identifying ADDED TRIGGERS'

DELETED Triggers: Query to identify deleted triggers in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED TRIGGERS'
SELECT object_name(parent_id) as TableName, trg.name as TriggerName FROM [@existingDB].sys.triggers trg
EXCEPT
SELECT object_name(parent_id) as TableName, trg.name as TriggerName FROM [@latestDB].sys.triggers trg
ORDER BY TableName, TriggerName
PRINT N'END OF Identifying DELETED TRIGGERS'

ADDED Functions: Query to identify newly added functions in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED FUNCTIONS'
SELECT schema_name(schema_id) as SchemaName, fun.name as FunctionName, fun.type as FunctionType FROM [@latestDB].sys.objects fun
WHERE (type = 'TF' or type = 'FN' or type = 'IF')
EXCEPT
SELECT schema_name(schema_id) as SchemaName, fun.name as FunctionName, fun.type as FunctionType FROM [@existingDB].sys.objects fun
WHERE (type = 'TF' or type = 'FN' or type = 'IF')
ORDER BY SchemaName, FunctionName, FunctionType
PRINT N'END OF Identifying ADDED FUNCTIONS'

DELETED Functions: Query to identify deleted functions in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED FUNCTIONS'
SELECT schema_name(schema_id) as SchemaName, fun.name as FunctionName, fun.type as FunctionType FROM [@existingDB].sys.objects fun
WHERE (type = 'TF' or type = 'FN' or type = 'IF')
EXCEPT
SELECT schema_name(schema_id) as SchemaName, fun.name as FunctionName, fun.type as FunctionType FROM [@latestDB].sys.objects fun
WHERE (type = 'TF' or type = 'FN' or type = 'IF')
ORDER BY SchemaName, FunctionName, FunctionType
PRINT N'END OF Identifying DELETED FUNCTIONS'

ADDED Schemas: Query to identify newly added schemas in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying ADDED SCHEMAS'
SELECT schema_name(schema_id) as SchemaName FROM [@latestDB].sys.schemas
EXCEPT
SELECT schema_name(schema_id) as SchemaName FROM [@existingDB].sys.schemas
ORDER BY SchemaName
PRINT N'END OF Identifying ADDED SCHEMAS'

DELETED Schemas: Query to identify deleted schemas in LatestDB

DECLARE @latestDB = 'latestDB' -- latestDB
DECLARE @existingDB = 'existingDB' -- existing DB

PRINT N'START OF Identifying DELETED SCHEMAS'
SELECT schema_name(schema_id) as SchemaName FROM [@existingDB].sys.schemas
EXCEPT
SELECT schema_name(schema_id) as SchemaName FROM [@latestDB].sys.schemas
ORDER BY SchemaName
PRINT N'END OF Identifying DELETED SCHEMAS'

With this I am concluding the illustration. Feel free to share your feedback.

Happy Programming !!!