Ms SQL new and old database data dictionary comparison script

/* Ms SQL comparison script for new and old database data dictionaries */

-- Note 1: the New and Old databases must be in the same database server and instance. It is best to log in as SA.
-- Note 2: This script can be used for system upgrade and transformation and data migration after relevant information is obtained.
Declare @ I int

Set @ I = 4/* indicate as the data dictionary information added to the new database;
2. To obtain more data dictionary information from the old database;
3. The data dictionary information of the table to be added to the new database;
4. To obtain the data dictionary information of the tables in the old database */

Use temp -- open the old database
Select sysobjects. Name as [Table], case when cast (sysproperties. [value] As varchar)
Is null then'' else cast (sysproperties. [value] As varchar) end as table description,
Syscolumns. Name as field, case when cast (properties. [value] As varchar) is null
Then ''else cast (properties. [value] As varchar) end as field description,
Policypes. Name as type, syscolumns. length,
Isnull (columnproperty (syscolumns. ID, syscolumns. Name, 'Scale'), 0)
As decimal places, syscolumns. isnullable as isnull,
Case when syscomments. Text is null
Then ''else syscomments. Text end as [Default],
Case when columnproperty (syscolumns. ID, syscolumns. Name, 'isidentity ')
= 1 then '√ 'else' end as ID, case when exists
(Select 1
From sysobjects
Where xtype = 'pk' and name in
(Select name
From sysindexes
Where indid in
(Select indid
From sysindexkeys
Where id = syscolumns. ID and colid = syscolumns. colid )))
Then' √ 'else' 'end as primary key into # Old
From syscolumns inner join
Sysobjects on sysobjects. ID = syscolumns. ID inner join
Policypes on syscolumns. xtype = policypes. xtype left Outer Join
Sysproperties properties on syscolumns. ID = properties. ID and
Syscolumns. colid = properties. smallid left Outer Join
Sysproperties on sysobjects. ID = sysproperties. ID and
Sysproperties. smallid = 0 left Outer Join
Syscomments on syscolumns. cdefault = syscomments. ID
Where (sysobjects. xtype = 'U ')

Use accdb -- open a new database
Select sysobjects. Name as [Table], case when cast (sysproperties. [value] As varchar)
Is null then'' else cast (sysproperties. [value] As varchar) end as table description,
Syscolumns. Name as field, case when cast (properties. [value] As varchar) is null
Then ''else cast (properties. [value] As varchar) end as field description,
Policypes. Name as type, syscolumns. length,
Isnull (columnproperty (syscolumns. ID, syscolumns. Name, 'Scale'), 0)
As decimal places, syscolumns. isnullable as isnull,
Case when syscomments. Text is null
Then ''else syscomments. Text end as [Default],
Case when columnproperty (syscolumns. ID, syscolumns. Name, 'isidentity ')
= 1 then '√ 'else' end as ID, case when exists
(Select 1
From sysobjects
Where xtype = 'pk' and name in
(Select name
From sysindexes
Where indid in
(Select indid
From sysindexkeys
Where id = syscolumns. ID and colid = syscolumns. colid )))
Then' √ 'else' 'end as primary key into # New
From syscolumns inner join
Sysobjects on sysobjects. ID = syscolumns. ID inner join
Policypes on syscolumns. xtype = policypes. xtype left Outer Join
Sysproperties properties on syscolumns. ID = properties. ID and
Syscolumns. colid = properties. smallid left Outer Join
Sysproperties on sysobjects. ID = sysproperties. ID and
Sysproperties. smallid = 0 left Outer Join
Syscomments on syscolumns. cdefault = syscomments. ID
Where (sysobjects. xtype = 'U ')

If @ I = 1
Begin
Select N. * -- Data Dictionary information added to the new database after comparison between the new database and the old database
From # new N left join # Old o on N. [Table] = O. [Table] and N. Field = O. field where O. [Table] is null
Or O. Field is null order by N. [Table], n. Field
End
Else
Begin
If @ I = 2
Begin
Select O. * -- more data dictionary information of the old database after comparison between the new database and the old database
From # New n right join # Old o on N. [Table] = O. [Table] and N. Field = O. field where N. [Table] is null
Or N. Field is null order by O. [Table], O. Field
End
Else
Begin
If @ I = 3
Begin
Select * -- Data Dictionary information of the table added to the new database after comparison between the new database and the old database
From # New where [Table] <> All (select [Table] From # Old) order by [Table], Field
End
Else
Begin
If @ I = 4
Begin
Select * -- Data Dictionary information of the tables in the old database after comparison between the new database and the old database
From # Old where [Table] <> All (select [Table] From # New) order by [Table], Field
End
Else
Begin
Select 'failed'
End
End
End
End

Drop table # Old
Drop table # New