Listing 2: Excerpt from the sp_DBDiff.sql Stored Procedure

AS
SET NOCOUNT ON
DECLARE @sql varchar(1000)
SELECT @sql = ''

IF (object_id('tempdb.dbo.#OLD')) IS NOT NULL
  DROP TABLE #OLD
CREATE TABLE #OLD
(TableName varchar(55), ColumnName varchar(55), 
  Type varchar(16), Bytes smallint, Nulls char(3))

IF (object_id('tempdb.dbo.#NEW')) IS NOT NULL
  DROP TABLE #NEW
CREATE TABLE #NEW
(TableName varchar(55), ColumnName varchar(55), 
  Type varchar(16), Bytes smallint, Nulls char(3))

IF (object_id('tempdb.dbo.#NewTables')) IS NOT NULL
  DROP TABLE #NewTables
CREATE TABLE #NewTables
(TableName varchar(55))

IF (object_id('tempdb.dbo.#DroppedTables')) IS NOT NULL
  DROP TABLE #DroppedTables
CREATE TABLE #DroppedTables
(TableName varchar(55))

IF (object_id('tempdb.dbo.#NewColumns')) IS NOT NULL
  DROP TABLE #NewColumns
CREATE TABLE #NewColumns
(TableName varchar(55), ColumnName varchar(55), 
  Type varchar(16), Bytes smallint)

IF (object_id('tempdb.dbo.#DroppedColumns')) IS NOT NULL
  DROP TABLE #DroppedColumns
CREATE TABLE #DroppedColumns
(TableName varchar(55), ColumnName varchar(55), 
  Type varchar(16), Bytes smallint)

IF (object_id('tempdb.dbo.#ChangedColumns')) IS NOT NULL
  DROP TABLE #ChangedColumns
CREATE TABLE #ChangedColumns
(TableName varchar(55), ColumnName varchar(55), 
  OldType varchar(16), OldBytes smallint, 
  NewType varchar(16), NewBytes smallint)

-- BEGIN CALLOUT A
SELECT @SQL = '[' + @OldDB + '].dbo.sp_Cols 
-- END CALLOUT A
  @Summary = ''N'', @UserOnly = ''N'''
INSERT INTO #OLD
EXECUTE(@SQL)

-- BEGIN CALLOUT B
SELECT @SQL = '[' + @NewDB + '].dbo.sp_Cols 
-- END CALLOUT B
  @Summary = ''N'', @UserOnly = ''N'''
INSERT INTO #NEW
EXECUTE (@SQL)

INSERT INTO #NewTables
SELECT DISTINCT 
  'NewTables' = N.TableName
  FROM #New N
  LEFT JOIN  #Old O On O.TableName  = N.TableName
  WHERE O.TableName IS NULL
ORDER BY 1

INSERT INTO #DroppedTables
SELECT DISTINCT 
  'DroppedTables' = O.TableName
  FROM #Old O
  LEFT JOIN  #New N ON N.TableName  = O.TableName
  WHERE N.TableName IS NULL
ORDER BY 1

INSERT INTO #NewColumns
SELECT N.TableName,
  'NewColumn' = N.ColumnName,
  'Type' = N.Type,
  'Bytes' = N.Bytes
  FROM #New N
  LEFT JOIN #Old O ON O.TableName = N.TableName
    AND O.ColumnName = N.ColumnName
  WHERE O.TableName IS NULL
    AND N.TableName NOT IN (select TableName FROM #NewTables)
ORDER BY 1,2

INSERT INTO #DroppedColumns
SELECT O.TableName,
  'DroppedColumn' = O.ColumnName,
  'Type' = O.Type,
  'Bytes' = O.Bytes
  FROM #OLD  O
  LEFT JOIN #New N ON N.TableName = O.TableName
    AND N.ColumnName = O.ColumnName
 WHERE N.TableName IS NULL
   AND O.TableName NOT IN (select TableName FROM #DroppedTables)
ORDER BY 1,2

INSERT INTO #ChangedColumns
SELECT O.TableName,
  O.ColumnName,
  'OldType' = O.Type,
  'OldBytes' = O.Bytes,
  'NewType' = N.Type,
  'NewBytes' = N.Bytes
  FROM #OLD O
  JOIN #New N ON N.TableName = O.TableName
    AND N.ColumnName = O.ColumnName
  WHERE (N.Type  <> O.Type)
    OR (N.Bytes <> O.Bytes)