Collation

Collation

-- Script that returns all available collations
select * from fn_helpcollations()
-- Script that returns all your databases' collations
select name, collation_name from sys.databases
-- Test_DBA SQL_Latin1_General_CP1_CI_AS
-- Changing Collation setting at Database Level
use master
ALTER database Test_DBA set single_user with rollback immediate
ALTER DATABASE Test_DBA COLLATE SQL_Latin1_General_CP1_CI_AS ;
ALTER database Test_DBA set multi_user

---

/* Script to compare all Collation settings
Returns 2 results sets
1) Databases where collation is different from server setting
2) Columns where collation is different from database setting
*/
IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS
WHERE ID = OBJECT_ID('tempdb.dbo.#CollationComparison'))
DROP TABLE #CollationComparison
CREATE TABLE #CollationComparison
(Database_Name SYSNAME
,Table_Schema SYSNAME
,Table_Name SYSNAME
,Column_Name SYSNAME
,Server_Collation SYSNAME
,Database_Collation SYSNAME
,Column_Collation SYSNAME)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @dbname NVARCHAR(200)
DECLARE dbcursor CURSOR FOR
select name from sys.databases where state_desc='Online'
OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
print @dbname
SET @SQL = 'INSERT INTO #CollationComparison
(Database_Name
,Table_Schema
,Table_Name
,Column_Name
,Server_Collation
,Database_Collation
,Column_Collation)
SELECT
C.TABLE_CATALOG AS DATABASE_NAME
,C.TABLE_SCHEMA
,C.TABLE_NAME
,C.COLUMN_NAME
,CONVERT(VARCHAR,SERVERPROPERTY(''COLLATION'')) AS SERVER_COLLATION
,CONVERT(SYSNAME,DATABASEPROPERTYEX(D.NAME,''COLLATION'')) AS DATABASE_COLLATION
,C.COLLATION_NAME AS COLUMN_COLLATION
FROM [' + @dbname + '].INFORMATION_SCHEMA.COLUMNS C
INNER JOIN SYS.DATABASES D
ON DB_ID(C.TABLE_CATALOG) = DB_ID(D.NAME)
WHERE DATA_TYPE IN (''VARCHAR'' ,''CHAR'',''NVARCHAR'',''NCHAR'',''TEXT'',''NTEXT'')
'
exec sp_executesql @SQL
print @sql
FETCH NEXT FROM dbcursor INTO @dbname
END
CLOSE dbcursor
DEALLOCATE dbcursor
SELECT DISTINCT Server_Collation,Database_Collation,Database_Name
FROM #CollationComparison
WHERE Server_Collation <> Database_Collation
SELECT DISTINCT top 1 * FROM #CollationComparison WHERE Column_Collation <> Database_Collation

--SELECT name,state_desc as DatabaseStatus_sysDatabase
--FROM sys.databases where state_desc='Online'
--
--SELECT *
--FROM sys.databases where state_desc='Online'

---

USE master;
GO
CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO
ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO

---