conversion char/nchar

Hi,
We are using sql server 2000..We intend to change the
char/varchar columns to nchar/nvarchat.Our application
contains lot's of dynamic tables.Hence there are no
standard no of tables and indexes in all sites..Can any
one help me out in writing scripts which queries the
dictionary objects and gives scripts which would work fine
in all sites...?
Thanks in advance
SridharThis script should get you started:
use tempdb
GO
CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar(10), thircol =
char(10))
go
SELECT 'ALTER TABLE ' + table_name +
' ALTER COLUMN ' + COLUMN_NAME +
CASE WHEN DATA_TYPE =3D 'char' THEN ' nchar ' ELSE ' nvarchar ' =
END +=20
' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
char(13) + char(10) + 'GO'
FROM information_schema.columns WHERE DATA_TYPE IN ('varchar', 'char')
--now execute the statements that are returned from the select statement
go
DROP TABLE foo=20
--=20
Keith
<anonymous@.discussions.microsoft.com> wrote in message =
news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> Hi,
>=20
> We are using sql server 2000..We intend to change the=20
> char/varchar columns to nchar/nvarchat.Our application=20
> contains lot's of dynamic tables.Hence there are no=20
> standard no of tables and indexes in all sites..Can any=20
> one help me out in writing scripts which queries the=20
> dictionary objects and gives scripts which would work fine=20
> in all sites...?
>=20
>=20
> Thanks in advance
>=20
> Sridhar
>|||Hi,
Thanks..do some where sql server stores the index and
constraints structure some where in dictionary ...other
wise how do i recreate the indexes and constraints after
converting to nchar
Sridhar
>--Original Message--
>This script should get you started:
>use tempdb
>GO
>CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar
(10), thircol char(10))
>go
>
>SELECT 'ALTER TABLE ' + table_name +
> ' ALTER COLUMN ' + COLUMN_NAME +
> CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE '
nvarchar ' END +
> ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
> char(13) + char(10) + 'GO'
>FROM information_schema.columns WHERE DATA_TYPE IN
('varchar', 'char')
>--now execute the statements that are returned from the
select statement
>go
>DROP TABLE foo
>
>
>--
>Keith
>
><anonymous@.discussions.microsoft.com> wrote in message
news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
fine[vbcol=seagreen]
>.
>|||SQL Server stores this information in system tables, like sysindexes, syscom
ments etc. You can read off of
these and use that information to re-generate the statements needed to re-cr
eate your stuff. Or script the
stuff: http://www.karaszi.com/sqlserver/in...ate_script.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:898401c432a5$31453bb0$a601280a@.p
hx.gbl...[vbcol=seagreen]
> Hi,
> Thanks..do some where sql server stores the index and
> constraints structure some where in dictionary ...other
> wise how do i recreate the indexes and constraints after
> converting to nchar
> Sridhar
> (10), thircol char(10))
> nvarchar ' END +
> ('varchar', 'char')
> select statement
> news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> fine