(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) https://esdbp.createaforum.com --------------------------------------------------------- ***************************************************** (DIR) Return to: Scripts ***************************************************** #Post#: 102-------------------------------------------------- Sample Script to Check Impact of Change of Database Collation By: srinivasma_exceldbp Date: November 12, 2014, 5:13 am --------------------------------------------------------- /*============================================================== ============== Summary: This script creates a sample database TestCollation and then shows the impact of changing the database collation. */ -- This script will help you see the impact of changing database collations. CREATE DATABASE TestCollation go USE TestCollation go SELECT DATABASEPROPERTYEX('TestCollation', 'Collation') -- the default for us english without changes at the windows layer or changes during SQL Server install is: -- SQL_Latin1_General_CP1_CI_AS -- To force it use: -- -- go -- To see the list of collations use the system function SELECT * FROM ::fn_helpcollations() go -- Since this started from a question on how to CHANGE collations here's how. -- Let's change the collation to a case sensitive collation... ALTER DATABASE TestCollation COLLATE Latin1_General_CS_AS_KS_WS go -- Confirm the change? SELECT DATABASEPROPERTYEX('TestCollation', 'Collation') go -- Create some objects CREATE TABLE dbo.Test ( go CREATE TABLE dbo.test ( go CREATE TABLE dbo.TEST ( go -- Now try to change back to case insensitive... ALTER DATABASE TestCollation COLLATE SQL_Latin1_General_CP1_CI_AS go /* -- This will generate these helpful messages: -- Msg 1505, Level 16, State 1, Line 1 -- CREATE UNIQUE INDEX terminated because a duplicate key was found for -- object name 'sys.sysschobjs' and index name 'nc1'. The duplicate key -- value is (0, 1, test). -- Msg 5072, Level 16, State 1, Line 1 -- ALTER DATABASE failed. The default collation of database 'TestCollation' -- cannot be set to SQL_Latin1_General_CP1_CI_AS. -- The statement has been terminated. -- For more info check out BOL under -- COLLATE -- "Specifying Collations" */ -- To see the server's setting EXEC sp_helpsort -- To see the database's setting EXEC sp_helpdb dbname -- To see the table's setting (for each column) EXEC sp_help tablename -- Clean up the test database USE master go DROP DATABASE TestCollation go *****************************************************