Solved

Reset collation

Posted on 2006-11-23
4
918 Views
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

thanks
0
Comment
Question by:plq
  • 2
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
RichardCorrie earned 500 total points
ID: 18003045
try:

declare
      @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
      select
            F.Column_Name,
            F.[Type_Name],
            ' (' + ltrim(str(F.[PRECISION])) + ')' Precisionx,
            F.Nullable
      from
            #tmpField F
      where
            charindex('char', F.[TYPE_NAME]) > 0
      order by
            Ordinal_Position

open crsField
set @bolFirst = 1

fetch next from crsField into
            @strColName,
            @strDataType,
            @strPRECISION,
            @intNullable
while @@Fetch_status = 0
begin
-- 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
                  @strColName,
                  @strDataType,
                  @strPRECISION,
                  @intNullable
end
close crsField
deallocate crsField
go
0
 
LVL 8

Author Comment

by:plq
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)

0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 18003302
try changing it to:

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

/Richard
0
 
LVL 8

Author Comment

by:plq
ID: 18003359
Excellent

Thanks for helping
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

930 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

10 Experts available now in Live!

Get 1:1 Help Now