Convert a Unicode Database into a non-unicode database

[Rodger Dodger]

Hi there.

We have an application that can run on a non-unicode or a unicode sql
server
database.

Currently the application is running in a unicode database, as a
non-unicode database is less than half the size, I would prefer to
have a non-unicode database for demo purposes to be on my laptop, etc
etc

Is it possible to change a unicode sql server 2000 database into a
non-unicode database?

And if so, how would I go about doing this?

Any help would be greatly appreciated.

Thanks

Rodger

[David Portas]

Re: Convert a Unicode Database into a non-unicode database

Unicode data uses NCHAR / NVARCHAR / NTEXT datatypes rather than CHAR /
VARCHAR / TEXT. This is defined at the column level rather than the database
level.

To change your database you could script all the tables, replace the unicode
datatypes with their non-unicode equivalents and then run the creation
script to create the new database. This might affect the behaviour of stored
procedures and other code but if the app is designed to cope with this then
presumably it shouldn't be a problem. Correctly converting your unicode data
to a non-unicode collation might be more of a challenge though.

--
David Portas
SQL Server MVP
--

[Simon Hayes]

Re: Convert a Unicode Database into a non-unicode database

wsbackup@hotmai l.com (Rodger Dodger) wrote in message news:<c1467e75. 0408102040.6037 ee2f@posting.go ogle.com>...[color=blue]
> Hi there.
>
> We have an application that can run on a non-unicode or a unicode sql
> server
> database.
>
> Currently the application is running in a unicode database, as a
> non-unicode database is less than half the size, I would prefer to
> have a non-unicode database for demo purposes to be on my laptop, etc
> etc
>
> Is it possible to change a unicode sql server 2000 database into a
> non-unicode database?
>
> And if so, how would I go about doing this?
>
> Any help would be greatly appreciated.
>
> Thanks
>
> Rodger[/color]

I'm not sure I understand your question - MSSQL always supports
Unicode in all databases. You can change a database's collation, but
that doesn't affect Unicode support, so I guess you may want to know
how to change all your nvarchar columns to varchar? If so, then one
way is to execute the results of a query like this:

SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' ALTER
COLUMN ' + COLUMN_NAME + ' VARCHAR(' + CAST(CHARACTER_ MAXIMUM_LENGTH
AS VARCHAR(10)) + ')'
FROM INFORMATION_SCH EMA.COLUMNS
WHERE DATA_TYPE = 'NVARCHAR'

This is a rather heavy-handed approach, however, so make sure you have
a backup before running it. If this doesn't help, or isn't what you're
trying to do, then please clarify what you need to achieve.

Simon