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/questions/20869435/converting-nvarchar-columns-datatype-to-varchar-datatype.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
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/questions/20869435/converting-nvarchar-columns-datatype-to-varchar-datatype.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
CORRECTION:
The first FETCH should be:
FETCH NEXT FROM curFixColsGetCols INTO @ColumnName, @ColumnLength
[rather than: FETCH NEXT FROM curFixColsGetCols INTO @ColumnName]
The first FETCH should be:
FETCH NEXT FROM curFixColsGetCols INTO @ColumnName, @ColumnLength
[rather than: FETCH NEXT FROM curFixColsGetCols INTO @ColumnName]
ASKER
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 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 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.
I did one new variable, so I added a DECLARE for it.
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_L ENGTH )
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_sch ema
and t.table_catalog=c.table_ca talog
WHERE t.table_Type = 'Base Table'
AND t.table_name <> 'dtproperties'
order by 1
and then execute the output.
just run this...
SELECT 'ALTER TABLE [' + TABLE_NAME + '] ALTER COLUMN [' + column_name + '] varchar('
+ convert(char(10),CHARACTER
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_sch
and t.table_catalog=c.table_ca
WHERE t.table_Type = 'Base Table'
AND t.table_name <> 'dtproperties'
order by 1
and then execute the output.
ASKER
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
-- 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
thanks for sharing the solution.
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