DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML 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
       *****************************************************