/*============================================================================
  File:     VerifyingCollationChanges.sql

  Summary:  This script connects to a SQL 2000 server and copies over the
			collation list in order to cross reference with new
			collations in SQL Server 2005. 

  Date:     March 15, 2005

  SQL Server Version: 9.00.1090 (IDW13)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

--DROP TABLE SQL2000Collations
--DROP TABLE SQL2005Collations

EXEC sp_addlinkedserver 'RoadRunner64' -- this is my 2000 server

SELECT * 
INTO SQL2000Collations
FROM OPENQUERY(RoadRunner64, 'select * from ::fn_helpcollations()')

SELECT [name] AS [name],
	[description] AS [description]
INTO SQL2005Collations
FROM ::fn_helpcollations();

EXEC sp_help SQL2000Collations
EXEC sp_help SQL2005Collations

--SQL 2000 = SQL_Latin1_General_CP1_CI_AS Master Database
--SQL 2005 = Latin1_General_CI_AI Master Database
--			 SQL_Latin1_General_CP1_CI_AS USER DATABASES

SELECT [name] COLLATE SQL_Latin1_General_CP1_CI_AS AS [name],
	[description] COLLATE SQL_Latin1_General_CP1_CI_AS AS [description]
INTO SQL2005Collations
FROM ::fn_helpcollations();

-- Which 2000 collations are NOT in 2005
SELECT * FROM SQL2000Collations [2000]
WHERE [2000].[name] NOT IN (SELECT [name] FROM SQL2005Collations);

-- Which 2005 collations are new
SELECT * FROM SQL2005Collations [2005]
WHERE [2005].[name] NOT IN (SELECT [name] FROM SQL2000Collations);

-- CLean Up
DROP TABLE SQL2000Collations;
DROP TABLE SQL2005Collations;

sp_helpsort

exec sp_helpdb master