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
Comment Utility

      @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

Comment Utility
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

Comment Utility
try changing it to:

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


Author Comment

Comment Utility

Thanks for helping

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now