Re: Need to reconfigure SQL Server to be Case-Insensitive
From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 06/29/04
- Next message: Tim Greenwood: "Re: copy a file with xp_cmdshell"
- Previous message: Russell Fields: "Re: Need to reconfigure SQL Server to be Case-Insensitive"
- In reply to: Ástþór IP: "Re: Need to reconfigure SQL Server to be Case-Insensitive"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 29 Jun 2004 12:52:19 -0500
============================================================================
== DISCLAIMER: test these steps on a development/test server before running
on a production box! ==
============================================================================
You will want to perform ALTER DATABASE and ALTER TABLE statements.
You can "automate" the creation of the ALTER TABLE statements....
USE pubs
go
SELECT 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME +
' ' + DATA_TYPE + '(' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) +') COLLATE
<new setting here>' + ' ' + CASE WHEN IS_NULLABLE = 'NO' THEN 'NULL' ELSE
'NOT NULL' END
+ char(13) + char(10) + 'go'
FROM INFORMATION_SCHEMA.columns
WHERE DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
(you will need to execute the results of the above query)
>From Books Online:
Changing Collations
You can change the collation of a column by using the ALTER TABLE statement:
CREATE TABLE MyTable
(PrimaryKey int PRIMARY KEY,
CharCol varchar(10) COLLATE French_CI_AS NOT NULL
)
GO
ALTER TABLE MyTable ALTER COLUMN CharCol
varchar(10)COLLATE Latin1_General_CI_AS NOT NULL
GO
You cannot alter the collation of a column that is currently referenced by:
A computed column.
An index.
Distribution statistics, either generated automatically or by the CREATE
STATISTICS statement.
A CHECK constraint.
A FOREIGN KEY constraint.
You can also use the COLLATE clause on an ALTER DATABASE to change the
default collation of the database:
ALTER DATABASE MyDatabase COLLATE French_CI_AS
Altering the default collation of a database does not change the collations
of the columns in any existing user-defined tables. These can be changed
with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:
The default collation for the database. This new default collation is
applied to all columns, user-defined data types, variables, and parameters
subsequently created in the database. It is also used when resolving the
object identifiers specified in SQL statements against the objects defined
in the database.
Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables
to the new collation.
All existing char, varchar, text, nchar, nvarchar, or ntext parameters and
scalar return values for stored procedures and user-defined functions to the
new collation.
The char, varchar, text, nchar, nvarchar, or ntext system data types, and
all user-defined data types based on these system data types, to the new
default collation.
After a collation has been assigned to any object other than a column or
database, you cannot change the collation except by dropping and re-creating
the object. This can be a complex operation. To change the default collation
for an instance of Microsoft® SQL Server™ 2000 you must:
Make sure you have all of the information or scripts needed to re-create
your user databases and all of the objects in them.
Export all of your data using a tool such as bulk copy.
Drop all of the user databases.
Rebuild the master database specifying the new collation.
Create all of the databases and all of the objects in them.
Import all of your data.
Note Instead of changing the default collation of an instance of SQL Server
2000, you can specify a default collation for each new database you create.
-- Keith "Ástþór IP" <drasl@spam.aip.cc> wrote in message news:6CEFEEFE-EC9D-498D-9865-A84A9C404C22@microsoft.com... > But I have a lot of databases and many tables in each database. Can I somehow change this setting in MSSQL and on every database and table. > -- > Regards, > Ástþór IP > > > "Gregory A. Larsen" wrote: > > > The case-sensitivity of a table name is based on the collation setting of > > the database. If you change your database collation to Case-Insensitive > > then when you create a new table then the name will be case-insensitive. > > > > -- > > > > -------------------------------------------------------------------------- -- > > -------------------------------------------------------------------------- -- > > ---- > > > > Need SQL Server Examples check out my website at > > http://www.geocities.com/sqlserverexamples > > "Ástþór Ingi Pétursson" <drasl@w.dacoda.is> wrote in message > > news:BCCD39DF-C296-45B8-B45C-3286DF08885F@microsoft.com... > > > Can I somehow do that ? > > > > > > The table names are Case-Sensitive but I need them to Case-Insensitive by > > default. > > > > > >
- Next message: Tim Greenwood: "Re: copy a file with xp_cmdshell"
- Previous message: Russell Fields: "Re: Need to reconfigure SQL Server to be Case-Insensitive"
- In reply to: Ástþór IP: "Re: Need to reconfigure SQL Server to be Case-Insensitive"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
