Changing collation -- URGENT

Hi,
I need an urgent help.
I have a database backup file with 30,000 tables, which has a particular col
lation SQL_Latin1_General_CP1_CI_AS.
Now I need to restore this database on a different server (srv2), but with d
ifferent collation Latin1_General_BIN.
So I created a database on srv2 with Latin1_General_BIN collation. But When
I tried to restore it from the backup file, am getting all the tables with S
QL_Latin1_General_CP1_CI_AS collation. I tried to change the database collat
ion on srv2 to Latin1_Gener
al_BIN , but still the collation is the old one.
Can anybody provide me the solution to this?
Thanks
GYKGyk
BOL says
You can also use the COLLATE clause on an ALTER DATABASE to change the
default collation of the database:
ALTER DATABASE MyDatabase COLLATE French_CI_AS
Altering the default collation of a database does not change the collations
of the columns in any existing user-defined tables. These can be changed
with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:
CREATE TABLE MyTable
(PrimaryKey int PRIMARY KEY,
CharCol varchar(10) COLLATE French_CI_AS NOT NULL
)
GO
ALTER TABLE MyTable ALTER COLUMN CharCol
varchar(10)COLLATE Latin1_General_CI_AS NOT NULL
GO
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:DCC92136-EE54-41F7-83ED-A07AC7BADDCC@.microsoft.com...
> Hi,
> I need an urgent help.
> I have a database backup file with 30,000 tables, which has a particular
collation SQL_Latin1_General_CP1_CI_AS.
> Now I need to restore this database on a different server (srv2), but with
different collation Latin1_General_BIN.
> So I created a database on srv2 with Latin1_General_BIN collation. But
When I tried to restore it from the backup file, am getting all the tables
with SQL_Latin1_General_CP1_CI_AS collation. I tried to change the database
collation on srv2 to Latin1_General_BIN , but still the collation is the old
one.
> Can anybody provide me the solution to this?
> Thanks
> GYK|||You can use the following script to change the collations of your columns,
but note that it is not really production quality code and that there are a
few issues with it. One of them is that you will see quite a lot of
duplicate primary key constraints create statements, which you will have to
remove manually.
Jacco Schalkwijk
SQL Server MVP
/*
Changing the collation of the existing tables is unfortunately not very
straightforward.
You might consider using SEM to script out the objects in the database,
change the
COLLATE clauses in the script to match the new collation, run this script in
a new
database with the new desired default collation, and finally use DTS to
transfer your
data to the new database.
However, if you wish to evaluate whether you can change the collation of
existing tables
in place, you might want to check out the helper procs in the attached
script. This
includes the following procs:
1. sp_check_for_common_collation_change_pro
blems
- makes an assessment of how difficult it would be to change
collation in-place
2. sp_generate_index_constraint_script_for_
collation_change
- outputs a CREATE INDEX/ALTER TABLE script to recreate indexes &
constraints
3. sp_generate_drop_cmds_for_collation_chan
ge
- outputs ALTER TABLE DROP CONSTRAINT and DROP INDEX commands
4. sp_generate_alter_cmds_for_collation_cha
nge
- outputs ALTER TABLE commands to change the collation
You use #1 to see if this path is practical for you. If so you run #2, #3,
and #4.
It the obstacles reported by #1 are not insurmountable, you run the drop
index/constraint
script (output of #3), the alter collation script (output of #4), and
finally the create
index/constraint script (output of #2).
If you do attempt this be sure to back up the database first. This script
is not
extremely well tested and isn't an official part of SQL; it's just something
that might
help get you started. If your schema is simple enough it may work as-is
without much
additional work. For more complex schemas there is still a considerable
amount of manual
work involved with doing a collation change in-place.
The T-SQL command ALTER TABLE...ALTER COLUMN provides a way to change a
column's
collation without exporting and re-importing the data. However, there are a
number
of things that will prevent a column's collation from being altered,
including:
- The column may not be used in any indexes or statistics.
- No check/foreign key/primary key constraints may reference the column.
- The column may not use the text or ntext datatype.
- The column may not be a computed column or be referenced by a computed
column.
- The column may not be replicated.
- No full-text index may exist on the column.
- No SCHEMABOUND views or functions may reference the column.
This script is intended to make it somewhat easier to change a database's
collation
without transferring all data to a new database.
CAUTION: This script relies on ALTER TABLE...ALTER COLUMN commands to change
the
collation of existing columns in the database. If the original and new
collations
are based on different code pages, and if you are not using Unicode data
types, SQL
Server will perform code page translation from the original collation's code
page to
the new code page as part of the ALTER COLUMN command. This is desirable in
most cases,
but it assumes that the data was stored in the correct code page to begin
with. If you
have been storing, for example, code page 850 data in a code page 1252
database, SQL
Server will interpret the data using the column's cp850-based collation and
this may
cause data loss. In general, if you are storing code page X data in a code
page Y
column, you should use a transfer-based approach to move the data to a
schema that uses
the correct collation.
How to Perform an In-Place Collation Change of a User Database:
1. Run this script, which will create the following four procedures in your
master
database:
sp_check_for_common_collation_change_pro
blems
sp_generate_index_constraint_script_for_
collation_change
sp_generate_drop_cmds_for_collation_chan
ge
sp_generate_alter_cmds_for_collation_cha
nge
2. Change to the database with the collation that is to be modified, and run
the proc
sp_check_for_common_collation_change_pro
blems. Read the output carefully to
evaluate
what potential problems you might encounter. Some schema elements will
require you to
make manual changes to the database before you can change a column's
collation. Other
schema elements (e.g. text/ntext columns) simply cannot have their collation
changed
in-place.
USE Northwind
EXEC sp_check_for_common_collation_change_pro
blems
3. Another method for changing the collation of a database is to transfer
everything
from the source database to a new database that uses the desired destination
collation.
Based on the output of sp_check_for_common_collation_change_pro
blems,
evaluate whether
it would be easier to use this approach or the transfer approach. If you
are able to
work around the potential pitfalls of an in-place collation change, continue
with the
next step after backing up your database and ensuring that you can restore
it if
necessary.
4. Run a command like the following to script out indexes and contraints
that would
prevent an in-place collation change. Pass the proc the full path to the
.SQL script
file you want the proc to create.
USE Northwind
EXEC sp_generate_index_constraint_script_for_
collation_change
@.scriptfilename = 'C:\IDX_CREATE.SQL'
5. Run the command below to geneate T-SQL commands to drop indexes and
contraints
(these objects will prevent an in-place collation change). Save the output
as
a SQL script, for example 'C:\IDX_DROP.SQL'.
USE Northwind
EXEC sp_generate_drop_cmds_for_collation_chan
ge
6. Run the command below to generate ALTER TABLE/ALTER COLUMN commands that
will
change the collation of all char/varchar/nchar/nvarchar columns in the
database.
Specify the desired new collation name via the @.newcollationname parameter.
Save
the output as a SQL script, for example, 'C:\ALTER_COLUMN.SQL'.
USE Northwind
EXEC sp_generate_alter_cmds_for_collation_cha
nge
@.newcollationname = 'SQL_Latin1_General_CP437_CI_AS'
7. Address any problems identified by
sp_check_for_common_collation_change_pro
blems.
For example, if the proc identified any schemabound views or functions you
must drop
these before continuing to the next step.
8. Use the scripts generated in steps 4, 5, and 6 to change the default
database
collation and the collation of any varchar/char/nvarchar/nchar columns in
the database:
- Run 'C:\IDX_DROP.SQL' to drop all indexes and constraints.
- Run 'C:\ALTER_COLUMN.SQL' to change column collations.
- Run 'C:\IDX_CREATE.SQL' to recreate indexes and constraints.
9. If your database contained schema elements that you manually dropped in
step 7,
recreate them.
*/
-- proc params and views in syscolumns still retain the old collation --
effect?
USE master
GO
IF OBJECT_ID ('sp_check_for_common_collation_change_p
roblems') IS NOT NULL
DROP PROC sp_check_for_common_collation_change_pro
blems
GO
CREATE PROCEDURE sp_check_for_common_collation_change_pro
blems AS
DECLARE @.bProblemsFound int -- BOOL. 0=false, 1=true
SET @.bProblemsFound = 0
SET NOCOUNT ON
IF (DB_ID() < 5 OR DB_NAME() IN ('master', 'tempdb', 'msdb', 'distribution',
'model'))
BEGIN
RAISERROR ('The ALTER TABLE approach to changing collation is not valid
for system
databases. Search for REBUILDM in Books Online or run this procedure from
a user database.',16,1)
RETURN -1
END
PRINT 'Checking database "' + DB_NAME()
+ '" for objects that could prevent a collation change...'
PRINT ''
-- Check for text/ntext columns in user tables and warn user.
IF EXISTS (SELECT *
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE c.xtype IN (99, 35) AND o.type = 'U' AND OBJECTPROPERTY (o.id,
'IsMSShipped')=0)
BEGIN
SET @.bProblemsFound = 1
PRINT '**** WARNING: TEXT OR NTEXT COLUMNS FOUND ****'
PRINT 'You have one or more text or ntext columns on user tables. The
collation '
PRINT 'of text and ntext columns cannot be modified in-place with ALTER
TABLE. '
PRINT 'These columns WILL BE SKIPPED by this script, and their collation
will '
PRINT 'not be changed. If you wish to change the collation of text/ntext
columns '
PRINT 'you must export the data, drop and add the column with the new
collation '
PRINT 'specified, and then import the data. text/ntext columns are listed
below: '
PRINT ''
SELECT LEFT (o.name, 60) AS [Table Name], LEFT (c.name, 60) AS [Colu
mn
Name]
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE c.xtype IN (99, 35) AND o.type = 'U' AND OBJECTPROPERTY (o.id,
'IsMSShipped')=0
PRINT ''
PRINT ''
END
-- Check for computed columns in user tables and warn user.
IF EXISTS (SELECT * FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE c.iscomputed = 1 AND o.type = 'U' AND OBJECTPROPERTY (o.id,
'IsMSShipped')=0)
BEGIN
SET @.bProblemsFound = 1
PRINT '**** WARNING: COMPUTED COLUMNS FOUND ****'
PRINT 'You have one or more computed columns defined on user tables. If
any '
PRINT 'of these columns reference varchar/char/nvarchar/ntext columns it
will '
PRINT 'prevent the referenced column''s collation from being changed. Any
of '
PRINT 'these columns that reference character columns must be dropped
before '
PRINT 'this script will run without errors. '
PRINT ''
PRINT 'This script does NOT preserve the computed columns'' definitions,
so be '
PRINT 'sure to script out the definition of these columns *before* running
the '
PRINT 'ALTER TABLE DROP COLUMN commands below. You will need to manually
add '
PRINT 'any columns that you dropped back to the tables after changing the
'
PRINT 'collation of the referenced column. This may modify the computed
columns'''
PRINT 'ordinal position in the table. If any indexes reference a computed
'
PRINT 'column you must manually drop that index before you can drop the
computed '
PRINT 'column. '
PRINT ''
SET NOCOUNT ON
SELECT
'-- ALTER TABLE [' + o.name + '] DROP COLUMN [' + c.name + ']' AS &#
91;Drop
Command],
'-- ' + t.text AS [Definition (May be Truncated)]
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
INNER JOIN syscomments t ON c.id = t.id AND c.colid = t.number
WHERE c.iscomputed = 1 AND o.type = 'U' AND OBJECTPROPERTY (o.id,
'IsMSShipped')=0
PRINT ''
PRINT ''
END
-- Check to see if database is involved in replication.
DECLARE @.replfound int
SET @.replfound = 0
-- merge publisher
IF DATABASEPROPERTY (DB_NAME(), 'IsMergePublished') = 1 SET @.replfound = 1
-- tran subscriber
IF DATABASEPROPERTY (DB_NAME(), 'IsSubscribed') = 1 SET @.replfound = 1
-- transactional publisher
IF OBJECT_ID ('sysarticles') IS NOT NULL SET @.replfound = 1
-- transactional subscriber
IF OBJECT_ID ('MSreplication_subscriptions') IS NOT NULL SET @.replfound = 1
IF @.replfound = 1
BEGIN
SET @.bProblemsFound = 1
PRINT '**** WARNING: DATABASE INVOLVED IN REPLICATION ****'
PRINT 'This database appears to be involved in replication. A replicated
column '
PRINT 'may not have its collation changed. You must drop replication
before '
PRINT 'running the script and redefine articles afterward. This script
WILL NOT '
PRINT 'script out publication properties for you. '
PRINT ''
PRINT ''
END
-- Check to see if database may have full text catalogs defined.
IF DATABASEPROPERTY (DB_NAME(), 'IsFulltextEnabled') = 1
BEGIN
SET @.bProblemsFound = 1
PRINT '**** WARNING: DATABASE FULLTEXT ENABLED ****'
PRINT 'Full text catalogs must be dropped before the collation of a column
used '
PRINT 'in a fulltext index may be changed. You can script out the
definition of a '
PRINT 'fulltext catalog in Enterprise Manager, and recreate it after the
columns'' '
PRINT 'collations have been changed. The catalog will require a full
population '
PRINT 'after it is recreated, and this may take a while. This script WILL
NOT '
PRINT 'script out your catalog properties for you.'
PRINT ''
PRINT ''
END
-- Check for schemabinding.
IF EXISTS (
SELECT *
FROM sysdepends d
INNER JOIN sysobjects o1 on d.id = o1.id
INNER JOIN sysobjects o2 on d.depid = o2.id
WHERE
(OBJECTPROPERTY (d.id, 'IsMSShipped')=0 OR OBJECTPROPERTY (d.depid,
'IsMSShipped')=0)
AND deptype > 0 -- an enforced dependency
-- Eliminate computed columns which show up as tables dependent on
themselves.
-- These are reported separately.
AND (o1.type <> 'U' OR o2.type <> 'U')
-- Eliminate CHECK constraints. These are handled separately.
AND o1.type <> 'C'
)
BEGIN
SET @.bProblemsFound = 1
PRINT '**** WARNING: POSSIBLE SCHEMABINDING DETECTED ****'
PRINT 'Views and User Defined Functions may be created WITH SCHEMABINDING,
which '
PRINT 'means that they must disallow any schema changes on the objects
they are '
PRINT 'dependent on. Before attempting to change a column''s collation
you must '
PRINT 'drop any views or UDFs that are schemabound to the column''s table.
This '
PRINT 'script WILL NOT script out these objects for you, so be sure to
script out '
PRINT 'their definition before dropping them. The objects that are schema
bound '
PRINT 'to another object are listed below: '
PRINT ''
SELECT DISTINCT
LEFT (OBJECT_NAME (d.id), 50) AS [Dependent Object],
CASE o1.type WHEN 'V' THEN 'VIEW' WHEN 'FN' THEN 'FUNCTION'
ELSE o1.type END AS [Dependent Obj Type],
LEFT (OBJECT_NAME (depid), 50) AS [Referenced Object],
CASE o2.type WHEN 'V' THEN 'VIEW' WHEN 'FN' THEN 'FUNCTION'
WHEN 'U' THEN 'USER TABLE'
ELSE o2.type END AS [Referenced Object Type]
FROM sysdepends d
INNER JOIN sysobjects o1 on d.id = o1.id
INNER JOIN sysobjects o2 on d.depid = o2.id
WHERE (OBJECTPROPERTY(d.id,'IsMSShipped')=0 OR
OBJECTPROPERTY(d.depid,'IsMSShipped')=0)
AND deptype > 0 -- an enforced dependency
-- Eliminate computed columns which show up as tables dependent on
themselves.
-- These are reported separately.
AND (o1.type <> 'U' OR o2.type <> 'U')
-- Eliminate CHECK constraints. These are handled separately.
AND o1.type <> 'C'
PRINT ''
PRINT ''
END
IF @.bProblemsFound = 1
BEGIN
PRINT 'One or more potential problems were identified. These issues must
be '
PRINT 'manually addressed before performing an in-place collation change.
'
PRINT ''
PRINT ''
END
ELSE
BEGIN
PRINT 'No blocking issues were identified that would prevent an in-place
collation '
PRINT 'change. '
PRINT ''
PRINT ''
END
-- Report on constraints and indexes that will need to be dropped.
IF EXISTS (SELECT *
FROM sysobjects
WHERE OBJECTPROPERTY (id, 'IsMSShipped') = 0
AND xtype IN ('F','D','C','UQ','PK'))
OR EXISTS (SELECT *
FROM sysindexes i
WHERE indid BETWEEN 1 AND 254 AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
AND NOT INDEXPROPERTY (id, name, 'IsAutoStatistics') = 1
AND NOT INDEXPROPERTY (id, name, 'IsHypothetical') = 1
-- not part of a UNIQUE or PK constraint
AND NOT EXISTS (SELECT * FROM sysobjects o WHERE o.xtype IN ('PK', 'UQ')
AND i.id = o.parent_obj AND o.name = i.name))
BEGIN
DECLARE @.objcount int
SELECT @.objcount = COUNT (*) FROM sysobjects
WHERE OBJECTPROPERTY (id, 'IsMSShipped') = 0 AND xtype IN
('F','D','C','UQ','PK')
PRINT '**** SUMMARY OF INDEXES & CONSTRAINTS ****'
PRINT 'Constraint count (UNIQUE, FK/PK, CHECK, DEFAULT): '
+ CONVERT (varchar(20), @.objcount)
SELECT @.objcount = COUNT (*) FROM sysindexes i
WHERE indid BETWEEN 1 AND 254 AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
AND NOT INDEXPROPERTY (id, name, 'IsAutoStatistics') = 1
AND NOT INDEXPROPERTY (id, name, 'IsHypothetical') = 1
AND NOT EXISTS (SELECT * FROM sysobjects o WHERE o.xtype IN ('PK', 'UQ')
AND i.id = o.parent_obj AND o.name = i.name)
PRINT 'Index count: ' + CONVERT (varchar(20), @.objcount)
PRINT 'Before a column''s collation may be modified, any indexes or
constraints on '
PRINT 'that column must be dropped. The following two stored procedures
may help by '
PRINT 'creating a script that will drop indexes/constraints, and another
script to '
PRINT 'recreate them after the collation is changed: '
PRINT ' sp_generate_index_constraint_script_for_
collation_change'
PRINT ' sp_generate_drop_cmds_for_collation_chan
ge '
PRINT ''
PRINT ''
END
GO
IF OBJECT_ID ('sp_generate_index_constraint_script_fo
r_collation_change') IS
NOT NULL
DROP PROC sp_generate_index_constraint_script_for_
collation_change
GO
CREATE PROC sp_generate_index_constraint_script_for_
collation_change
-- Output file to create (default:
'C:\<DBNAME>_IDX_CONSTRAINT_TRANSFER_SCRIPT.SQL')
@.scriptfilename sysname=NULL,
@.trustedconnection bit=1, -- Use a trusted connection to connect to the
server
@.username sysname='sa', -- Name of the user to connect as (defaults to
'sa')
@.password sysname=NULL, -- User's password
@.servername sysname=NULL -- Only used to handle incorrect @.@.SERVERNAME
AS
-- SQLDMO_SCRIPT_TYPE vars
DECLARE @.SQLDMOScript_Default int
DECLARE @.SQLDMOScript_Drops int
DECLARE @.SQLDMOScript_ObjectPermissions int
DECLARE @.SQLDMOScript_PrimaryObject int
DECLARE @.SQLDMOScript_ClusteredIndexes int
DECLARE @.SQLDMOScript_Triggers int
DECLARE @.SQLDMOScript_DatabasePermissions int
DECLARE @.SQLDMOScript_Permissions int
DECLARE @.SQLDMOScript_ToFileOnly int
DECLARE @.SQLDMOScript_Bindings int
DECLARE @.SQLDMOScript_AppendToFile int
DECLARE @.SQLDMOScript_NoDRI int
DECLARE @.SQLDMOScript_UDDTsToBaseType int
DECLARE @.SQLDMOScript_IncludeIfNotExists int
DECLARE @.SQLDMOScript_NonClusteredIndexes int
DECLARE @.SQLDMOScript_Indexes int
DECLARE @.SQLDMOScript_Aliases int
DECLARE @.SQLDMOScript_NoCommandTerm int
DECLARE @.SQLDMOScript_DRIIndexes int
DECLARE @.SQLDMOScript_IncludeHeaders int
DECLARE @.SQLDMOScript_OwnerQualify int
DECLARE @.SQLDMOScript_TimestampToBinary int
DECLARE @.SQLDMOScript_SortedData int
DECLARE @.SQLDMOScript_SortedDataReorg int
DECLARE @.SQLDMOScript_TransferDefault int
DECLARE @.SQLDMOScript_DRI_NonClustered int
DECLARE @.SQLDMOScript_DRI_Clustered int
DECLARE @.SQLDMOScript_DRI_Checks int
DECLARE @.SQLDMOScript_DRI_Defaults int
DECLARE @.SQLDMOScript_DRI_UniqueKeys int
DECLARE @.SQLDMOScript_DRI_ForeignKeys int
DECLARE @.SQLDMOScript_DRI_PrimaryKey int
DECLARE @.SQLDMOScript_DRI_AllKeys int
DECLARE @.SQLDMOScript_DRI_AllConstraints int
DECLARE @.SQLDMOScript_DRI_All int
DECLARE @.SQLDMOScript_DRIWithNoCheck int
DECLARE @.SQLDMOScript_NoIdentity int
DECLARE @.SQLDMOScript_UseQuotedIdentifiers int
-- SQLDMO_SCRIPT2_TYPE vars
DECLARE @.SQLDMOScript2_Default int
DECLARE @.SQLDMOScript2_AnsiPadding int
DECLARE @.SQLDMOScript2_AnsiFile int
DECLARE @.SQLDMOScript2_UnicodeFile int
DECLARE @.SQLDMOScript2_NonStop int
DECLARE @.SQLDMOScript2_NoFG int
DECLARE @.SQLDMOScript2_MarkTriggers int
DECLARE @.SQLDMOScript2_OnlyUserTriggers int
DECLARE @.SQLDMOScript2_EncryptPWD int
DECLARE @.SQLDMOScript2_SeparateXPs int
-- SQLDMO_SCRIPT_TYPE values
SET @.SQLDMOScript_Default = 4
SET @.SQLDMOScript_Drops = 1
SET @.SQLDMOScript_ObjectPermissions = 2
SET @.SQLDMOScript_PrimaryObject = 4
SET @.SQLDMOScript_ClusteredIndexes = 8
SET @.SQLDMOScript_Triggers = 16
SET @.SQLDMOScript_DatabasePermissions = 32
SET @.SQLDMOScript_Permissions = 34
SET @.SQLDMOScript_ToFileOnly = 64
SET @.SQLDMOScript_Bindings = 128
SET @.SQLDMOScript_AppendToFile = 256
SET @.SQLDMOScript_NoDRI = 512
SET @.SQLDMOScript_UDDTsToBaseType = 1024
SET @.SQLDMOScript_IncludeIfNotExists = 4096
SET @.SQLDMOScript_NonClusteredIndexes = 8192
SET @.SQLDMOScript_Indexes = 73736
SET @.SQLDMOScript_Aliases = 16384
SET @.SQLDMOScript_NoCommandTerm = 32768
SET @.SQLDMOScript_DRIIndexes = 65536
SET @.SQLDMOScript_IncludeHeaders = 131072
SET @.SQLDMOScript_OwnerQualify = 262144
SET @.SQLDMOScript_TimestampToBinary = 524288
SET @.SQLDMOScript_SortedData = 1048576
SET @.SQLDMOScript_SortedDataReorg = 2097152
SET @.SQLDMOScript_TransferDefault = 422143
SET @.SQLDMOScript_DRI_NonClustered = 4194304
SET @.SQLDMOScript_DRI_Clustered = 8388608
SET @.SQLDMOScript_DRI_Checks = 16777216
SET @.SQLDMOScript_DRI_Defaults = 33554432
SET @.SQLDMOScript_DRI_UniqueKeys = 67108864
SET @.SQLDMOScript_DRI_ForeignKeys = 134217728
SET @.SQLDMOScript_DRI_PrimaryKey = 268435456
SET @.SQLDMOScript_DRI_AllKeys = 469762048
SET @.SQLDMOScript_DRI_AllConstraints = 520093696
SET @.SQLDMOScript_DRI_All = 532676608
SET @.SQLDMOScript_DRIWithNoCheck = 536870912
SET @.SQLDMOScript_NoIdentity = 1073741824
SET @.SQLDMOScript_UseQuotedIdentifiers = -1
-- SQLDMO_SCRIPT2_TYPE values
SET @.SQLDMOScript2_Default = 0
SET @.SQLDMOScript2_AnsiPadding = 1
SET @.SQLDMOScript2_AnsiFile = 2
SET @.SQLDMOScript2_UnicodeFile = 4
SET @.SQLDMOScript2_NonStop = 8
SET @.SQLDMOScript2_NoFG = 16
SET @.SQLDMOScript2_MarkTriggers = 32
SET @.SQLDMOScript2_OnlyUserTriggers = 64
SET @.SQLDMOScript2_EncryptPWD = 128
SET @.SQLDMOScript2_SeparateXPs = 256
DECLARE @.dbname sysname,
@.srvobject int, -- SQL Server object
@.object int, -- Work variable for accessing COM objects
@.hr int, -- Contains HRESULT returned by COM
@.tfobject int, -- Stores pointer to Transfer object
@.scriptoptions int, -- Options bitmask for Transfer
@.res int
SET @.res=0
IF (DB_ID() < 5 OR DB_NAME() IN
('master','tempdb','msdb','distribution'
,'model'))
BEGIN
RAISERROR ('The ALTER TABLE approach to changing collation is not valid
for system
databases. Search for REBUILDM in Books Online or run this procedure from
a user database.',16,1)
RETURN -1
END
SET @.dbname=DB_NAME()
IF (@.scriptfilename IS NULL)
SET @.scriptfilename = 'C:' + DB_NAME() + '_INDEX_CONSTRAINT_SCRIPT.SQL'
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('-- Starting sp_generate_index_constraint_script_for_
collation_
change',
0, 1) WITH NOWAIT
RAISERROR ('-- Current (source) Database: %s', 0, 1, @.dbname) WITH
NOWAIT
RAISERROR ('', 0, 1) WITH NOWAIT
-- @.servername is NULL by default
IF @.servername IS NULL
SET @.servername = @.@.SERVERNAME
ELSE
RAISERROR ('-- Override of local @.@.SERVERNAME with @.servername (sp
param).
-- Using local server name: %s', 0, 1, @.servername) WITH NOWAIT
RAISERROR ('-- Script w/cmds to recreate indexes/constraints will be
saved to: %s',
0, 1, @.scriptfilename) WITH NOWAIT
RAISERROR ('-- This may run for a while if there are many tables in the
database.',
0, 1) WITH NOWAIT
RAISERROR ('', 0, 1) WITH NOWAIT
-- Create a SQLServer object
EXEC @.hr=sp_OACreate 'SQLDMO.SQLServer', @.srvobject OUTPUT
IF (@.hr <> 0) BEGIN
RAISERROR ('Error creating SQLDMO.SQLServer object (%d).', 15, 1, @.hr)
EXEC sp_displayoaerrorinfo @.srvobject, @.hr
RETURN -1
END
-- Connect to the server
IF (@.trustedconnection=1) BEGIN
EXEC @.hr = sp_OASetProperty @.srvobject, 'LoginSecure', 1
IF (@.hr <> 0) BEGIN
RAISERROR ('Error setting SQLServer.LoginSecure property (%d).', 15,
1, @.hr)
EXEC sp_displayoaerrorinfo @.srvobject, @.hr
GOTO ServerError
END
EXEC @.hr = sp_OAMethod @.srvobject, 'Connect', NULL, @.servername
END
ELSE BEGIN
IF (@.password IS NOT NULL)
BEGIN
EXEC @.hr = sp_OAMethod @.srvobject, 'Connect', NULL, @.servername,
@.username, @.password
END
ELSE BEGIN
EXEC @.hr = sp_OAMethod @.srvobject, 'Connect', NULL, @.servername,
@.username
END
END
IF (@.hr <> 0) BEGIN
PRINT 'Failed to connect to server ''' + @.servername
+ '''. If this is not the name of the local SQL Server instance, '
PRINT 'pass in the optional @.servername parameter to provide the correct
local '
+ 'server name as follows: '
PRINT ''
PRINT ' EXEC
sp_generate_index_constraint_script_for_
collation_change '
+ '@.scriptfilename=''...'', '''
+ DB_NAME() + ''', @.servername = ''<correct_SQL_Server_name>'' '
PRINT ''
PRINT 'Or, use sp_dropserver and sp_addserver w/the ''LOCAL'' parameter
to '
+ 'correct the local server name.'
PRINT ''
RAISERROR ('Error calling SQLServer.Connect method (%d).', 15, 1, @.hr)
EXEC sp_displayoaerrorinfo @.srvobject, @.hr
GOTO ServerError
END
-- Create a Transfer object
EXEC @.hr=sp_OACreate 'SQLDMO.Transfer', @.tfobject OUTPUT
IF (@.hr <> 0) BEGIN
RAISERROR ('Error creating SQLServer.Transfer object (%d).', 15, 1, @.hr)
EXEC sp_displayoaerrorinfo @.tfobject, @.hr
GOTO FreeSrv
END
-- Set Transfer's CopyData property
EXEC @.hr = sp_OASetProperty @.tfobject, 'CopyData', 0
IF (@.hr <> 0) BEGIN
RAISERROR ('Error setting Transfer.CopyData property (%d).', 15, 1, @.hr)
EXEC sp_displayoaerrorinfo @.tfobject, @.hr
GOTO FreeAll
END
-- Tell Transfer to copy the schema
EXEC @.hr = sp_OASetProperty @.tfobject, 'CopySchema', 1
IF (@.hr <> 0) BEGIN
RAISERROR ('Error setting Transfer.CopySchema property (%d).', 15, 1,
@.hr)
EXEC sp_displayoaerrorinfo @.tfobject, @.hr
GOTO FreeAll
END
-- Tell Transfer not to copy all objects -- we only want constraints,
indexes, etc.
EXEC @.hr = sp_OASetProperty @.tfobject, 'CopyAllObjects', 0
IF (@.hr <> 0) BEGIN
RAISERROR ('Error setting Transfer.CopyAllObjects property (%d).', 15,
1, @.hr)
EXEC sp_displayoaerrorinfo @.tfobject, @.hr
GOTO FreeAll
END
-- Must tell Transfer to consider all tables even though we aren't scripting
the base
-- table itself.
EXEC @.hr = sp_OASetProperty @.tfobject, 'CopyAllTables', 1
IF (@.hr <> 0) BEGIN
RAISERROR ('Error setting Transfer.IncludeGroups property (%d).', 15, 1,
@.hr)
EXEC sp_displayoaerrorinfo @.tfobject, @.hr
GOTO FreeAll
END
-- Include object dependencies or script won't create parent table PKs
before trying
-- to create child FKs.
EXEC @.hr = sp_OASetProperty @.tfobject, 'IncludeDependencies', 1
IF (@.hr <> 0) BEGIN
RAISERROR ('Error setting Transfer.IncludeDependencies property (%d).',
15, 1, @.hr)
EXEC sp_displayoaerrorinfo @.tfobject, @.hr
GOTO FreeAll
END
IF (@.scriptoptions IS NULL)
BEGIN
SET @.scriptoptions=@.SQLDMOScript_OwnerQualif
y | @.SQLDMOScript_Default
| @.SQLDMOScript_Bindings | @.SQLDMOScript_DRI_All |
@.SQLDMOScript_Indexes
-- Don't script table creates, just indexes and constraints.
SET @.scriptoptions=@.scriptoptions ^ @.SQLDMOScript_PrimaryObject
END
-- Set Transfer's ScriptType property
EXEC @.hr = sp_OASetProperty @.tfobject, 'ScriptType', @.scriptoptions
IF (@.hr <> 0) BEGIN
RAISERROR ('Error setting Transfer.ScriptType property (%d).', 15, 1,
@.hr)
EXEC sp_displayoaerrorinfo @.tfobject, @.hr
GOTO FreeAll
END
-- Get a pointer to the SQLServer object's Databases collection
EXEC @.hr = sp_OAGetProperty @.srvobject, 'Databases', @.object OUT
IF @.hr <> 0 BEGIN
RAISERROR ('Error getting SQLServer.Databases property (%d).', 15, 1,
@.hr)
EXEC sp_displayoaerrorinfo @.srvobject, @.hr
GOTO FreeAll
END
-- Get a pointer from the Databases collection for the current database (the
one
-- we are going to script indexes for).
EXEC @.hr = sp_OAMethod @.object, 'Item', @.object OUT, @.dbname
IF @.hr <> 0 BEGIN
RAISERROR ('Error getting Databases.Item property (%d).', 15, 1, @.hr)
EXEC sp_displayoaerrorinfo @.object, @.hr
GOTO FreeAll
END
-- Call the Database object's Transfer method to create a script file with
the object
-- definitions.
EXEC @.hr = sp_OAMethod @.object, 'ScriptTransfer', NULL, @.tfobject, 2,
@.scriptfilename
IF @.hr <> 0 BEGIN
RAISERROR ('Error calling Database.ScriptTransfer property (%d).', 15,
1, @.hr)
EXEC sp_displayoaerrorinfo @.object, @.hr
GOTO FreeAll
END
PRINT ''
PRINT 'SQL script with commands to recreate indexes and constraints saved
to: '
PRINT ' ' + @.scriptfilename
PRINT ''
GOTO FreeAll
ServerError:
SET @.res=-1
RAISERROR ('Error transferring schema.', 15, 1)
FreeAll:
EXEC sp_OADestroy @.tfobject
FreeSrv:
EXEC sp_OADestroy @.srvobject
RETURN @.res
GO
IF OBJECT_ID ('sp_generate_drop_cmds_for_collation_ch
ange') IS NOT NULL
DROP PROC sp_generate_drop_cmds_for_collation_chan
ge
GO
CREATE PROCEDURE sp_generate_drop_cmds_for_collation_chan
ge AS
/*
This generates DROP commands for all indexes, constraints, and user-created
statistics in
the current database. In theory only a subset of these items (those that
referenced a
varchar/char/nchar/nvarchar column either directly or indirectly) would need
to be dropped.
However, there are a number of problems with doing a more selective drop:
- A FK may be defined on a non-"string" column, but may reference a string
PK column.
In this case the FK constraint must be dropped even though it is not on a
column that
will be targeted for a collation change (because the PK must be dropped,
and it can't
be dropped while a FK references it). A similar potential problem exists
for CHECK
constraints. Would have to untangle these sort of constraint
dependencies to reliably
identify exactly those constraints that had to be dropped.
- In the case of a clustered index or PK on a string column, you would want
to drop all
nonclustered indexes (even those on non-string columns) before dropping
the clustered
index to avoid two unnecessary rebuilds of each NC index when the
clustered index was
first dropped and then recreated. However, when the clustered index was
on a non-
string column you would ideally do a more selective drop of the NC
indexes.
- There is no way to tell DMO to only script out just the relevant
constraints or
indexes.
For simplicity's sake, this script is designed to drop and all constraints
and indexes.
The complementary proc
sp_generate_index_constraint_script_for_
collation_change generates
a script that will recreate all indexes and constraints.
*/
IF (DB_ID() < 5 OR DB_NAME() IN ('master', 'tempdb', 'msdb', 'distribution',
'model'))
BEGIN
RAISERROR ('The ALTER TABLE approach to changing collation is not valid
for system
databases. Search for REBUILDM in Books Online or run this procedure from
a user database.',16,1)
RETURN -1
END
PRINT '/**** Generating DROP commands for constraints, statistics, and
indexes ****/'
PRINT '/* '
PRINT 'Save this output to a .SQL script and run it to drop constraints and
indexes '
PRINT 'that might prevent a column''s collation from being changed. Back up
the '
PRINT 'database before running this, and be sure that you have scripted out
the index, '
PRINT 'constraint, and statistics definitions so you can recreate these
objects after '
PRINT 'changing the collation of the underlying columns.'
PRINT '*/'
PRINT ''
DECLARE @.sql nvarchar (4000)
DECLARE @.obj_type varchar (30)
DECLARE @.itemcount int
DECLARE @.currentitem int
SET NOCOUNT ON
-- 1. Drop UNIQUE, CHECK, DEFAULT, and FOREIGN KEY constraints (sysobjects)
-- 2. Drop nonclustered indexes and statistics (sysindexes)
-- 3. Drop PRIMARY KEY constraints (sysobjects)
-- 4. Drop non-PK clustered indexes (sysindexes)
IF OBJECT_ID ('tempdb..#idx_const_drop') IS NOT NULL DROP TABLE
#idx_const_drop
SELECT *
INTO #idx_const_drop
FROM (
-- non-PK constraints
SELECT 1 AS obj_type, CONVERT (nvarchar (4000), 'ALTER TABLE ['
+ OBJECT_NAME (parent_obj) + '] DROP CONSTRAINT [' + name + ']') AS [
;sql]
FROM sysobjects
WHERE OBJECTPROPERTY (id, 'IsMSShipped')=0 AND xtype IN ('F','D','C','UQ')
UNION
-- nonclustered indexes and statistics
SELECT 2, 'DROP '
+ CASE INDEXPROPERTY (id, name, 'IsStatistics')
WHEN 1 THEN 'STATISTICS ' WHEN 0 THEN 'INDEX ' END
+ '[' + OBJECT_NAME (id) + '].[' + name + ']'
FROM sysindexes i
WHERE indid BETWEEN 2 AND 254 AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
AND NOT INDEXPROPERTY (id, name, 'IsAutoStatistics') = 1
AND NOT INDEXPROPERTY (id, name, 'IsHypothetical') = 1
-- Not part of a constraint
AND NOT EXISTS (SELECT * FROM sysobjects o
WHERE o.xtype IN ('PK', 'UQ') AND i.id = o.parent_obj AND o.name
= i.name)
UNION
-- primary key constraints
SELECT 3, 'ALTER TABLE [' + OBJECT_NAME (parent_obj) + '] DROP CONSTRAIN
T
['
+ name + ']'
FROM sysobjects
WHERE OBJECTPROPERTY (id, 'IsMSShipped') = 0 AND xtype IN ('PK')
UNION
-- clustered indexes
SELECT 4, 'DROP INDEX [' + OBJECT_NAME (id) + '].[' + name + ']'
FROM sysindexes i
WHERE indid = 1 AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
AND INDEXPROPERTY (id, name, 'IsAutoStatistics') = 0
AND INDEXPROPERTY (id, name, 'IsHypothetical') = 0
-- Not part of a constraint
AND NOT EXISTS (SELECT * FROM sysobjects o
WHERE o.xtype IN ('PK', 'UQ') AND i.id = o.parent_obj AND o.name
= i.name)
) AS t
SET @.itemcount = @.@.ROWCOUNT
SET @.currentitem = 1
PRINT 'USE ' + DB_NAME()
PRINT 'GO'
DECLARE c CURSOR LOCAL READ_ONLY FOR
SELECT obj_type, [sql] FROM #idx_const_drop ORDER BY obj_type
OPEN c
FETCH NEXT FROM c INTO @.obj_type, @.sql
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
PRINT 'RAISERROR (''Executing DROP cmd ' + CONVERT (varchar (20),
@.currentitem)
+ ' of ' + CONVERT (varchar (20), @.itemcount) + ': ' + @.sql
+ ''', 0, 1) WITH NOWAIT'
PRINT @.sql
PRINT 'GO'
SET @.currentitem = @.currentitem + 1
FETCH NEXT FROM c INTO @.obj_type, @.sql
END
CLOSE c
DEALLOCATE c
PRINT ''
PRINT ''
GO
IF OBJECT_ID ('sp_generate_alter_cmds_for_collation_c
hange') IS NOT NULL
DROP PROC sp_generate_alter_cmds_for_collation_cha
nge
GO
CREATE PROCEDURE sp_generate_alter_cmds_for_collation_cha
nge
@.newcollationname sysname AS
DECLARE @.sql nvarchar (4000)
DECLARE @.objname sysname
DECLARE @.columnname sysname
DECLARE @.type sysname
DECLARE @.size int
DECLARE @.isnullable int -- BOOL. 0=false, 1=true
DECLARE @.itemcount int
DECLARE @.currentitem int
IF (DB_ID() < 5 OR DB_NAME() IN ('master', 'tempdb', 'msdb', 'distribution',
'model'))
BEGIN
RAISERROR ('The ALTER TABLE approach to changing collation is not valid
for system
databases. Search for REBUILDM in Books Online or run this procedure from
a user database.',16,1)
RETURN -1
END
IF COLLATIONPROPERTY (@.newcollationname, 'CodePage') IS NULL
BEGIN
RAISERROR ('%s is not a valid collation. See the "SQL Collation Name"
and
"Windows Collation Name" articles in the SQL Server Books Online.',
16, 1, @.newcollationname)
RETURN -1
END
SET NOCOUNT ON
PRINT '/**** Generating ALTER TABLE ALTER COLUMN commands for string columns
****/'
PRINT '/*'
PRINT 'Save this output as a .SQL script and run it to change the collation
of '
PRINT 'char, varchar, nchar, and nvarchar columns to ' + @.newcollationname +
'.'
PRINT '*/'
PRINT ''
-- Change default collation of the database to the system default
-- (same as tempdb). Note that this does *not* change the collation
-- of existing tables or columns.
PRINT ''
PRINT 'USE ' + DB_NAME()
PRINT 'GO'
PRINT 'RAISERROR (''Changing database default collation (requires exclusive
access)...'',
0, 1) WITH NOWAIT'
SET @.sql = 'ALTER DATABASE [' + DB_NAME() + '] COLLATE '
+ @.newcollationname
PRINT @.sql
PRINT 'GO'
PRINT ''
PRINT ''
-- Change collation of each (var)char/n(var)char/(n)text column to match
-- the database collation.
-- Open a cursor on a list of all char/varchar/nchar/nvarchar columns
-- on user tables.
IF OBJECT_ID ('tempdb..#col_list') IS NOT NULL DROP TABLE #col_list
SELECT object_name (o.id) AS objname, c.name AS colname,
t.name AS [type], c.prec AS [size], isnullable
INTO #col_list
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
INNER JOIN systypes t ON c.xtype = t.xtype
WHERE o.type = 'U' AND t.name IN ('varchar', 'nvarchar', 'char', 'nchar')
AND OBJECTPROPERTY (o.id, 'IsMSShipped') = 0
SET @.itemcount = @.@.ROWCOUNT
SET @.currentitem = 1
DECLARE col INSENSITIVE CURSOR FOR
SELECT objname, colname, [type], [size], isnullable
FROM #col_list
ORDER BY objname, colname
FOR READ ONLY
OPEN col
FETCH NEXT FROM col INTO @.objname, @.columnname, @.type, @.size,
@.isnullable
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
-- Generate ALTER TABLE ... ALTER COLUMN stmt.
SET @.sql = 'ALTER TABLE [' + @.objname + '] ' + 'ALTER COLUMN ['
+ @.columnname + '] ' + @.type + ' '
-- Add column length if type is not text/ntext.
IF @.type IN ('varchar', 'nvarchar', 'char', 'nchar')
SET @.sql = @.sql + '(' + CAST (@.size AS varchar) + ') '
SET @.sql = @.sql + 'COLLATE ' + @.newcollationname + ' '
IF @.isnullable = 1
SET @.sql = @.sql + 'NULL '
ELSE
SET @.sql = @.sql + 'NOT NULL '
PRINT 'RAISERROR (''Executing ALTER cmd ' + CONVERT (varchar (20),
@.currentitem)
+ ' of ' + CONVERT (varchar (20), @.itemcount) + ': ' + @.sql
+ ''', 0, 1) WITH NOWAIT'
PRINT @.sql
PRINT 'GO'
SET @.currentitem = @.currentitem + 1
FETCH NEXT FROM col INTO @.objname, @.columnname, @.type, @.size,
@.isnullable
END
CLOSE col
DEALLOCATE col
PRINT 'PRINT ''
PRINT 'PRINT ''Base table columns that have not been changed to the new
collation:'''
PRINT 'PRINT ''
PRINT 'SELECT '
PRINT ' RIGHT (c.TABLE_SCHEMA + ''.'' + c.TABLE_NAME + ''.'' + COLUMN_NAME,
60) AS COLUMN_NAME, '
PRINT ' LEFT (COLLATION_NAME, 40) AS COLLATION_NAME, '
PRINT ' LEFT (DATA_TYPE, 9) AS DATA_TYPE '
PRINT 'FROM INFORMATION_SCHEMA.COLUMNS c '
PRINT 'INNER JOIN INFORMATION_SCHEMA.TABLES t '
PRINT ' ON c.TABLE_CATALOG = t.TABLE_CATALOG AND c.TABLE_SCHEMA =
t.TABLE_SCHEMA '
PRINT ' AND c.TABLE_NAME = t.TABLE_NAME AND t.TABLE_TYPE = ''BASE TABLE''
'
PRINT 'WHERE DATA_TYPE IN
(''char'',''nchar'',''varchar'',''nvarch
ar'',''text'',''ntext'') '
PRINT ' AND OBJECTPROPERTY (OBJECT_ID (t.TABLE_SCHEMA + ''.'' +
t.TABLE_NAME), ''IsMSShipped'')=0 '
PRINT ' AND COLLATION_NAME != ''' + @.newcollationname + ''''
PRINT 'ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME '
PRINT 'GO'
GO
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:DCC92136-EE54-41F7-83ED-A07AC7BADDCC@.microsoft.com...
> Hi,
> I need an urgent help.
> I have a database backup file with 30,000 tables, which has a particular
collation SQL_Latin1_General_CP1_CI_AS.
> Now I need to restore this database on a different server (srv2), but with
different collation Latin1_General_BIN.
> So I created a database on srv2 with Latin1_General_BIN collation. But
When I tried to restore it from the backup file, am getting all the tables
with SQL_Latin1_General_CP1_CI_AS collation. I tried to change the database
collation on srv2 to Latin1_General_BIN , but still the collation is the old
one.
> Can anybody provide me the solution to this?
> Thanks
> GYK|||Thanks Uri and Jacco....
Jacco, I tried your script... but it doesn't work when indexes are created
on the columns... it gives error of identity... So I was thinking of genera
ting the database script from the old database, into a text file. then find
and replace the collation a
nd then run this new script to create the new database with new collation. A
m trying that out, lets see if this works..
thanks
GYK|||Can you post the exact error message you get. As I stated in my previous
post there can be quite a few issues with this script, although most of them
can be resolved reasonably easily.
Jacco Schalkwijk
SQL Server MVP
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:0D16CC56-47B9-4BF0-9FA8-C4FD99F40B29@.microsoft.com...
> Thanks Uri and Jacco....
> Jacco, I tried your script... but it doesn't work when indexes are
created on the columns... it gives error of identity... So I was thinking
of generating the database script from the old database, into a text file.
then find and replace the collation and then run this new script to create
the new database with new collation. Am trying that out, lets see if this
works..
> thanks
> GYK