In the attachment you find the SQL script with the procedure of comparing two databases.
Result of the script is the query output in the format:
  • TYPE - type of the founded difference (table, column, index, key)
  • NAME - name of the difference (TableName.ObjectName for type 'COLUMN', 'INDEX', 'KEY' or TableName for type 'TABLE')
  • STATUS - values 'REMOVED', 'ADDED' or 'MODIFIED' in relation of SourceDB to TargetDB
  • DESCRIPTION - short info about object changes

*You should replace @SourceDB and @TargetDB values with your database names.

*This script is result of using the procedure from other author but completely rewritten.

001.DECLARE
002.    @SourceDB SYSNAME='Source',
003.    @TargetDB SYSNAME='Target',
004.    @ExcludeSQL VARCHAR(8000) = 'SELECT TR.NAME COLLATE DATABASE_DEFAULT FROM #TABLES_RESULT TR'
005. 
006.  SET nocount ON
007.  SET ansi_warnings ON
008.  SET ansi_nulls ON
009.   
010.  DECLARE @sqlStr VARCHAR(8000) 
011.  SET @SourceDB= Rtrim(Ltrim(@SourceDB))
012.  IF DB_ID(@SourceDB) IS NULL
013.  BEGIN
014.        PRINT 'Error: Unable to find the database '+ @SourceDB +'!!!'
015.        RETURN
016.  END
017.   
018.  SET @TargetDB= Rtrim(Ltrim(@TargetDB))
019.  IF DB_ID(@SourceDB) IS NULL
020.  BEGIN
021.        PRINT 'Error: Unable to find the database '+ @TargetDB +'!!!'
022.        RETURN
023.    END
024.     
025.  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDB) + 25)
026.  PRINT 'Comparing databases ' + @SourceDB + ' and ' + @TargetDB
027.  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDB) + 25)    
028.  -----------------------------------------------------------------------------------------
029.    -- Create temp tables needed to hold the db structure
030.    -----------------------------------------------------------------------------------------  
031.     
032.    IF OBJECT_ID('TEMPDB..#TABLES_RESULT')IS NOT NULL
033.        DROP TABLE #TABLES_RESULT
034.    IF OBJECT_ID('TEMPDB..#SOURCE_COLUMNS')IS NOT NULL
035.        DROP TABLE #SOURCE_COLUMNS
036.    IF OBJECT_ID('TEMPDB..#TARGET_COLUMNS')IS NOT NULL
037.        DROP TABLE #TARGET_COLUMNS
038.    IF OBJECT_ID('TEMPDB..#COLUMNS_RESULT')IS NOT NULL
039.        DROP TABLE #COLUMNS_RESULT
040.    IF OBJECT_ID('TEMPDB..#SOURCE_IDX')IS NOT NULL
041.        DROP TABLE #SOURCE_IDX
042.    IF OBJECT_ID('TEMPDB..#TARGET_IDX')IS NOT NULL
043.        DROP TABLE #TARGET_IDX
044.    IF OBJECT_ID('TEMPDB..#IDX_RESULT')IS NOT NULL
045.        DROP TABLE #IDX_RESULT
046.    IF OBJECT_ID('TEMPDB..#SOURCE_FK')IS NOT NULL
047.        DROP TABLE #SOURCE_FK
048.    IF OBJECT_ID('TEMPDB..#TARGET_FK')IS NOT NULL
049.        DROP TABLE #TARGET_FK
050.    IF OBJECT_ID('TEMPDB..#FK_RESULT')IS NOT NULL
051.        DROP TABLE #FK_RESULT
052. 
053.  CREATE TABLE #TABLES_RESULT
054.  (
055.        ID INT IDENTITY(1,1),
056.        TYPE VARCHAR(30),
057.        NAME SYSNAME,
058.        STATUS VARCHAR(30),
059.        DESCRIPTION VARCHAR(1000)
060.  )
061. 
062.  CREATE TABLE #SOURCE_COLUMNS
063.  (
064.        ID INT IDENTITY(1,1),
065.        TABLENAME SYSNAME,
066.        COLUMNNAME SYSNAME,
067.        DATATYPE SYSNAME,
068.        NULLABLE VARCHAR(15),
069.  )
070. 
071.  CREATE TABLE #TARGET_COLUMNS
072.  (
073.        ID INT IDENTITY(1,1),
074.        TABLENAME SYSNAME,
075.        COLUMNNAME SYSNAME,
076.        DATATYPE SYSNAME,
077.        NULLABLE VARCHAR(15),
078.  )
079. 
080.  CREATE TABLE #COLUMNS_RESULT
081.  (
082.        ID INT IDENTITY(1,1),
083.        TYPE VARCHAR(30),
084.        NAME SYSNAME,
085.        STATUS VARCHAR(30),
086.        DESCRIPTION VARCHAR(1000)
087.  )
088. 
089.  CREATE TABLE #SOURCE_IDX
090.  (
091.        ID INT IDENTITY(1,1),
092.        TABLE_NAME SYSNAME,
093.        IDX_NAME SYSNAME ,
094.        IDX_TYPE VARCHAR(20),
095.        IS_PRIMARY_KEY VARCHAR(10),
096.        IS_UNIQUE VARCHAR(10),
097.        IDX_COLUMNS VARCHAR(1000),
098.        IDX_INCLUDED_COLUMNS VARCHAR(1000)
099.  )
100. 
101.  CREATE TABLE #TARGET_IDX
102.  (
103.        ID INT IDENTITY(1,1),
104.        TABLE_NAME SYSNAME,
105.        IDX_NAME SYSNAME ,
106.        IDX_TYPE VARCHAR(20),
107.        IS_PRIMARY_KEY VARCHAR(10),
108.        IS_UNIQUE VARCHAR(10),
109.        IDX_COLUMNS VARCHAR(1000),
110.        IDX_INCLUDED_COLUMNS VARCHAR(1000)
111.  )
112. 
113.    CREATE TABLE #IDX_RESULT
114.  (
115.        ID INT IDENTITY(1,1),
116.        TYPE VARCHAR(30),
117.        NAME SYSNAME,
118.        STATUS VARCHAR(30),
119.        DESCRIPTION VARCHAR(1000)
120.  )
121. 
122.  CREATE TABLE #SOURCE_FK
123.  (
124.        ID INT IDENTITY(1,1),
125.        FK_NAME SYSNAME,
126.        FK_TABLE SYSNAME,
127.        FK_COLUMNS VARCHAR(1000),
128.        PK_TABLE SYSNAME,
129.        PK_COLUMNS VARCHAR(1000)
130.  )
131. 
132.  CREATE TABLE #TARGET_FK
133.  (
134.        ID INT IDENTITY(1,1),
135.        FK_NAME SYSNAME,
136.        FK_TABLE SYSNAME,
137.        FK_COLUMNS VARCHAR(1000),
138.        PK_TABLE SYSNAME,
139.        PK_COLUMNS VARCHAR(1000)
140.  )
141. 
142.  CREATE TABLE #FK_RESULT
143.  (
144.        ID INT IDENTITY(1,1),
145.        TYPE VARCHAR(30),
146.        NAME SYSNAME,
147.        STATUS VARCHAR(30),
148.        DESCRIPTION VARCHAR(1000)
149.  )
150. 
151.  PRINT 'Getting the tables result!';
152.    INSERT INTO #TABLES_RESULT(TYPE, NAME, STATUS, DESCRIPTION)
153.    EXEC(
154.    'SELECT
155.        ''TABLE'' AS TYPE,
156.        CASE WHEN S.NAME IS NULL THEN T.NAME ELSE S.NAME END COLLATE DATABASE_DEFAULT NAME,
157.        CASE WHEN S.NAME IS NULL THEN ''ADDED'' ELSE ''REMOVED'' END COLLATE DATABASE_DEFAULT STATUS,
158.        NULL AS DESCRIPTION
159.    FROM ['+@SourceDB+'].SYS.TABLES S
160.    FULL OUTER JOIN ['+@TargetDB+'].SYS.TABLES T ON T.NAME COLLATE DATABASE_DEFAULT = S.NAME COLLATE DATABASE_DEFAULT
161.    WHERE S.NAME IS NULL OR T.NAME IS NULL');
162.  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDB) + 25);
163. 
164.  PRINT 'Getting the source columns!';
165.    INSERT INTO #SOURCE_COLUMNS(TABLENAME, COLUMNNAME, DATATYPE, NULLABLE)
166.    EXEC(
167.    'SELECT
168.        ST.NAME TABLENAME,
169.        SC.NAME COLUMNNAME,
170.        STY.NAME + CASE WHEN STY.NAME IN (''char'',''varchar'',''nvarchar'') THEN
171.        ''(''+CASE WHEN SC.MAX_LENGTH > 0 THEN CAST(SC.MAX_LENGTH AS VARCHAR) ELSE ''max'' END + '')'' ELSE ''''END DATATYPE,
172.        CASE WHEN SC.IS_NULLABLE = 0 THEN ''not null'' ELSE ''null'' END NULLABLE
173.    FROM ['+@SourceDB+'].SYS.TABLES ST
174.    INNER JOIN ['+@SourceDB+'].SYS.COLUMNS SC ON ST.object_id = SC.object_id
175.    INNER JOIN ['+@SourceDB+'].SYS.TYPES STY ON SC.user_type_id = STY.user_type_id
176.    WHERE ST.NAME NOT IN ('+@ExcludeSQL+')');
177.  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDB) + 25);
178. 
179.  PRINT 'Getting the target columns!';
180.    INSERT INTO #TARGET_COLUMNS(TABLENAME, COLUMNNAME, DATATYPE, NULLABLE)
181.    EXEC(
182.    'SELECT
183.        ST.NAME TABLENAME,
184.        SC.NAME COLUMNNAME,
185.        STY.NAME + CASE WHEN STY.NAME IN (''char'',''varchar'',''nvarchar'') THEN
186.        ''(''+CASE WHEN SC.MAX_LENGTH > 0 THEN CAST(SC.MAX_LENGTH AS VARCHAR) ELSE ''max'' END + '')'' ELSE ''''END DATATYPE,
187.        CASE WHEN SC.IS_NULLABLE = 0 THEN ''not null'' ELSE ''null'' END NULLABLE
188.    FROM ['+@TargetDB+'].SYS.TABLES ST
189.    INNER JOIN ['+@TargetDB+'].SYS.COLUMNS SC ON ST.object_id=SC.object_id
190.    INNER JOIN ['+@TargetDB+'].SYS.TYPES STY ON SC.user_type_id =STY.user_type_id
191.    WHERE ST.NAME NOT IN ('+@ExcludeSQL+')');
192.  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDB) + 25);
193. 
194.  PRINT 'Getting the columns result!';
195.    INSERT INTO #COLUMNS_RESULT(TYPE, NAME, STATUS, DESCRIPTION)
196.    EXEC(
197.    'SELECT
198.        ''COLUMN'' AS TYPE,
199.        CASE WHEN S.COLUMNNAME IS NULL THEN T.COLUMNNAME
200.             ELSE S.COLUMNNAME END NAME,
201. 
202.        CASE WHEN S.COLUMNNAME IS NULL THEN ''ADDED''
203.             WHEN T.COLUMNNAME IS NULL THEN ''REMOVED''
204.             ELSE ''MODIFIED'' END STATUS,
205. 
206.        CASE
207.             WHEN S.COLUMNNAME IS NULL THEN ''DETAILS:''
208.                  + '' Type [ '' + T.DATATYPE + '' ]''
209.                  + '', Nullable [ '' + T.NULLABLE + '' ]''
210.             WHEN T.COLUMNNAME IS NULL THEN ''DETAILS:''
211.                  + '' Type [ '' + S.DATATYPE + '' ]''
212.                  + '', Nullable [ '' + S.NULLABLE + '' ]''
213.             WHEN S.COLUMNNAME = T.COLUMNNAME THEN ''MODIFICATIONS:''
214.                  + CASE WHEN S.DATATYPE != T.DATATYPE THEN '' Type [ '' + S.DATATYPE + '' -> '' + T.DATATYPE + '' ]'' ELSE '''' END
215.                  + CASE WHEN S.NULLABLE != T.NULLABLE THEN '', Nullable [ '' + S.NULLABLE + '' -> '' + T.NULLABLE + '' ]'' ELSE '''' END
216.             ELSE NULL END DESCRIPTION
217.    FROM #SOURCE_COLUMNS S
218.    FULL OUTER JOIN #TARGET_COLUMNS T ON T.COLUMNNAME = S.COLUMNNAME AND T.TABLENAME = S.TABLENAME
219.    WHERE
220.        S.COLUMNNAME IS NULL
221.        OR T.COLUMNNAME IS NULL
222.        OR S.DATATYPE != T.DATATYPE
223.        OR S.NULLABLE != T.NULLABLE');
224.  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDB) + 25);
225. 
226.  PRINT 'Getting the source indexes!';
227.    INSERT INTO #SOURCE_IDX(TABLE_NAME, IDX_NAME, IDX_TYPE, IS_PRIMARY_KEY, IS_UNIQUE, IDX_COLUMNS, IDX_INCLUDED_COLUMNS)
228.    EXEC(
229.    'WITH CTE AS (
230.    SELECT
231.        IC.INDEX_ID,
232.        IC.OBJECT_ID,
233.        T.NAME AS TABLE_NAME,
234.        I.NAME AS INDEX_NAME,
235.        CASE WHEN IC.IS_INCLUDED_COLUMN = 0 THEN C.NAME END AS COLUMNNAME,
236.        CASE WHEN IC.IS_INCLUDED_COLUMN = 1 THEN C.NAME END AS INCLUDEDCOLUMN,
237.        I.TYPE_DESC,
238.        I.IS_PRIMARY_KEY,I.IS_UNIQUE
239.    FROM ['+@SourceDB+'].SYS.INDEXES I
240.    INNER JOIN ['+@SourceDB+'].SYS.INDEX_COLUMNS IC
241.            ON  I.INDEX_ID = IC.INDEX_ID
242.            AND I.OBJECT_ID = IC.OBJECT_ID
243.    INNER JOIN ['+@SourceDB+'].SYS.COLUMNS C
244.            ON  IC.COLUMN_ID = C.COLUMN_ID
245.            AND I.OBJECT_ID = C.OBJECT_ID
246.    INNER JOIN ['+@SourceDB+'].SYS.TABLES T
247.            ON  I.OBJECT_ID = T.OBJECT_ID)
248. 
249.    SELECT C.TABLE_NAME, C.INDEX_NAME, C.TYPE_DESC INDEX_TYPE,
250.           CASE WHEN C.IS_PRIMARY_KEY = 0 THEN ''False'' ELSE ''True'' END IS_PRIMARY_KEY,
251.           CASE WHEN C.IS_UNIQUE = 0 THEN ''False'' ELSE ''True'' END IS_UNIQUE,
252.           STUFF(( SELECT '', ''+ A.COLUMNNAME FROM CTE A WHERE C.INDEX_ID = A.INDEX_ID AND C.OBJECT_ID = A.OBJECT_ID FOR XML PATH('''')), 1, 1, '''') AS IDX_COLUMNS,
253.           STUFF(( SELECT '', ''+ A.INCLUDEDCOLUMN FROM CTE A WHERE C.INDEX_ID = A.INDEX_ID AND C.OBJECT_ID = A.OBJECT_ID FOR XML PATH('''')), 1, 1, '''') AS IDX_INCLUDED_COLUMNS
254.    FROM CTE C
255.    WHERE C.TABLE_NAME NOT IN ('+@ExcludeSQL+')
256.    GROUP BY C.INDEX_ID, C.OBJECT_ID, C.TABLE_NAME, C.INDEX_NAME, C.TYPE_DESC, C.IS_PRIMARY_KEY, C.IS_UNIQUE')
257.  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDB) + 25);
258. 
259.  PRINT 'Getting the target indexes!';
260.    INSERT INTO #TARGET_IDX(TABLE_NAME, IDX_NAME, IDX_TYPE, IS_PRIMARY_KEY, IS_UNIQUE, IDX_COLUMNS, IDX_INCLUDED_COLUMNS)
261.    EXEC(
262.    'WITH CTE AS (
263.    SELECT
264.        IC.INDEX_ID,
265.        IC.OBJECT_ID,
266.        T.NAME AS TABLE_NAME,
267.        I.NAME AS INDEX_NAME,
268.        CASE WHEN IC.IS_INCLUDED_COLUMN = 0 THEN C.NAME END AS COLUMNNAME,
269.        CASE WHEN IC.IS_INCLUDED_COLUMN = 1 THEN C.NAME END AS INCLUDEDCOLUMN,
270.        I.TYPE_DESC,
271.        I.IS_PRIMARY_KEY,I.IS_UNIQUE
272.    FROM ['+@TargetDB+'].SYS.INDEXES I
273.    INNER JOIN ['+@TargetDB+'].SYS.INDEX_COLUMNS IC
274.            ON  I.INDEX_ID = IC.INDEX_ID
275.            AND I.OBJECT_ID = IC.OBJECT_ID
276.    INNER JOIN ['+@TargetDB+'].SYS.COLUMNS C
277.            ON  IC.COLUMN_ID = C.COLUMN_ID
278.            AND I.OBJECT_ID = C.OBJECT_ID
279.    INNER JOIN ['+@TargetDB+'].SYS.TABLES T
280.            ON  I.OBJECT_ID = T.OBJECT_ID)
281. 
282.    SELECT C.TABLE_NAME, C.INDEX_NAME, C.TYPE_DESC INDEX_TYPE,
283.           CASE WHEN C.IS_PRIMARY_KEY = 0 THEN ''False'' ELSE ''True'' END IS_PRIMARY_KEY,
284.           CASE WHEN C.IS_UNIQUE = 0 THEN ''False'' ELSE ''True'' END IS_UNIQUE,
285.           STUFF(( SELECT '', ''+ A.COLUMNNAME FROM CTE A WHERE C.INDEX_ID = A.INDEX_ID AND C.OBJECT_ID = A.OBJECT_ID FOR XML PATH('''')), 1, 1, '''') AS IDX_COLUMNS,
286.           STUFF(( SELECT '', ''+ A.INCLUDEDCOLUMN FROM CTE A WHERE C.INDEX_ID = A.INDEX_ID AND C.OBJECT_ID = A.OBJECT_ID FOR XML PATH('''')), 1, 1, '''') AS IDX_INCLUDED_COLUMNS
287.    FROM CTE C
288.    WHERE C.TABLE_NAME NOT IN ('+@ExcludeSQL+')
289.    GROUP BY C.INDEX_ID, C.OBJECT_ID, C.TABLE_NAME, C.INDEX_NAME, C.TYPE_DESC, C.IS_PRIMARY_KEY, C.IS_UNIQUE')
290.  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDB) + 25);
291. 
292.  PRINT 'Getting the indexes result!';
293.    INSERT INTO #IDX_RESULT(TYPE, NAME, STATUS, DESCRIPTION)
294.    EXEC(
295.    'SELECT
296.        ''INDEX'' AS TYPE,
297.        CASE WHEN S.IDX_NAME IS NULL THEN T.IDX_NAME
298.             ELSE S.IDX_NAME END NAME,
299. 
300.        CASE WHEN S.IDX_NAME IS NULL THEN ''ADDED''
301.             WHEN T.IDX_NAME IS NULL THEN ''REMOVED''
302.             ELSE ''MODIFIED'' END STATUS,
303. 
304.        CASE
305.             WHEN S.IDX_NAME IS NULL THEN ''DETAILS:''
306.                  + '' Type [ '' + T.IDX_TYPE + '' ]''
307.                  + '', IsPrimary [ '' + T.IS_PRIMARY_KEY + '' ]''
308.                  + '', IsUnique [ '' + T.IS_UNIQUE + '' ]''
309.                  + '', Columns [ '' + T.IDX_COLUMNS + '' ]''
310.                  + CASE WHEN T.IDX_INCLUDED_COLUMNS IS NULL THEN '''' ELSE
311.                  '', IncludedColumns [ '' + T.IDX_INCLUDED_COLUMNS + '' ]'' END
312.             WHEN T.IDX_NAME IS NULL THEN ''DETAILS:''
313.                  + '' Type [ '' + S.IDX_TYPE + '' ]''
314.                  + '', IsPrimary [ '' + S.IS_PRIMARY_KEY + '' ]''
315.                  + '', IsUnique [ '' + S.IS_UNIQUE + '' ]''
316.                  + '', Columns [ '' + S.IDX_COLUMNS + '' ]''
317.                  + CASE WHEN S.IDX_INCLUDED_COLUMNS IS NULL THEN '''' ELSE
318.                  '', IncludedColumns [ '' + S.IDX_INCLUDED_COLUMNS + '' ]'' END
319.             WHEN S.IDX_NAME = T.IDX_NAME THEN ''MODIFICATIONS:''
320.                  + CASE WHEN S.IDX_TYPE != T.IDX_TYPE THEN '' Type [ '' + S.IDX_TYPE + '' -> '' + T.IDX_TYPE + '' ]'' ELSE '''' END
321.                  + CASE WHEN S.IS_PRIMARY_KEY != T.IS_PRIMARY_KEY THEN '', IsPrimary [ '' + S.IS_PRIMARY_KEY + '' -> '' + T.IS_PRIMARY_KEY + '' ]'' ELSE '''' END
322.                  + CASE WHEN S.IS_UNIQUE != T.IS_UNIQUE THEN '', IsUnique [ '' + S.IS_UNIQUE + '' -> '' + T.IS_UNIQUE + '' ]'' ELSE '''' END
323.                  + CASE WHEN S.IDX_COLUMNS != T.IDX_COLUMNS THEN '', Columns [ '' + ISNULL(S.IDX_COLUMNS, '''') + '' -> '' + ISNULL(T.IDX_COLUMNS, '''') + '' ]'' ELSE '''' END
324.                  + CASE WHEN S.IDX_INCLUDED_COLUMNS != T.IDX_INCLUDED_COLUMNS THEN '', IncludedColumns [ ''
325.                            + ISNULL(S.IDX_INCLUDED_COLUMNS, '''') + '' -> '' + ISNULL(T.IDX_INCLUDED_COLUMNS, '''') + '' ]'' ELSE '''' END
326.             ELSE NULL END DESCRIPTION
327.    FROM #SOURCE_IDX S
328.    FULL OUTER JOIN #TARGET_IDX T ON T.IDX_NAME = S.IDX_NAME AND T.TABLE_NAME = S.TABLE_NAME
329.    WHERE
330.        S.IDX_NAME IS NULL
331.        OR T.IDX_NAME IS NULL
332.        OR S.IDX_TYPE != T.IDX_TYPE
333.        OR S.IS_PRIMARY_KEY != T.IS_PRIMARY_KEY
334.        OR S.IS_UNIQUE != T.IS_UNIQUE
335.        OR S.IDX_COLUMNS != T.IDX_COLUMNS
336.        OR S.IDX_INCLUDED_COLUMNS != T.IDX_INCLUDED_COLUMNS')
337.  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDB) + 25);
338. 
339.  PRINT 'Getting the source keys!';
340.    INSERT INTO #SOURCE_FK(FK_NAME, FK_TABLE, FK_COLUMNS, PK_TABLE, PK_COLUMNS)
341.    EXEC(
342.    'WITH CTE AS (
343.    SELECT
344.        PT.NAME PK_TABLE,
345.        C1.NAME PK_COLUMN,
346.        FT.NAME FK_TABLE,
347.        C2.NAME FK_COLUMN,
348.        FK.NAME FK_NAME
349.    FROM
350.    ['+@SourceDB+'].SYS.FOREIGN_KEYS FK
351.    INNER JOIN ['+@SourceDB+'].SYS.FOREIGN_KEY_COLUMNS FKC
352.        ON FK.OBJECT_ID=FKC.CONSTRAINT_OBJECT_ID
353.    INNER JOIN ['+@SourceDB+'].SYS.COLUMNS C1
354.        ON FKC.PARENT_COLUMN_ID=C1.COLUMN_ID
355.        AND FKC.PARENT_OBJECT_ID=C1.OBJECT_ID
356.    INNER JOIN ['+@SourceDB+'].SYS.COLUMNS C2
357.        ON FKC.REFERENCED_COLUMN_ID=C2.COLUMN_ID
358.        AND FKC.REFERENCED_OBJECT_ID=C2.OBJECT_ID
359.    LEFT JOIN ['+@SourceDB+'].SYS.TABLES FT ON FT.OBJECT_ID = FK.REFERENCED_OBJECT_ID
360.    LEFT JOIN ['+@SourceDB+'].SYS.TABLES PT ON PT.OBJECT_ID = FK.PARENT_OBJECT_ID)
361. 
362.    SELECT C.FK_NAME,
363.           C.FK_TABLE,
364.           STUFF( ( SELECT '', ''+ A.FK_COLUMN FROM CTE A WHERE C.FK_NAME = A.FK_NAME AND C.FK_TABLE=A.FK_TABLE FOR XML PATH('''')),1 ,1, '''') AS FK_COLUMNS,
365.           C.PK_TABLE,
366.           STUFF( ( SELECT '', ''+ A.PK_COLUMN FROM CTE A WHERE C.FK_NAME = A.FK_NAME AND C.PK_TABLE=A.PK_TABLE FOR XML PATH('''')),1 ,1, '''') AS PK_COLUMNS
367.    FROM CTE C
368.    WHERE C.PK_TABLE NOT IN ('+@ExcludeSQL+') AND C.FK_TABLE NOT IN ('+@ExcludeSQL+')
369.    GROUP BY C.FK_NAME, C.FK_TABLE, C.PK_TABLE')
370.  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDB) + 25);
371. 
372.  PRINT 'Getting the target keys!';
373.    INSERT INTO #TARGET_FK(FK_NAME, FK_TABLE, FK_COLUMNS, PK_TABLE, PK_COLUMNS)
374.    EXEC(
375.    'WITH CTE AS (
376.    SELECT
377.        PT.NAME PK_TABLE,
378.        C1.NAME PK_COLUMN,
379.        FT.NAME FK_TABLE,
380.        C2.NAME FK_COLUMN,
381.        FK.NAME FK_NAME
382.    FROM
383.    ['+@TargetDB+'].SYS.FOREIGN_KEYS FK
384.    INNER JOIN ['+@TargetDB+'].SYS.FOREIGN_KEY_COLUMNS FKC
385.        ON FK.OBJECT_ID=FKC.CONSTRAINT_OBJECT_ID
386.    INNER JOIN ['+@TargetDB+'].SYS.COLUMNS C1
387.        ON FKC.PARENT_COLUMN_ID=C1.COLUMN_ID
388.        AND FKC.PARENT_OBJECT_ID=C1.OBJECT_ID
389.    INNER JOIN ['+@TargetDB+'].SYS.COLUMNS C2
390.        ON FKC.REFERENCED_COLUMN_ID=C2.COLUMN_ID
391.        AND FKC.REFERENCED_OBJECT_ID=C2.OBJECT_ID
392.    LEFT JOIN ['+@TargetDB+'].SYS.TABLES FT ON FT.OBJECT_ID = FK.REFERENCED_OBJECT_ID
393.    LEFT JOIN ['+@TargetDB+'].SYS.TABLES PT ON PT.OBJECT_ID = FK.PARENT_OBJECT_ID)
394. 
395.    SELECT C.FK_NAME,
396.           C.FK_TABLE,
397.           STUFF( ( SELECT '', ''+ A.FK_COLUMN FROM CTE A WHERE C.FK_NAME = A.FK_NAME AND C.FK_TABLE=A.FK_TABLE FOR XML PATH('''')),1 ,1, '''') AS FK_COLUMNS,
398.           C.PK_TABLE,
399.           STUFF( ( SELECT '', ''+ A.PK_COLUMN FROM CTE A WHERE C.FK_NAME = A.FK_NAME AND C.PK_TABLE=A.PK_TABLE FOR XML PATH('''')),1 ,1, '''') AS PK_COLUMNS
400.    FROM CTE C
401.    WHERE C.PK_TABLE NOT IN ('+@ExcludeSQL+') AND C.FK_TABLE NOT IN ('+@ExcludeSQL+')
402.    GROUP BY C.FK_NAME, C.FK_TABLE, C.PK_TABLE')
403.  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDB) + 25);
404. 
405.  PRINT 'Getting the keys result!';
406.    INSERT INTO #FK_RESULT(TYPE, NAME, STATUS, DESCRIPTION)
407.    EXEC(
408.    'SELECT
409.        ''KEY'' AS TYPE,
410.        CASE WHEN S.FK_NAME IS NULL THEN T.FK_NAME
411.             ELSE S.FK_NAME END NAME,
412. 
413.        CASE WHEN S.FK_NAME IS NULL THEN ''ADDED''
414.             WHEN T.FK_NAME IS NULL THEN ''REMOVED''
415.             ELSE ''MODIFIED'' END STATUS,
416. 
417.        CASE
418.             WHEN S.FK_NAME IS NULL THEN ''DETAILS:''
419.                  + '' FK_Table [ '' + T.FK_TABLE + '' ]''
420.                  + '', FK_Columns [ '' + T.FK_COLUMNS + '' ]''
421.                  + '', PK_Table [ '' + T.PK_TABLE + '' ]''
422.                  + '', PK_Columns [ '' + T.PK_COLUMNS + '' ]''
423.             WHEN T.FK_NAME IS NULL THEN ''DETAILS:''
424.                  + '' FK_Table [ '' + S.FK_TABLE + '' ]''
425.                  + '', FK_Columns [ '' + S.FK_COLUMNS + '' ]''
426.                  + '', PK_Table [ '' + S.PK_TABLE + '' ]''
427.                  + '', PK_Columns [ '' + S.PK_COLUMNS + '' ]''
428.             WHEN S.FK_NAME = T.FK_NAME THEN ''MODIFICATIONS:''
429.                  + CASE WHEN S.FK_TABLE != T.FK_TABLE THEN '' FK_Table [ '' + S.FK_TABLE + '' -> '' + T.FK_TABLE + '' ]'' ELSE '''' END
430.                  + CASE WHEN S.FK_COLUMNS != T.FK_COLUMNS THEN '', FK_Columns [ '' + S.FK_COLUMNS + '' -> '' + T.FK_COLUMNS + '' ]'' ELSE '''' END
431.                  + CASE WHEN S.PK_TABLE != T.PK_TABLE THEN '' PK_Table [ '' + S.PK_TABLE + '' -> '' + T.PK_TABLE + '' ]'' ELSE '''' END
432.                  + CASE WHEN S.PK_COLUMNS != T.PK_COLUMNS THEN '', PK_Columns [ '' + S.PK_COLUMNS + '' -> '' + T.PK_COLUMNS + '' ]'' ELSE '''' END
433.             ELSE NULL END DESCRIPTION
434.    FROM #SOURCE_FK S
435.    FULL OUTER JOIN #TARGET_FK T ON T.FK_NAME = S.FK_NAME AND T.FK_TABLE = S.FK_TABLE
436.    WHERE
437.        S.FK_NAME IS NULL
438.        OR T.FK_NAME IS NULL
439.        OR S.FK_TABLE != T.FK_TABLE
440.        OR S.FK_COLUMNS != T.FK_COLUMNS
441.        OR S.PK_TABLE != T.PK_TABLE
442.        OR S.PK_COLUMNS != T.PK_COLUMNS')
443.  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDB) + 25);
444. 
445.select * from #TABLES_RESULT
446.union all
447.select * from #COLUMNS_RESULT
448.union all
449.select * from #IDX_RESULT
450.union all
451.select * from #FK_RESULT;