/* -- Change the data type of all tables from Char and varchar to nchar and nvarchar.
-- Producer build 2004.02 (reference please keep this information )--*/
/* -- Call example:
Exec p_set
--*/
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_set] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_set]
Go
Create procedure p_set
As
Declare TB cursor
Select SQL = 'alter table ['+ D. Name
+ '] Alter column [' + A. Name + '] N'
+ B. Name + '(' + Cast (A. length * 2 as varchar) + ')'
From syscolumns
Left join policypes B on A. xtype = B. xusertype
Inner join sysobjects D on A. ID = D. id and D. xtype = 'U' and D. Name <> 'dtproperties'
Where
B. Name in ('Char ', 'varchar ')
And
Not exists (select 1 from sysobjects where xtype = 'pk' and name in (
Select name from sysindexes where indid in (
Select indid from sysindexkeys where id = A. ID and colid = A. colid
) -- The primary key cannot be modified.
Order by D. Name, A. Name
Declare @ SQL varchar (1000)
Open TB
Fetch next from TB into @ SQL
While @ fetch_status = 0
Begin
Exec (@ SQL)
Fetch next from TB into @ SQL
End
Close TB
Deallocate TB
Go