p5g9htYQ

1USE master
2GO
3CREATE TRIGGER trg_DDL_ChangeCOllationDatabase
4ON ALL SERVER
5FOR CREATE_DATABASE
6AS
7declare @databasename varchar(200)
8set @databasename =db_name()
9    ALTER DATABASE @databasename COLLATE xxxxxxxxxxxxxxxxxxx
10GO
11	
12SET NOEXEC ON;
13
14SELECT N'g' COLLATE Latin1;
15
16SET NOEXEC OFF;
17	
18SET NOEXEC ON;
19
20BEGIN TRAN
21CREATE TABLE #t (Col1 INT);
22ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2;
23ROLLBACK TRAN;
24
25SET NOEXEC OFF;
26	
27USE [master];
28GO
29CREATE TRIGGER trg_DDL_ChangeDatabaseCollation
30ON ALL SERVER
31FOR CREATE_DATABASE
32AS
33SET NOCOUNT ON;
34
35DECLARE @CollationName [sysname] = N'Latin1_General_100_BIN2',
36        @SQL NVARCHAR(4000);
37
38SELECT @SQL = N'ALTER DATABASE ' + QUOTENAME(sd.[name]) + N' COLLATE ' + @CollationName
39FROM   sys.databases sd
40WHERE  sd.[name] = EVENTDATA().value(N'(/EVENT_INSTANCE/DatabaseName)[1]', N'sysname')
41AND    sd.[collation_name] <> @CollationName;
42
43IF (@SQL IS NOT NULL)
44BEGIN
45  PRINT @SQL; -- DEBUG
46  COMMIT TRAN; -- close existing Transaction, else will get error
47  EXEC sys.sp_executesql @SQL;
48  BEGIN TRAN; -- begin new Transaction, else will get different error
49END;
50ELSE
51BEGIN
52  PRINT 'Collation already correct.';
53END;
54
55GO
56	
57-- skip ALTER:
58CREATE DATABASE [tttt] COLLATE Latin1_General_100_BIN2;
59DROP DATABASE [tttt];
60
61-- perform ALTER:
62CREATE DATABASE [tttt] COLLATE SQL_Latin1_General_CP1_CI_AI;
63DROP DATABASE [tttt];
64	
65IF (@SQL IS NOT NULL)
66BEGIN
67  /*
68  PRINT @SQL; -- DEBUG
69  COMMIT TRAN; -- close existing Transaction, else will get error
70  EXEC sys.sp_executesql @sql;
71  BEGIN TRAN; -- begin new Transaction, else will get different error
72  */
73  DECLARE @CMD NVARCHAR(MAX) = N'EXEC xp_cmdshell N''sqlcmd -S . -d master -E -Q "'
74                             + @SQL + N';" -t 15''';
75  PRINT @CMD;
76  EXEC (@CMD);
77END;
78ELSE
79...
80	
81USE master
82GO
83CREATE TRIGGER trg_DDL_ChangeCOllationDatabase
84ON ALL SERVER
85FOR CREATE_DATABASE
86AS
87SET NOCOUNT ON; 
88DECLARE @databasename NVARCHAR(256) = N''
89DECLARE @event_data XML; 
90DECLARE @sql NVARCHAR(4000) = N''
91
92SET @event_data = EVENTDATA()
93
94SET @databasename = @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(256)') 
95
96SET @sql += 'ALTER DATABASE ' + QUOTENAME(@databasename) + ' COLLATE al''z a-b-cee''z'
97
98PRINT @sql
99
100EXEC sys.sp_executesql @sql
101
102GO
103	
104CREATE DATABASE DingDong
105	
106EXEC sp_MSforeachdb N'IF EXISTS 
107(
108     select top 1 name from sys.databases where collation_name != 
109     SQL_Latin1_General_CP1_CI_AS
110)
111BEGIN
112    -- do something
113END';