Avatar of thumper631
thumper631 asked on

Converting nvarchar columns datatype to varchar datatype thru scripting

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 https://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
Microsoft SQL Server

Avatar of undefined
Last Comment
Lowfatspread

8/22/2022 - Mon
Scott Pletcher

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
Scott Pletcher

CORRECTION:

The first FETCH should be:

FETCH NEXT FROM curFixColsGetCols INTO @ColumnName, @ColumnLength

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

ASKER
thumper631

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Scott Pletcher

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.
Lowfatspread

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.
ASKER
thumper631

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


Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Pletcher

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
SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
thumper631

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

Lowfatspread

thanks for sharing the solution.
Your help has saved me hundreds of hours of internet surfing.
fblack61