Data Conversion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Data Conversion

Author

Topic

afifimk
Starting Member

22 Posts

Posted - 2010-12-04 : 11:25:08

I have a legacy database where I need to convert all the 'CHAR' fileds to 'VARCHAR'. Is there a SP/Sample Code can automate this process for me?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-04 : 14:22:17

Rename the old table.
Recreate the table with the desired formats.
Insert records from old to new.

or, you can do

Alter TableName Alter columnname varchar(length)

for each column.

Poor planning on your part does not constitute an emergency on my part.

afifimk
Starting Member

22 Posts

Posted - 2010-12-04 : 14:38:08

This will work, but I was hoping for an automated/scripted solution as I have about 90 tables and one of those tables has about 140 columns(Bad design, I know).

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-04 : 14:49:01

[code]
Declare @SQL varchar(200)
Declare @table varchar(100), @column varchar(100), @length int

Declare foo CURSOR
FOR
Select a.TABLE_NAME,a.COLUMN_NAME,a.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS a INNER JOIN INFORMATION_SCHEMA.TABLES b
on a.TABLE_NAME = b.TABLE_NAME
WHERE a.DATA_TYPE = 'char' and b.TABLE_TYPE = 'BASE_TABLE'

OPEN foo
FETCH NEXT FROM foo into @table,@column,@length
WHILE @@Fetch_Status = 0
BEGIN
Select @SQL = 'ALTER TABLE [' + @table + '] ALTER COLUMN [' + @column +'] varchar(' + convert(varchar,@length) +')'
PRINT @SQL --test first by printing
--execute (@SQL)

FETCH NEXT FROM foo into @table,@column,@length
END

CLOSE foo
DEALLOCATE foo

[/code]

Probably not the best idea, but it will do what you ask.

Poor planning on your part does not constitute an emergency on my part.

afifimk
Starting Member

22 Posts

Posted - 2010-12-04 : 17:26:48

Thank you, I'll give it a try.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-04 : 17:33:35

Make sure you have a good backup of the database first.

Poor planning on your part does not constitute an emergency on my part.

afifimk
Starting Member

22 Posts

Posted - 2010-12-04 : 17:50:18

Thanks, I have a dev server and will try there first.

afifimk
Starting Member

22 Posts

Posted - 2010-12-04 : 18:00:51

Below is a test table script and I am not sure how to plug in the data from that table into the script that you showed above. I would realy appreciate it if you fill it in for me.


USE [TSGWEB-2]
GO
/****** Object:  Table [dbo].[OPs]    Script Date: 12/04/2010 17:56:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OPs](
	[ID] [int] NOT NULL,
	[OPName] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ActiveFlag] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-04 : 22:15:07

The code I provided didn't require anything else. just run it as is and you will see results which PRINT the proposed SQL statements to alter every column in every table from char to varchar as you requested. It WOULD be more effective if each ALTER table command only ran once on each table...but if you generate the printed results, you can copy/paste that into a new query window and edit it to suit.

If the tables already exist...is your plan now to drop and recreate them? you can do this by generating scripts using the wizard, to a new query window and drop/recreate every table. Before you run the generated script do a find and replace for char to varchar.

Poor planning on your part does not constitute an emergency on my part.

afifimk
Starting Member

22 Posts

Posted - 2010-12-05 : 15:16:44

I ran this part of the code as a test, the columns stayed as CHAR, and nothing printed on the screen.


Declare @SQL varchar(200)
Declare @table varchar(100), @column varchar(100), @length int


Declare foo CURSOR
FOR
Select a.TABLE_NAME,a.COLUMN_NAME,a.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS a INNER JOIN INFORMATION_SCHEMA.TABLES b
	on a.TABLE_NAME = b.TABLE_NAME
WHERE a.DATA_TYPE = 'char' and b.TABLE_TYPE = 'BASE_TABLE'

OPEN foo
FETCH NEXT FROM foo into @table,@column,@length
WHILE @@Fetch_Status = 0
	BEGIN
		Select @SQL = 'ALTER TABLE [' + @table + '] ALTER COLUMN [' + @column +'] varchar(' + convert(varchar,@length) +')'
		PRINT @SQL --test first by printing
		execute (@SQL)
	
	FETCH NEXT FROM foo into @table,@column,@length
	END
 

CLOSE foo
DEALLOCATE foo

Then I ran the following:


Declare @SQL varchar(200)
Declare @table varchar(100), @column varchar(100), @length int


Declare foo CURSOR
FOR
Select a.TABLE_NAME,a.COLUMN_NAME,a.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS a INNER JOIN INFORMATION_SCHEMA.TABLES b
	on a.TABLE_NAME = b.TABLE_NAME
WHERE a.DATA_TYPE = 'char' and b.TABLE_TYPE = 'BASE_TABLE'

OPEN foo
FETCH NEXT FROM foo into @table,@column,@length
WHILE @@Fetch_Status = 0
	BEGIN
		Select @SQL = 'ALTER TABLE [' + @table + '] ALTER COLUMN [' + @column +'] varchar(' + convert(varchar,@length) +')'
		PRINT @SQL --test first by printing
		--execute (@SQL)
	
	--FETCH NEXT FROM foo into @table,@column,@length
	--END
 

--CLOSE foo
--DEALLOCATE foo

and I get the following error:


Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '@SQL'.

Obviously, I am doing something wrong here.

My tables do exist and my intention is to repeat some steps that were giving to me by a DB consultant who gave me DTSK file to run and he test it in DEV and it worked fine now I am trying to do it in production but unable to follow the steps. I opened the DTSK file in note pad and searched for the keywords 'ALTER' or 'DROP' to no avail.

I guess this is getting to be complicated so I think your first suggestion is the way to go. It may take me a little longer but less things to worry about.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-05 : 15:34:07

You are getting the error because you have commented out the loop termination - it is looking for an end after the print @sql.

I would be cautious about running this as it could have to rebuild data pages multiple times for a table.
Will also probably give errors if anything is involved in a foreign key.

I would create new tables and copy the data from the old then rename and create constraints.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-05 : 17:06:08

quote:
Originally posted by afifimk

I guess this is getting to be complicated so I think your first suggestion is the way to go. It may take me a little longer but less things to worry about.

I agree--rename/recreate/insert from old to new like I first suggested is the way to go, but you wanted other options...hope it works out.

Poor planning on your part does not constitute an emergency on my part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-07 : 03:34:52

Run this

Select 'ALTER TABLE ['+a.TABLE_NAME+'] ALTER COLUMN ['+a.COLUMN_NAME+'] varchar('+cast(a.CHARACTER_MAXIMUM_LENGTH as varchar(10))+')'
FROM INFORMATION_SCHEMA.COLUMNS a INNER JOIN INFORMATION_SCHEMA.TABLES b
on a.TABLE_NAME = b.TABLE_NAME
WHERE a.DATA_TYPE = 'char' and b.TABLE_TYPE = 'BASE_TABLE'

Copy the result and run them again

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources