/* -- 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