MS SQL new and old Library data dictionary comparison script

Compare | scripts | data /*ms SQL new and old Library data dictionary comparison script *

--NOTE 1: The old and new libraries must be in the same instance of the same database server, preferably logged in as SA.
--NOTE 2: This script can be used as a system upgrade to obtain relevant information for data migration.
DECLARE @i int

Set @i=4/* marked 3:1 as the data dictionary information to be added to the new library;
2 in order to get the old library more data dictionary information;
3 Data dictionary information for the table to be added to the new library;
4 The data dictionary information for the table to be obtained from the old library * *

Use temp--open Old library
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,
Systypes.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 logo, 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
systypes on syscolumns.xtype = Systypes.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 new library
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,
Systypes.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 logo, 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
systypes on syscolumns.xtype = Systypes.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.*--The data dictionary information added to the new library after the new library is compared with the old library
From #new N-left join #old O to n.[table]=o.[table] and N.field=o.field where o.[table] is null
Or O.field is a null order by N.[table],n.field
End
Else
Begin
If @i=2
Begin
Select O.*--The data dictionary information from the old library after the new library is compared with the old library
From #new n right join #old O to n.[table]=o.[table] and N.field=o.field where n.[table] is null
Or N.field is a null order by O.[table],o.field
End
Else
Begin
If @i=3
Begin
SELECT *--Data dictionary information for tables added to the new library after the new library is compared to the old library
From #new where [table] <> All (select [table] from #old) Order by [Table],field
End
Else
Begin
If @i=4
Begin
SELECT *--Data dictionary information for tables that are more than the old library after the new library is compared to the old library
from #old where [table] <> All (select [table] from #new) Order by [Table],field
End
Else
Begin
Select ' Error '
End
End
End
End

drop table #old
drop table #new