Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Reset collation

Posted on 2006-11-23
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 10

Accepted Solution

RichardCorrie earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

722 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