The query will help to get the column level difference for 2 database(Like Prod and Dev database). This help us to verify what are the column changes has been done in dev environment after Prod deployment.
Please change the database name as per your requirement.
In below example I used the MiteshProd as Prouction db and Miteshdev as dev enviroment
--Change the Database name ; For below query I used the MiteshProd as Prouction db and Miteshdev as dev enviroment
SELECT CL.TABLE_SCHEMA+'.'+CL.TABLE_NAME+'.'+CL.COLUMN_NAME as ColumnName,CL.TABLE_SCHEMA+'.'+CL.TABLE_NAME+'.'+CL.COLUMN_NAME+' HAS BeLOW Changes'+CHAR(13)+CHAR(10)+
IIF( ISNULL(CL.COLUMN_DEFAULT,'')= ISNULL(CL1.COLUMN_DEFAULT,''),'','|COLUMN_DEFAULT IS CHANGE')+
IIF( ISNULL(CL.IS_NULLABLE,'')=ISNULL(CL1.IS_NULLABLE,''),'','|IS_NULLABLE IS CHANGE')+
IIF( ISNULL(CL.DATA_TYPE,'')=ISNULL(CL1.DATA_TYPE,''),'','|DATA_TYPE IS CHANGE')+
IIF( ISNULL(CL.CHARACTER_MAXIMUM_LENGTH,'')=ISNULL(CL1.CHARACTER_MAXIMUM_LENGTH,''),'','|CHARACTER_MAXIMUM_LENGTH IS CHANGE')+
IIF( ISNULL(CL.CHARACTER_OCTET_LENGTH,'')=ISNULL(CL1.CHARACTER_OCTET_LENGTH,''),'','|CHARACTER_OCTET_LENGTH IS CHANGE')+
IIF( ISNULL(CL.NUMERIC_PRECISION,'')=ISNULL(CL1.NUMERIC_PRECISION,''),'','|NUMERIC_PRECISION IS CHANGE')+
IIF( ISNULL(CL.NUMERIC_PRECISION_RADIX,'')=ISNULL(CL1.NUMERIC_PRECISION_RADIX,''),'','|NUMERIC_PRECISION_RADIX IS CHANGE')+
IIF( ISNULL(CL.NUMERIC_SCALE,'')=ISNULL(CL1.NUMERIC_SCALE,''),'','|NUMERIC_SCALE IS CHANGE')+
IIF( ISNULL(CL.DATETIME_PRECISION,'')=ISNULL(CL1.DATETIME_PRECISION,''),'','|DATETIME_PRECISION IS CHANGE')+
IIF( ISNULL(CL.CHARACTER_SET_CATALOG,'')=ISNULL(CL1.CHARACTER_SET_CATALOG,''),'','|CHARACTER_SET_CATALOG IS CHANGE')+
IIF( ISNULL(CL.CHARACTER_SET_SCHEMA,'')=ISNULL(CL1.CHARACTER_SET_SCHEMA,''),'','|CHARACTER_SET_SCHEMA IS CHANGE')+
IIF( ISNULL(CL.CHARACTER_SET_NAME,'')=ISNULL(CL1.CHARACTER_SET_NAME,''),'','|CHARACTER_SET_NAME IS CHANGE')+
IIF( ISNULL(CL.COLLATION_CATALOG,'')=ISNULL(CL1.COLLATION_CATALOG,''),'','|COLLATION_CATALOG IS CHANGE')+
IIF( ISNULL(CL.COLLATION_SCHEMA,'')=ISNULL(CL1.COLLATION_SCHEMA,''),'','|COLLATION_SCHEMA IS CHANGE')+
IIF( ISNULL(CL.COLLATION_NAME,'')=ISNULL(CL1.COLLATION_NAME,''),'','|COLLATION_NAME IS CHANGE')+
IIF( ISNULL(CL.DOMAIN_SCHEMA,'')=ISNULL(CL1.DOMAIN_SCHEMA,''),'','|DOMAIN_SCHEMA IS CHANGE')+
IIF( ISNULL(CL.DOMAIN_NAME,'')=ISNULL(CL1.DOMAIN_NAME,''),'','|DOMAIN_NAME IS CHANGE')ChangeDetail
from MiteshProd.INFORMATION_SCHEMA.COLUMNS CL
INNER JOIN Miteshdev.INFORMATION_SCHEMA.COLUMNS CL1
ON CL.TABLE_SCHEMA = CL1.TABLE_SCHEMA
AND CL.TABLE_NAME = CL1.TABLE_NAME
AND CL.COLUMN_NAME = CL1.COLUMN_NAME
WHERE NOT
(
ISNULL(CL.COLUMN_DEFAULT,'')=ISNULL(CL1.COLUMN_DEFAULT,'')
AND ISNULL(CL.IS_NULLABLE,'')=ISNULL(CL1.IS_NULLABLE,'')
AND ISNULL(CL.DATA_TYPE,'')=ISNULL(CL1.DATA_TYPE,'')
AND ISNULL(CL.CHARACTER_MAXIMUM_LENGTH,'')=ISNULL(CL1.CHARACTER_MAXIMUM_LENGTH,'')
AND ISNULL(CL.CHARACTER_OCTET_LENGTH,'')=ISNULL(CL1.CHARACTER_OCTET_LENGTH,'')
AND ISNULL(CL.NUMERIC_PRECISION,'')=ISNULL(CL1.NUMERIC_PRECISION,'')
AND ISNULL(CL.NUMERIC_PRECISION_RADIX,'')=ISNULL(CL1.NUMERIC_PRECISION_RADIX,'')
AND ISNULL(CL.NUMERIC_SCALE,'')=ISNULL(CL1.NUMERIC_SCALE,'')
AND ISNULL(CL.DATETIME_PRECISION,'')=ISNULL(CL1.DATETIME_PRECISION,'')
AND ISNULL(CL.CHARACTER_SET_CATALOG,'')=ISNULL(CL1.CHARACTER_SET_CATALOG,'')
AND ISNULL(CL.CHARACTER_SET_SCHEMA,'')=ISNULL(CL1.CHARACTER_SET_SCHEMA,'')
AND ISNULL(CL.CHARACTER_SET_NAME,'')=ISNULL(CL1.CHARACTER_SET_NAME,'')
AND ISNULL(CL.COLLATION_CATALOG,'')=ISNULL(CL1.COLLATION_CATALOG,'')
AND ISNULL(CL.COLLATION_SCHEMA,'')=ISNULL(CL1.COLLATION_SCHEMA,'')
AND ISNULL(CL.COLLATION_NAME,'')=ISNULL(CL1.COLLATION_NAME,'')
AND ISNULL(CL.DOMAIN_SCHEMA,'')=ISNULL(CL1.DOMAIN_SCHEMA,'')
AND ISNULL(CL.DOMAIN_NAME,'')=ISNULL(CL1.DOMAIN_NAME,'')
)
UNION
Select ISNULL(CL.TABLE_SCHEMA+'.'+CL.TABLE_NAME+'.'+CL.COLUMN_NAME,CL1.TABLE_SCHEMA+'.'+CL1.TABLE_NAME+'.'+CL1.COLUMN_NAME)ColumnName,
CASE WHEN CL1 .COLUMN_NAME IS NULL THEN CL.TABLE_SCHEMA+'.'+CL.TABLE_NAME+'.'+CL.COLUMN_NAME+' IS DELETED'
ELSE CL1.TABLE_SCHEMA+'.'+CL1.TABLE_NAME+'.'+CL1.COLUMN_NAME+' IS ADDED' END ChangeDetail
from MiteshProd.INFORMATION_SCHEMA.COLUMNS CL
FULL OUTER JOIN Miteshdev.INFORMATION_SCHEMA.COLUMNS CL1
ON CL.TABLE_SCHEMA = CL1.TABLE_SCHEMA
AND CL.TABLE_NAME = CL1.TABLE_NAME
AND CL.COLUMN_NAME = CL1.COLUMN_NAME
WHERE CL.COLUMN_NAME IS NULL OR CL1.COLUMN_NAME IS NULL