Solved

Converting nvarchar columns datatype to varchar datatype thru scripting

Posted on 2004-03-30
11
1,866 Views
Last Modified: 2008-02-01
Hello.

I was given a client SQL database that had be converted from Access to SQL 2000.  My problems is that the data was pull in as NVarchar instead of Varchar.  In another example here in EE http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20869435.html?query=converting+nvarchar+to+varchar&searchType=topic the script seem to work if I follow the converstation however I am expected to set the width to 500 char.  I was wondering if there was a way for the script to automaticly set the width.  Here is an example of the orginal converting script


DECLARE curFixColsGetTables CURSOR
FOR SELECT TABLE_NAME
FROM information_schema.tables
WHERE table_Type = 'Base Table'
AND table_name <> 'dtproperties'

OPEN curFixColsGetTables
FETCH NEXT FROM curFixColsGetTables into @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
 
  DECLARE curFixColsGetCols CURSOR
  FOR SELECT COLUMN_NAME
  FROM information_schema.columns
  WHERE table_name = @TableName AND DATA_TYPE = 'nvarchar'

  OPEN curFixColsGetCols
  FETCH NEXT FROM curFixColsGetCols INTO @ColumnName
 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @strSQL = 'ALTER TABLE ' + @TableName
           + ' ALTER COLUMN ' + @ColumnName
           + ' varchar(500)'
    PRINT @strSQL
    EXEC  SP_EXECUTESQL @strSQL

    FETCH NEXT FROM curFixColsGetCols
    INTO @ColumnName
  END
  CLOSE curFixColsGetCols
  DEALLOCATE curFixColsGetCols
 
  FETCH NEXT FROM curFixColsGetTables
  INTO @TableName
  END
CLOSE curFixColsGetTables
DEALLOCATE curFixColsGetTables

So if the NVarchar width is NVarchar(60) then I think that I would like for this to be Varchar(60)

Thanks

James
0
Comment
Question by:thumper631
  • 5
  • 3
  • 3
11 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10714884
Try this:

DECLARE @ColumnLength SMALLINT

DECLARE curFixColsGetTables CURSOR
FOR SELECT TABLE_NAME, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.tables
WHERE table_Type = 'Base Table'
AND table_name <> 'dtproperties'

OPEN curFixColsGetTables
FETCH NEXT FROM curFixColsGetTables into @TableName, @ColumnLength

WHILE @@FETCH_STATUS = 0
BEGIN
 
  DECLARE curFixColsGetCols CURSOR
  FOR SELECT COLUMN_NAME
  FROM information_schema.columns
  WHERE table_name = @TableName AND DATA_TYPE = 'nvarchar'

  OPEN curFixColsGetCols
  FETCH NEXT FROM curFixColsGetCols INTO @ColumnName
 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @strSQL = 'ALTER TABLE ' + @TableName
           + ' ALTER COLUMN ' + @ColumnName
           + ' varchar(' + CAST(@ColumnLength AS VARCHAR(5)) + ')'
    PRINT @strSQL
    --EXEC  SP_EXECUTESQL @strSQL

    FETCH NEXT FROM curFixColsGetCols
    INTO @ColumnName, @ColumnLength
  END
  CLOSE curFixColsGetCols
  DEALLOCATE curFixColsGetCols
 
  FETCH NEXT FROM curFixColsGetTables
  INTO @TableName
  END
CLOSE curFixColsGetTables
DEALLOCATE curFixColsGetTables
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10714898
CORRECTION:

The first FETCH should be:

FETCH NEXT FROM curFixColsGetCols INTO @ColumnName, @ColumnLength

[rather than:  FETCH NEXT FROM curFixColsGetCols INTO @ColumnName]

0
 

Author Comment

by:thumper631
ID: 10715647
Scott.

I received the following 3 errors when running the script

Server: Msg 137, Level 15, State 1, Line 10
Must declare the variable '@TableName'.

Server: Msg 137, Level 15, State 1, Line 22
Must declare the variable '@ColumnName'.

Server: Msg 137, Level 15, State 1, Line 29
Must declare the variable '@strSQL'.

Thx

James
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10715786
I don't what the declarations should most accurately be, since those variables were in your original code, I didn't add them.  They should use whatever declarations they were using before.

I did one new variable, so I added a DECLARE for it.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10715949
you don't really need all the cursors etc...

just run this...

SELECT 'ALTER TABLE [' + TABLE_NAME + '] ALTER COLUMN [' + column_name + '] varchar('
 + convert(char(10),CHARACTER_MAXIMUM_LENGTH )
FROM information_schema.tables as t
inner Join
 (select * from information_schema.columns
    Where DATA_TYPE = 'NVARCHAR') as c
 on t.table_name=c.table_name
and t.table_schema=c.table_schema
and t.table_catalog=c.table_catalog
WHERE t.table_Type = 'Base Table'
AND t.table_name <> 'dtproperties'
order by 1

and then execute the output.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:thumper631
ID: 10716089
Ok, my orginial sql script copy was incomplete.  The code with the addition that Scott made is now the following.......

set nocount on
declare @ColumnName varchar(255)
declare @TableName  varchar(255)
declare @strSQL nvarchar(255)
DECLARE @ColumnLength SMALLINT

DECLARE curFixColsGetTables CURSOR
FOR SELECT TABLE_NAME, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.tables
WHERE table_Type = 'Base Table'
AND table_name <> 'dtproperties'

OPEN curFixColsGetTables
FETCH NEXT FROM curFixColsGetTables into @TableName, @ColumnLength

WHILE @@FETCH_STATUS = 0
BEGIN
 
  DECLARE curFixColsGetCols CURSOR
  FOR SELECT COLUMN_NAME
  FROM information_schema.columns
  WHERE table_name = @TableName AND DATA_TYPE = 'nvarchar'

  OPEN curFixColsGetCols
  FETCH NEXT FROM curFixColsGetCols INTO @ColumnName, @ColumnLength
 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @strSQL = 'ALTER TABLE ' + @TableName
           + ' ALTER COLUMN ' + @ColumnName
           + ' varchar(' + CAST(@ColumnLength AS VARCHAR(5)) + ')'
    PRINT @strSQL
    --EXEC  SP_EXECUTESQL @strSQL

    FETCH NEXT FROM curFixColsGetCols
    INTO @ColumnName, @ColumnLength
  END
  CLOSE curFixColsGetCols
  DEALLOCATE curFixColsGetCols
 
  FETCH NEXT FROM curFixColsGetTables
  INTO @TableName
  END
CLOSE curFixColsGetTables
DEALLOCATE curFixColsGetTables

The error that I now receiving is

Server: Msg 207, Level 16, State 3, Line 9
Invalid column name 'CHARACTER_MAXIMUM_LENGTH'.

The problem with the suggest that Lowfatspread has suggest is that I have to do this for 30+ tables.

Please keep the suggestion coming as I am learning more and more on SQL each day.....

James


0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10716685
D'OH, SORRY, I added the column to the wrong query.  Below is corrected code (minus the variable DECLAREs):


DECLARE curFixColsGetTables CURSOR
FOR SELECT TABLE_NAME
FROM information_schema.tables
WHERE table_Type = 'Base Table'
AND table_name <> 'dtproperties'

OPEN curFixColsGetTables
FETCH NEXT FROM curFixColsGetTables into @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
 
  DECLARE curFixColsGetCols CURSOR
  FOR SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH
  FROM information_schema.columns
  WHERE table_name = @TableName AND DATA_TYPE = 'nvarchar'

  OPEN curFixColsGetCols
  FETCH NEXT FROM curFixColsGetCols INTO @ColumnName, @ColumnLength
 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @strSQL = 'ALTER TABLE ' + @TableName
           + ' ALTER COLUMN ' + @ColumnName
           + ' varchar(' + CAST(@ColumnLength AS VARCHAR(5)) + ')'
    PRINT @strSQL
    --EXEC  SP_EXECUTESQL @strSQL

    FETCH NEXT FROM curFixColsGetCols
    INTO @ColumnName, @ColumnLength
  END
  CLOSE curFixColsGetCols
  DEALLOCATE curFixColsGetCols
 
  FETCH NEXT FROM curFixColsGetTables
  INTO @TableName
  END
CLOSE curFixColsGetTables
DEALLOCATE curFixColsGetTables
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 400 total points
ID: 10716718
CORRECTION:

Change the EXEC to what's below (including ucommenting it :-) (I did that for my own test) :

EXEC (@strSQL)

you don't need sp_executeSQL here and it may cause conversion issues.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 100 total points
ID: 10716808
sorry why is 30+ tables a problem...

you only get the same ouput as you'd generate from the cursor example.......

if you want to process it automatically then

declare @N int
SELECT 'ALTER TABLE [' + TABLE_NAME + '] ALTER COLUMN [' + column_name + '] varchar('
 + convert(char(10),CHARACTER_MAXIMUM_LENGTH )  as SQL, Identity(int,1,1) as rowno into #temp
FROM information_schema.tables as t
inner Join
 (select * from information_schema.columns
    Where DATA_TYPE = 'NVARCHAR') as c
 on t.table_name=c.table_name
and t.table_schema=c.table_schema
and t.table_catalog=c.table_catalog
WHERE t.table_Type = 'Base Table'
AND t.table_name <> 'dtproperties'
order by 1
Set @n = @@ROWCOUNT

Declare @I int
Declare @SQLSTR Varchar(8000)
set @i=1

While @i <=@N
begin
     Select @sqlstr=SQL,@i=@i+1 from #temp where @rowno=@i
--  PRINT @SQLSTR  
     EXEC (@SQLSTR)
End


     
0
 

Author Comment

by:thumper631
ID: 10717396
Here is the finally solution that I used for anyone else who may need to use this

--  Changes all NVarChar --> VarChar in your database
--  thx to Scott Pletcher of Expert Exchange to helping me to complete this

set nocount on
declare @ColumnName varchar(255)
declare @TableName  varchar(255)
declare @strSQL nvarchar(255)
DECLARE @ColumnLength SMALLINT

DECLARE curFixColsGetTables CURSOR
FOR SELECT TABLE_NAME
FROM information_schema.tables
WHERE table_Type = 'Base Table'
AND table_name <> 'dtproperties'

OPEN curFixColsGetTables
FETCH NEXT FROM curFixColsGetTables into @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
 
  DECLARE curFixColsGetCols CURSOR
  FOR SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH
  FROM information_schema.columns
  WHERE table_name = @TableName AND DATA_TYPE = 'nvarchar'

  OPEN curFixColsGetCols
  FETCH NEXT FROM curFixColsGetCols INTO @ColumnName, @ColumnLength
 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @strSQL = 'ALTER TABLE ' + @TableName
           + ' ALTER COLUMN ' + @ColumnName
           + ' varchar(' + CAST(@ColumnLength AS VARCHAR(5)) + ')'
   
--  Run as is to see what fields are going to be change
PRINT @strSQL

--  Uncomment next line to commit changes
--  EXEC  SP_EXECUTESQL @strSQL

    FETCH NEXT FROM curFixColsGetCols
    INTO @ColumnName, @ColumnLength
  END
  CLOSE curFixColsGetCols
  DEALLOCATE curFixColsGetCols
 
  FETCH NEXT FROM curFixColsGetTables
  INTO @TableName
  END
CLOSE curFixColsGetTables
DEALLOCATE curFixColsGetTables

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10718241
thanks for sharing the solution.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now