Reset collation

Posted on 2006-11-23
Last Modified: 2008-01-09
How can I programmatically (using TSQL) remove the collation sequence from all fields in a database, so that each field has collation sequence "database default" ?

At the moment most fields have a hard coded collation sequence which is different from the database default, and any fields added by the programs will default to the database default, so there's a collation mismatch on each where clause/join etc. I want to get them the same.

sql 2000

Question by:plq
  • 2
  • 2
LVL 10

Accepted Solution

RichardCorrie earned 500 total points
ID: 18003045

      @bolHasIdentity bit,
      @strIdentityColumn sysname,
      @strColName sysname,
      @strDataType varchar(50),
      @strPRECISION varchar(50),
      @intNullable smallint,
      @bolFirst bit,
      @strTable sysname,
      @strcollateSeq varchar(50)

set @strcollateSeq = 'SQL_Latin1_General_CP1_CI_AS' -- or whatever collation seq you want to use
set @strTable = 'bills' -- the name of your table

create table #tmpField
TABLE_QUALIFIER sysname, -- Table or view qualifier name. This field can be NULL.
TABLE_OWNER sysname, -- Table or view owner name. This field always returns a value.
TABLE_NAME sysname, -- Table or view name. This field always returns a value.
COLUMN_NAME sysname, -- Column name, for each column of the TABLE_NAME returned. This field always returns a value.
DATA_TYPE smallint, -- Integer code for ODBC data type. If this is a data type that cannot be mapped to an ODBC type, it is NULL. The native data type name is returned in the TYPE_NAME column.
[TYPE_NAME] varchar(50), -- String representing a data type. The underlying DBMS presents this data type name.
[PRECISION] int, -- Number of significant digits. The return value for the PRECISION column is in base 10.
LENGTH int, -- Transfer size of the data.1
SCALE smallint, -- Number of digits to the right of the decimal point.
RADIX smallint, -- Base for numeric datatypes.
NULLABLE smallint, -- Specifies nullability. 1 = NULL is possible. 0 = NOT NULL.
REMARKS varchar(254), -- This field always returns NULL.
COLUMN_DEF nvarchar(4000), -- Default value of the column.
SQL_DATA_TYPE smallint, -- Value of the SQL data type as it appears in the TYPE field of the descriptor. This column is the same as the DATA_TYPE column, except for the datetime and SQL-92 interval data types. This column always returns a value.
SQL_DATETIME_SUB smallint, -- Subtype code for datetime and SQL-92 interval data types. For other data types, this column returns NULL.
CHAR_OCTET_LENGTH int, -- Maximum length in bytes of a character or integer data type column. For all other data types, this column returns NULL.
ORDINAL_POSITION int, -- Ordinal position of the column in the table. The first column in the table is 1. This column always returns a value.
IS_NULLABLE varchar(254), -- Nullability of the column in the table.
--                  ISO rules are followed to determine nullability.
--                  An ISO SQL-compliant DBMS cannot return an empty string.
--                  YES = Column can include NULLS.
--                  NO = Column cannot include NULLS.
--                  This column returns a zero-length string if nullability is unknown.
--                  The value returned for this column is different from the value returned for the NULLABLE column.
SS_DATA_TYPE tinyint -- SQL Server data type, used by extended stored procedures. For more information, see Data Types.  
insert into #tmpField exec sp_columns @table_name = @strTable

declare crsField cursor for
            ' (' + ltrim(str(F.[PRECISION])) + ')' Precisionx,
            #tmpField F
            charindex('char', F.[TYPE_NAME]) > 0
      order by

open crsField
set @bolFirst = 1

fetch next from crsField into
while @@Fetch_status = 0
-- you could change the print command to exec
      print ('alter table ' + @strTable + ' Alter column ' + @strcolName + ' ' + @strDataType + @strprecision
        + ' COLLATE ' + @strCollateSEq + ' ' + case when @intNullable = 1 then ' Null ' else ' Not Null' end)

      fetch next from crsField into
close crsField
deallocate crsField

Author Comment

ID: 18003132
This is excellent. Just one more minor issue ...

Any idea how to set to <database default> instead of a specific collation ?

     print ('alter table ' + @strTable + ' Alter column ' + @strcolName + ' ' + @strDataType + @strprecision
       + ' COLLATE ' + ????????????????????????? + ' ' + case when @intNullable = 1 then ' Null ' else ' Not Null' end)

LVL 10

Expert Comment

ID: 18003302
try changing it to:

 set @strcollateSeq = 'database_default'   -- or whatever collation seq you want to use


Author Comment

ID: 18003359

Thanks for helping

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Improvement  ( Speed) 14 28
SQL Function NOT ROUND 9 10
divide by zero error 23 16
syntax sql error 2 13
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question