Tags: alter, char, collation, database, field, fields, programming, sql, table, varchar, xyz

Change field collation

On Programmer » SQL

47,131 words with 4 Comments; publish: Thu, 29 May 2008 04:18:00 GMT; (200140.63, « »)

Hi,

is there a way to change the collation of *all* char/varchar fields in

every table in a given database?

I can do it like 'alter table XYZ alter column Afield char(50)

collate Latin1_General_CI_AS' for every field but that's a hell

of a job...

Groeten,

Jeroen

All Comments

Leave a comment...

  • 4 Comments
    • I don't think there is an easy way around this.

      You might try to generate all the "alter table, alter column" statements

      dynamically by using the INFORMATION_SCHEMA.COLUMNS view. After you have

      generated all the statements then execute them.

      Or you might migrate your database to a new database changing the collation

      as you migrate, then copy the new database back over the top of your old

      database.

      --

      ----

      ----

      --

      Need SQL Server Examples check out my website at

      http://www.geocities.com/sqlserverexamples

      "Jeroen" <PingMe.sql.todaysummary.com.NG.com> wrote in message

      news:11w43ejb68d6z$.1kgha83p7loz4.dlg.sql.todaysummary.com.40tude.net...

      > Hi,

      > is there a way to change the collation of *all* char/varchar fields in

      > every table in a given database?

      > I can do it like 'alter table XYZ alter column Afield char(50)

      > collate Latin1_General_CI_AS' for every field but that's a hell

      > of a job...

      > Groeten,

      > Jeroen

      #1; Thu, 29 May 2008 04:20:00 GMT
    • You will have to loop through all the columns of all tables and run an ALTER

      TABLE command for each column.

      You can find an example of looping through all columns of all tables, on my

      site, at: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

      --

      HTH,

      Vyas, MVP (SQL Server)

      http://vyaskn.tripod.com/

      Is .NET important for a database professional?

      http://vyaskn.tripod.com/poll.htm

      "Jeroen" <PingMe.sql.todaysummary.com.NG.com> wrote in message

      news:11w43ejb68d6z$.1kgha83p7loz4.dlg.sql.todaysummary.com.40tude.net...

      Hi,

      is there a way to change the collation of *all* char/varchar fields in

      every table in a given database?

      I can do it like 'alter table XYZ alter column Afield char(50)

      collate Latin1_General_CI_AS' for every field but that's a hell

      of a job...

      Groeten,

      Jeroen

      #2; Thu, 29 May 2008 04:21:00 GMT
    • And in addition to the previous posts, you also have to drop and recreate

      all indexes, constraints and statistics on the columns before you can alter

      a column.

      See this script that Bart Duncan from Microsoft posted a while ago. You

      first have to create sp_displayoaerrorinfo (and child proc sp_hexadecimal),

      which you can find in the "OLE Automation Return Codes and Error

      Information" Books Online topic.

      There are quite a lot of caveats and you use this script at your own risk.

      Read the comments carefully before using it.

      --

      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_problems

      - 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_change

      - outputs ALTER TABLE DROP CONSTRAINT and DROP INDEX commands

      4. sp_generate_alter_cmds_for_collation_change

      - 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_problems

      sp_generate_index_constraint_script_for_collation_change

      sp_generate_drop_cmds_for_collation_change

      sp_generate_alter_cmds_for_collation_change

      2. Change to the database with the collation that is to be modified, and run

      the proc

      sp_check_for_common_collation_change_problems. 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_problems

      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_problems,

      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

      .sql.todaysummary.com.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_change

      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 .sql.todaysummary.com.newcollationname parameter.

      Save

      the output as a SQL script, for example, 'C:\ALTER_COLUMN.SQL'.

      USE Northwind

      EXEC sp_generate_alter_cmds_for_collation_change

      .sql.todaysummary.com.newcollationname = 'SQL_Latin1_General_CP437_CI_AS'

      7. Address any problems identified by

      sp_check_for_common_collation_change_problems.

      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_problems') IS NOT NULL

      DROP PROC sp_check_for_common_collation_change_problems

      GO

      CREATE PROCEDURE sp_check_for_common_collation_change_problems AS

      DECLARE .sql.todaysummary.com.bProblemsFound int -- BOOL. 0=false, 1=true

      SET .sql.todaysummary.com.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 .sql.todaysummary.com.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 [Column

      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 .sql.todaysummary.com.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 [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 .sql.todaysummary.com.replfound int

      SET .sql.todaysummary.com.replfound = 0

      -- merge publisher

      IF DATABASEPROPERTY (DB_NAME(), 'IsMergePublished') = 1 SET .sql.todaysummary.com.replfound = 1

      -- tran subscriber

      IF DATABASEPROPERTY (DB_NAME(), 'IsSubscribed') = 1 SET .sql.todaysummary.com.replfound = 1

      -- transactional publisher

      IF OBJECT_ID ('sysarticles') IS NOT NULL SET .sql.todaysummary.com.replfound = 1

      -- transactional subscriber

      IF OBJECT_ID ('MSreplication_subscriptions') IS NOT NULL SET .sql.todaysummary.com.replfound = 1

      IF .sql.todaysummary.com.replfound = 1

      BEGIN

      SET .sql.todaysummary.com.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 .sql.todaysummary.com.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 .sql.todaysummary.com.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 .sql.todaysummary.com.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 .sql.todaysummary.com.objcount int

      SELECT .sql.todaysummary.com.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), .sql.todaysummary.com.objcount)

      SELECT .sql.todaysummary.com.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), .sql.todaysummary.com.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_change '

      PRINT ''

      PRINT ''

      END

      GO

      IF OBJECT_ID ('sp_generate_index_constraint_script_for_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')

      .sql.todaysummary.com.scriptfilename sysname=NULL,

      .sql.todaysummary.com.trustedconnection bit=1, -- Use a trusted connection to connect to the

      server

      .sql.todaysummary.com.username sysname='sa', -- Name of the user to connect as (defaults to

      'sa')

      .sql.todaysummary.com.password sysname=NULL, -- User's password

      .sql.todaysummary.com.servername sysname=NULL -- Only used to handle incorrect .sql.todaysummary.com..sql.todaysummary.com.SERVERNAME

      AS

      -- SQLDMO_SCRIPT_TYPE vars

      DECLARE .sql.todaysummary.com.SQLDMOScript_Default int

      DECLARE .sql.todaysummary.com.SQLDMOScript_Drops int

      DECLARE .sql.todaysummary.com.SQLDMOScript_ObjectPermissions int

      DECLARE .sql.todaysummary.com.SQLDMOScript_PrimaryObject int

      DECLARE .sql.todaysummary.com.SQLDMOScript_ClusteredIndexes int

      DECLARE .sql.todaysummary.com.SQLDMOScript_Triggers int

      DECLARE .sql.todaysummary.com.SQLDMOScript_DatabasePermissions int

      DECLARE .sql.todaysummary.com.SQLDMOScript_Permissions int

      DECLARE .sql.todaysummary.com.SQLDMOScript_ToFileOnly int

      DECLARE .sql.todaysummary.com.SQLDMOScript_Bindings int

      DECLARE .sql.todaysummary.com.SQLDMOScript_AppendToFile int

      DECLARE .sql.todaysummary.com.SQLDMOScript_NoDRI int

      DECLARE .sql.todaysummary.com.SQLDMOScript_UDDTsToBaseType int

      DECLARE .sql.todaysummary.com.SQLDMOScript_IncludeIfNotExists int

      DECLARE .sql.todaysummary.com.SQLDMOScript_NonClusteredIndexes int

      DECLARE .sql.todaysummary.com.SQLDMOScript_Indexes int

      DECLARE .sql.todaysummary.com.SQLDMOScript_Aliases int

      DECLARE .sql.todaysummary.com.SQLDMOScript_NoCommandTerm int

      DECLARE .sql.todaysummary.com.SQLDMOScript_DRIIndexes int

      DECLARE .sql.todaysummary.com.SQLDMOScript_IncludeHeaders int

      DECLARE .sql.todaysummary.com.SQLDMOScript_OwnerQualify int

      DECLARE .sql.todaysummary.com.SQLDMOScript_TimestampToBinary int

      DECLARE .sql.todaysummary.com.SQLDMOScript_SortedData int

      DECLARE .sql.todaysummary.com.SQLDMOScript_SortedDataReorg int

      DECLARE .sql.todaysummary.com.SQLDMOScript_TransferDefault int

      DECLARE .sql.todaysummary.com.SQLDMOScript_DRI_NonClustered int

      DECLARE .sql.todaysummary.com.SQLDMOScript_DRI_Clustered int

      DECLARE .sql.todaysummary.com.SQLDMOScript_DRI_Checks int

      DECLARE .sql.todaysummary.com.SQLDMOScript_DRI_Defaults int

      DECLARE .sql.todaysummary.com.SQLDMOScript_DRI_UniqueKeys int

      DECLARE .sql.todaysummary.com.SQLDMOScript_DRI_ForeignKeys int

      DECLARE .sql.todaysummary.com.SQLDMOScript_DRI_PrimaryKey int

      DECLARE .sql.todaysummary.com.SQLDMOScript_DRI_AllKeys int

      DECLARE .sql.todaysummary.com.SQLDMOScript_DRI_AllConstraints int

      DECLARE .sql.todaysummary.com.SQLDMOScript_DRI_All int

      DECLARE .sql.todaysummary.com.SQLDMOScript_DRIWithNoCheck int

      DECLARE .sql.todaysummary.com.SQLDMOScript_NoIdentity int

      DECLARE .sql.todaysummary.com.SQLDMOScript_UseQuotedIdentifiers int

      -- SQLDMO_SCRIPT2_TYPE vars

      DECLARE .sql.todaysummary.com.SQLDMOScript2_Default int

      DECLARE .sql.todaysummary.com.SQLDMOScript2_AnsiPadding int

      DECLARE .sql.todaysummary.com.SQLDMOScript2_AnsiFile int

      DECLARE .sql.todaysummary.com.SQLDMOScript2_UnicodeFile int

      DECLARE .sql.todaysummary.com.SQLDMOScript2_NonStop int

      DECLARE .sql.todaysummary.com.SQLDMOScript2_NoFG int

      DECLARE .sql.todaysummary.com.SQLDMOScript2_MarkTriggers int

      DECLARE .sql.todaysummary.com.SQLDMOScript2_OnlyUserTriggers int

      DECLARE .sql.todaysummary.com.SQLDMOScript2_EncryptPWD int

      DECLARE .sql.todaysummary.com.SQLDMOScript2_SeparateXPs int

      -- SQLDMO_SCRIPT_TYPE values

      SET .sql.todaysummary.com.SQLDMOScript_Default = 4

      SET .sql.todaysummary.com.SQLDMOScript_Drops = 1

      SET .sql.todaysummary.com.SQLDMOScript_ObjectPermissions = 2

      SET .sql.todaysummary.com.SQLDMOScript_PrimaryObject = 4

      SET .sql.todaysummary.com.SQLDMOScript_ClusteredIndexes = 8

      SET .sql.todaysummary.com.SQLDMOScript_Triggers = 16

      SET .sql.todaysummary.com.SQLDMOScript_DatabasePermissions = 32

      SET .sql.todaysummary.com.SQLDMOScript_Permissions = 34

      SET .sql.todaysummary.com.SQLDMOScript_ToFileOnly = 64

      SET .sql.todaysummary.com.SQLDMOScript_Bindings = 128

      SET .sql.todaysummary.com.SQLDMOScript_AppendToFile = 256

      SET .sql.todaysummary.com.SQLDMOScript_NoDRI = 512

      SET .sql.todaysummary.com.SQLDMOScript_UDDTsToBaseType = 1024

      SET .sql.todaysummary.com.SQLDMOScript_IncludeIfNotExists = 4096

      SET .sql.todaysummary.com.SQLDMOScript_NonClusteredIndexes = 8192

      SET .sql.todaysummary.com.SQLDMOScript_Indexes = 73736

      SET .sql.todaysummary.com.SQLDMOScript_Aliases = 16384

      SET .sql.todaysummary.com.SQLDMOScript_NoCommandTerm = 32768

      SET .sql.todaysummary.com.SQLDMOScript_DRIIndexes = 65536

      SET .sql.todaysummary.com.SQLDMOScript_IncludeHeaders = 131072

      SET .sql.todaysummary.com.SQLDMOScript_OwnerQualify = 262144

      SET .sql.todaysummary.com.SQLDMOScript_TimestampToBinary = 524288

      SET .sql.todaysummary.com.SQLDMOScript_SortedData = 1048576

      SET .sql.todaysummary.com.SQLDMOScript_SortedDataReorg = 2097152

      SET .sql.todaysummary.com.SQLDMOScript_TransferDefault = 422143

      SET .sql.todaysummary.com.SQLDMOScript_DRI_NonClustered = 4194304

      SET .sql.todaysummary.com.SQLDMOScript_DRI_Clustered = 8388608

      SET .sql.todaysummary.com.SQLDMOScript_DRI_Checks = 16777216

      SET .sql.todaysummary.com.SQLDMOScript_DRI_Defaults = 33554432

      SET .sql.todaysummary.com.SQLDMOScript_DRI_UniqueKeys = 67108864

      SET .sql.todaysummary.com.SQLDMOScript_DRI_ForeignKeys = 134217728

      SET .sql.todaysummary.com.SQLDMOScript_DRI_PrimaryKey = 268435456

      SET .sql.todaysummary.com.SQLDMOScript_DRI_AllKeys = 469762048

      SET .sql.todaysummary.com.SQLDMOScript_DRI_AllConstraints = 520093696

      SET .sql.todaysummary.com.SQLDMOScript_DRI_All = 532676608

      SET .sql.todaysummary.com.SQLDMOScript_DRIWithNoCheck = 536870912

      SET .sql.todaysummary.com.SQLDMOScript_NoIdentity = 1073741824

      SET .sql.todaysummary.com.SQLDMOScript_UseQuotedIdentifiers = -1

      -- SQLDMO_SCRIPT2_TYPE values

      SET .sql.todaysummary.com.SQLDMOScript2_Default = 0

      SET .sql.todaysummary.com.SQLDMOScript2_AnsiPadding = 1

      SET .sql.todaysummary.com.SQLDMOScript2_AnsiFile = 2

      SET .sql.todaysummary.com.SQLDMOScript2_UnicodeFile = 4

      SET .sql.todaysummary.com.SQLDMOScript2_NonStop = 8

      SET .sql.todaysummary.com.SQLDMOScript2_NoFG = 16

      SET .sql.todaysummary.com.SQLDMOScript2_MarkTriggers = 32

      SET .sql.todaysummary.com.SQLDMOScript2_OnlyUserTriggers = 64

      SET .sql.todaysummary.com.SQLDMOScript2_EncryptPWD = 128

      SET .sql.todaysummary.com.SQLDMOScript2_SeparateXPs = 256

      DECLARE .sql.todaysummary.com.dbname sysname,

      .sql.todaysummary.com.srvobject int, -- SQL Server object

      .sql.todaysummary.com.object int, -- Work variable for accessing COM objects

      .sql.todaysummary.com.hr int, -- Contains HRESULT returned by COM

      .sql.todaysummary.com.tfobject int, -- Stores pointer to Transfer object

      .sql.todaysummary.com.scriptoptions int, -- Options bitmask for Transfer

      .sql.todaysummary.com.res int

      SET .sql.todaysummary.com.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 .sql.todaysummary.com.dbname=DB_NAME()

      IF (.sql.todaysummary.com.scriptfilename IS NULL)

      SET .sql.todaysummary.com.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, .sql.todaysummary.com.dbname) WITH

      NOWAIT

      RAISERROR ('', 0, 1) WITH NOWAIT

      -- .sql.todaysummary.com.servername is NULL by default

      IF .sql.todaysummary.com.servername IS NULL

      SET .sql.todaysummary.com.servername = .sql.todaysummary.com..sql.todaysummary.com.SERVERNAME

      ELSE

      RAISERROR ('-- Override of local .sql.todaysummary.com..sql.todaysummary.com.SERVERNAME with .sql.todaysummary.com.servername (sp

      param).

      -- Using local server name: %s', 0, 1, .sql.todaysummary.com.servername) WITH NOWAIT

      RAISERROR ('-- Script w/cmds to recreate indexes/constraints will be

      saved to: %s',

      0, 1, .sql.todaysummary.com.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 .sql.todaysummary.com.hr=sp_OACreate 'SQLDMO.SQLServer', .sql.todaysummary.com.srvobject OUTPUT

      IF (.sql.todaysummary.com.hr <> 0) BEGIN

      RAISERROR ('Error creating SQLDMO.SQLServer object (%d).', 15, 1, .sql.todaysummary.com.hr)

      EXEC sp_displayoaerrorinfo .sql.todaysummary.com.srvobject, .sql.todaysummary.com.hr

      RETURN -1

      END

      -- Connect to the server

      IF (.sql.todaysummary.com.trustedconnection=1) BEGIN

      EXEC .sql.todaysummary.com.hr = sp_OASetProperty .sql.todaysummary.com.srvobject, 'LoginSecure', 1

      IF (.sql.todaysummary.com.hr <> 0) BEGIN

      RAISERROR ('Error setting SQLServer.LoginSecure property (%d).', 15,

      1, .sql.todaysummary.com.hr)

      EXEC sp_displayoaerrorinfo .sql.todaysummary.com.srvobject, .sql.todaysummary.com.hr

      GOTO ServerError

      END

      EXEC .sql.todaysummary.com.hr = sp_OAMethod .sql.todaysummary.com.srvobject, 'Connect', NULL, .sql.todaysummary.com.servername

      END

      ELSE BEGIN

      IF (.sql.todaysummary.com.password IS NOT NULL)

      BEGIN

      EXEC .sql.todaysummary.com.hr = sp_OAMethod .sql.todaysummary.com.srvobject, 'Connect', NULL, .sql.todaysummary.com.servername,

      .sql.todaysummary.com.username, .sql.todaysummary.com.password

      END

      ELSE BEGIN

      EXEC .sql.todaysummary.com.hr = sp_OAMethod .sql.todaysummary.com.srvobject, 'Connect', NULL, .sql.todaysummary.com.servername,

      .sql.todaysummary.com.username

      END

      END

      IF (.sql.todaysummary.com.hr <> 0) BEGIN

      PRINT 'Failed to connect to server ''' + .sql.todaysummary.com.servername

      + '''. If this is not the name of the local SQL Server instance, '

      PRINT 'pass in the optional .sql.todaysummary.com.servername parameter to provide the correct

      local '

      + 'server name as follows: '

      PRINT ''

      PRINT ' EXEC

      sp_generate_index_constraint_script_for_collation_change '

      + '.sql.todaysummary.com.scriptfilename=''...'', '''

      + DB_NAME() + ''', .sql.todaysummary.com.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, .sql.todaysummary.com.hr)

      EXEC sp_displayoaerrorinfo .sql.todaysummary.com.srvobject, .sql.todaysummary.com.hr

      GOTO ServerError

      END

      -- Create a Transfer object

      EXEC .sql.todaysummary.com.hr=sp_OACreate 'SQLDMO.Transfer', .sql.todaysummary.com.tfobject OUTPUT

      IF (.sql.todaysummary.com.hr <> 0) BEGIN

      RAISERROR ('Error creating SQLServer.Transfer object (%d).', 15, 1, .sql.todaysummary.com.hr)

      EXEC sp_displayoaerrorinfo .sql.todaysummary.com.tfobject, .sql.todaysummary.com.hr

      GOTO FreeSrv

      END

      -- Set Transfer's CopyData property

      EXEC .sql.todaysummary.com.hr = sp_OASetProperty .sql.todaysummary.com.tfobject, 'CopyData', 0

      IF (.sql.todaysummary.com.hr <> 0) BEGIN

      RAISERROR ('Error setting Transfer.CopyData property (%d).', 15, 1, .sql.todaysummary.com.hr)

      EXEC sp_displayoaerrorinfo .sql.todaysummary.com.tfobject, .sql.todaysummary.com.hr

      GOTO FreeAll

      END

      -- Tell Transfer to copy the schema

      EXEC .sql.todaysummary.com.hr = sp_OASetProperty .sql.todaysummary.com.tfobject, 'CopySchema', 1

      IF (.sql.todaysummary.com.hr <> 0) BEGIN

      RAISERROR ('Error setting Transfer.CopySchema property (%d).', 15, 1,

      .sql.todaysummary.com.hr)

      EXEC sp_displayoaerrorinfo .sql.todaysummary.com.tfobject, .sql.todaysummary.com.hr

      GOTO FreeAll

      END

      -- Tell Transfer not to copy all objects -- we only want constraints,

      indexes, etc.

      EXEC .sql.todaysummary.com.hr = sp_OASetProperty .sql.todaysummary.com.tfobject, 'CopyAllObjects', 0

      IF (.sql.todaysummary.com.hr <> 0) BEGIN

      RAISERROR ('Error setting Transfer.CopyAllObjects property (%d).', 15,

      1, .sql.todaysummary.com.hr)

      EXEC sp_displayoaerrorinfo .sql.todaysummary.com.tfobject, .sql.todaysummary.com.hr

      GOTO FreeAll

      END

      -- Must tell Transfer to consider all tables even though we aren't scripting

      the base

      -- table itself.

      EXEC .sql.todaysummary.com.hr = sp_OASetProperty .sql.todaysummary.com.tfobject, 'CopyAllTables', 1

      IF (.sql.todaysummary.com.hr <> 0) BEGIN

      RAISERROR ('Error setting Transfer.IncludeGroups property (%d).', 15, 1,

      .sql.todaysummary.com.hr)

      EXEC sp_displayoaerrorinfo .sql.todaysummary.com.tfobject, .sql.todaysummary.com.hr

      GOTO FreeAll

      END

      -- Include object dependencies or script won't create parent table PKs

      before trying

      -- to create child FKs.

      EXEC .sql.todaysummary.com.hr = sp_OASetProperty .sql.todaysummary.com.tfobject, 'IncludeDependencies', 1

      IF (.sql.todaysummary.com.hr <> 0) BEGIN

      RAISERROR ('Error setting Transfer.IncludeDependencies property (%d).',

      15, 1, .sql.todaysummary.com.hr)

      EXEC sp_displayoaerrorinfo .sql.todaysummary.com.tfobject, .sql.todaysummary.com.hr

      GOTO FreeAll

      END

      IF (.sql.todaysummary.com.scriptoptions IS NULL)

      BEGIN

      SET .sql.todaysummary.com.scriptoptions=.sql.todaysummary.com.SQLDMOScript_OwnerQualify | .sql.todaysummary.com.SQLDMOScript_Default

      | .sql.todaysummary.com.SQLDMOScript_Bindings | .sql.todaysummary.com.SQLDMOScript_DRI_All |

      .sql.todaysummary.com.SQLDMOScript_Indexes

      -- Don't script table creates, just indexes and constraints.

      SET .sql.todaysummary.com.scriptoptions=.sql.todaysummary.com.scriptoptions ^ .sql.todaysummary.com.SQLDMOScript_PrimaryObject

      END

      -- Set Transfer's ScriptType property

      EXEC .sql.todaysummary.com.hr = sp_OASetProperty .sql.todaysummary.com.tfobject, 'ScriptType', .sql.todaysummary.com.scriptoptions

      IF (.sql.todaysummary.com.hr <> 0) BEGIN

      RAISERROR ('Error setting Transfer.ScriptType property (%d).', 15, 1,

      .sql.todaysummary.com.hr)

      EXEC sp_displayoaerrorinfo .sql.todaysummary.com.tfobject, .sql.todaysummary.com.hr

      GOTO FreeAll

      END

      -- Get a pointer to the SQLServer object's Databases collection

      EXEC .sql.todaysummary.com.hr = sp_OAGetProperty .sql.todaysummary.com.srvobject, 'Databases', .sql.todaysummary.com.object OUT

      IF .sql.todaysummary.com.hr <> 0 BEGIN

      RAISERROR ('Error getting SQLServer.Databases property (%d).', 15, 1,

      .sql.todaysummary.com.hr)

      EXEC sp_displayoaerrorinfo .sql.todaysummary.com.srvobject, .sql.todaysummary.com.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 .sql.todaysummary.com.hr = sp_OAMethod .sql.todaysummary.com.object, 'Item', .sql.todaysummary.com.object OUT, .sql.todaysummary.com.dbname

      IF .sql.todaysummary.com.hr <> 0 BEGIN

      RAISERROR ('Error getting Databases.Item property (%d).', 15, 1, .sql.todaysummary.com.hr)

      EXEC sp_displayoaerrorinfo .sql.todaysummary.com.object, .sql.todaysummary.com.hr

      GOTO FreeAll

      END

      -- Call the Database object's Transfer method to create a script file with

      the object

      -- definitions.

      EXEC .sql.todaysummary.com.hr = sp_OAMethod .sql.todaysummary.com.object, 'ScriptTransfer', NULL, .sql.todaysummary.com.tfobject, 2,

      .sql.todaysummary.com.scriptfilename

      IF .sql.todaysummary.com.hr <> 0 BEGIN

      RAISERROR ('Error calling Database.ScriptTransfer property (%d).', 15,

      1, .sql.todaysummary.com.hr)

      EXEC sp_displayoaerrorinfo .sql.todaysummary.com.object, .sql.todaysummary.com.hr

      GOTO FreeAll

      END

      PRINT ''

      PRINT 'SQL script with commands to recreate indexes and constraints saved

      to: '

      PRINT ' ' + .sql.todaysummary.com.scriptfilename

      PRINT ''

      GOTO FreeAll

      ServerError:

      SET .sql.todaysummary.com.res=-1

      RAISERROR ('Error transferring schema.', 15, 1)

      FreeAll:

      EXEC sp_OADestroy .sql.todaysummary.com.tfobject

      FreeSrv:

      EXEC sp_OADestroy .sql.todaysummary.com.srvobject

      RETURN .sql.todaysummary.com.res

      GO

      IF OBJECT_ID ('sp_generate_drop_cmds_for_collation_change') IS NOT NULL

      DROP PROC sp_generate_drop_cmds_for_collation_change

      GO

      CREATE PROCEDURE sp_generate_drop_cmds_for_collation_change 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.todaysummary.com.sql nvarchar (4000)

      DECLARE .sql.todaysummary.com.obj_type varchar (30)

      DECLARE .sql.todaysummary.com.itemcount int

      DECLARE .sql.todaysummary.com.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 CONSTRAINT

      ['

      + 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 .sql.todaysummary.com.itemcount = .sql.todaysummary.com..sql.todaysummary.com.ROWCOUNT

      SET .sql.todaysummary.com.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 .sql.todaysummary.com.obj_type, .sql.todaysummary.com.sql

      WHILE (.sql.todaysummary.com..sql.todaysummary.com.FETCH_STATUS <> -1)

      BEGIN

      PRINT 'RAISERROR (''Executing DROP cmd ' + CONVERT (varchar (20),

      .sql.todaysummary.com.currentitem)

      + ' of ' + CONVERT (varchar (20), .sql.todaysummary.com.itemcount) + ': ' + .sql.todaysummary.com.sql

      + ''', 0, 1) WITH NOWAIT'

      PRINT .sql.todaysummary.com.sql

      PRINT 'GO'

      SET .sql.todaysummary.com.currentitem = .sql.todaysummary.com.currentitem + 1

      FETCH NEXT FROM c INTO .sql.todaysummary.com.obj_type, .sql.todaysummary.com.sql

      END

      CLOSE c

      DEALLOCATE c

      PRINT ''

      PRINT ''

      GO

      IF OBJECT_ID ('sp_generate_alter_cmds_for_collation_change') IS NOT NULL

      DROP PROC sp_generate_alter_cmds_for_collation_change

      GO

      CREATE PROCEDURE sp_generate_alter_cmds_for_collation_change

      .sql.todaysummary.com.newcollationname sysname AS

      DECLARE .sql.todaysummary.com.sql nvarchar (4000)

      DECLARE .sql.todaysummary.com.objname sysname

      DECLARE .sql.todaysummary.com.columnname sysname

      DECLARE .sql.todaysummary.com.type sysname

      DECLARE .sql.todaysummary.com.size int

      DECLARE .sql.todaysummary.com.isnullable int -- BOOL. 0=false, 1=true

      DECLARE .sql.todaysummary.com.itemcount int

      DECLARE .sql.todaysummary.com.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 (.sql.todaysummary.com.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, .sql.todaysummary.com.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 ' + .sql.todaysummary.com.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.todaysummary.com.sql = 'ALTER DATABASE [' + DB_NAME() + '] COLLATE '

      + .sql.todaysummary.com.newcollationname

      PRINT .sql.todaysummary.com.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 .sql.todaysummary.com.itemcount = .sql.todaysummary.com..sql.todaysummary.com.ROWCOUNT

      SET .sql.todaysummary.com.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 .sql.todaysummary.com.objname, .sql.todaysummary.com.columnname, .sql.todaysummary.com.type, .sql.todaysummary.com.size,

      .sql.todaysummary.com.isnullable

      WHILE (.sql.todaysummary.com..sql.todaysummary.com.FETCH_STATUS <> -1)

      BEGIN

      -- Generate ALTER TABLE ... ALTER COLUMN stmt.

      SET .sql.todaysummary.com.sql = 'ALTER TABLE [' + .sql.todaysummary.com.objname + '] ' + 'ALTER COLUMN ['

      + .sql.todaysummary.com.columnname + '] ' + .sql.todaysummary.com.type + ' '

      -- Add column length if type is not text/ntext.

      IF .sql.todaysummary.com.type IN ('varchar', 'nvarchar', 'char', 'nchar')

      SET .sql.todaysummary.com.sql = .sql.todaysummary.com.sql + '(' + CAST (.sql.todaysummary.com.size AS varchar) + ') '

      SET .sql.todaysummary.com.sql = .sql.todaysummary.com.sql + 'COLLATE ' + .sql.todaysummary.com.newcollationname + ' '

      IF .sql.todaysummary.com.isnullable = 1

      SET .sql.todaysummary.com.sql = .sql.todaysummary.com.sql + 'NULL '

      ELSE

      SET .sql.todaysummary.com.sql = .sql.todaysummary.com.sql + 'NOT NULL '

      PRINT 'RAISERROR (''Executing ALTER cmd ' + CONVERT (varchar (20),

      .sql.todaysummary.com.currentitem)

      + ' of ' + CONVERT (varchar (20), .sql.todaysummary.com.itemcount) + ': ' + .sql.todaysummary.com.sql

      + ''', 0, 1) WITH NOWAIT'

      PRINT .sql.todaysummary.com.sql

      PRINT 'GO'

      SET .sql.todaysummary.com.currentitem = .sql.todaysummary.com.currentitem + 1

      FETCH NEXT FROM col INTO .sql.todaysummary.com.objname, .sql.todaysummary.com.columnname, .sql.todaysummary.com.type, .sql.todaysummary.com.size,

      .sql.todaysummary.com.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'',''nvarchar'',''text'',''ntext'') '

      PRINT ' AND OBJECTPROPERTY (OBJECT_ID (t.TABLE_SCHEMA + ''.'' +

      t.TABLE_NAME), ''IsMSShipped'')=0 '

      PRINT ' AND COLLATION_NAME != ''' + .sql.todaysummary.com.newcollationname + ''''

      PRINT 'ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME '

      PRINT 'GO'

      GO

      "Jeroen" <PingMe.sql.todaysummary.com.NG.com> wrote in message

      news:11w43ejb68d6z$.1kgha83p7loz4.dlg.sql.todaysummary.com.40tude.net...

      > Hi,

      > is there a way to change the collation of *all* char/varchar fields in

      > every table in a given database?

      > I can do it like 'alter table XYZ alter column Afield char(50)

      > collate Latin1_General_CI_AS' for every field but that's a hell

      > of a job...

      > Groeten,

      > Jeroen

      #3; Thu, 29 May 2008 04:22:00 GMT
    • Thanks everybody for each piece of info!

      I'll look into it.

      Groeten

      Jeroen

      #4; Thu, 29 May 2008 04:23:00 GMT