Need to change All the data types in a table...

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 2000 Forums

SQL Server Development (2000)

Need to change All the data types in a table...

Author

Topic

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-09 : 09:38:03

I've got a table where every field is of type nvarchar, and I want to change them all to varchar, while retaining the original character storage length. I don't have any constraints/defaults to worry about at present.

I've used the following code to generate a dynamic SQL statement, but I'm not sure how to loop through the table to ALTER each column's definition.

--===================================================================
-- This is the code I'm using to generate the list of columns from the table
--===================================================================
SELECT
Column_name, data_type, character_maximum_length, column_default, is_nullable
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
Table_name = 'T_1a'

--===================================================================
-- Declare all the variables for this procedure
--===================================================================
Declare @SQL VarChar(1000)
declare @tablename varchar(50)
declare @colname varchar(50)
declare @datatype varchar(20)
declare @fieldLength varchar(2)
declare @nullstatus varchar(5)

--====================================================================
-- Set the variables to test this code for a single column,
-- need to figure out how to apply these type changes to every
-- column definition in the table.
--====================================================================
set @tablename = 'T_1A_new'
set @colname = 'Filler1'
set @datatype = 'CHAR'
set @fieldLength = '5'
set @nullStatus = 'Null'

--==================================================================
-- Now generate the dynamic SQL
--==================================================================
SELECT @SQL = 'ALTER TABLE '
SELECT @SQL = @SQL + @tablename
SELECT @SQL = @SQL + ' ALTER COLUMN '
SELECT @SQL = @SQL + @colname
SELECT @SQL = @SQL + ' '
SELECT @SQL = @SQL + @datatype
SELECT @SQL = @SQL + '('
SELECT @SQL = @SQL + @fieldlength
SELECT @SQL = @SQL + ') '
SELECT @SQL = @SQL + @nullstatus

Exec ( @SQL)

Thanks,
The Newbie Kevin

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-09 : 09:50:56

You can try this:

SELECT 'ALTER TABLE [' + C.TABLE_SCHEMA + '].[' + C.TABLE_NAME + '] ALTER COLUMN [' + C.COLUMN_NAME + '] varchar(' + cast(C.CHARACTER_MAXIMUM_LENGTH as varchar) + ') ' + CASE C.IS_NULLABLE WHEN 'YES' THEN '' ELSE 'NOT ' END + 'NULL
FROM INFORMATION_SCHEMA.COLUMNS C INNER JOIN INFORMATION_SCHEMA.TABLES T
ON C.TABLE_SCHEMA=T.TABLE_SCHEMA AND C.TABLE_NAME=T.TABLE_NAME
WHERE C.DATA_TYPE='nvarchar' AND T.TABLE_TYPE='BASE TABLE'

You can then copy and paste the results into another query window and run it.

Edit: oooooops, fixed to avoid errors with views.

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-09 : 10:01:41

Rob,

Just looking at that makes my head hurt.

Did you type this all out straight from your brain through your hands, or do you have some kind of tool that generates the script automatically ?

I can already see that my original solution seems to be rather procedurally oriented, the only thing I seem to be missing to be 100% wrong is a Cursor.

Just wondering,
Kevin the Newbie

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-09 : 10:28:57

Ok,

If I add this to the end of your code, to specify the table I'm interested in...

AND T.TABLE_NAME = 'T_1A_new'

Then execute the thing in Query Analyzer - I get a result set that contains all the SQL statements I will need to run against my table, but how do I get that result set to execute and actually alter the table ?

Thanks,
Kevin

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-09 : 10:30:14

All done from the brain.

Code generators are a great way to never learn how to code. I know a lot of people disagree and call them excellent time-savers, and I'm sure they are. But I've seen many people who are totally lost without these tools, and it's pretty funny (and sad) to interview them for jobs and listen to them explain how they "know SQL", yet can't write a SELECT statement. And when I say "SELECT" I mean it, don't even ask them about "FROM". Or "JOIN".

"WHERE"? What's that?

Anyway, I'm pretty notorious for putting my code on one line. Here's a better formatted version:

SELECT 'ALTER TABLE [' +
C.TABLE_SCHEMA +
'].[' +
C.TABLE_NAME +
'] ALTER COLUMN [' +
C.COLUMN_NAME +
'] varchar(' +
cast(C.CHARACTER_MAXIMUM_LENGTH as varchar) +
') ' +
CASE C.IS_NULLABLE WHEN 'YES' THEN '' ELSE 'NOT ' END + 'NULL'
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON C.TABLE_SCHEMA=T.TABLE_SCHEMA
AND C.TABLE_NAME=T.TABLE_NAME
WHERE C.DATA_TYPE='nvarchar'
AND T.TABLE_TYPE='BASE TABLE'

Take it one line at a time, don't go the next until you know what it does. Try changing parts of one line and look at how it affects the results. This is how I learned SQL too, the headache is normal and will go away.

Ooooops, BTW, my code will change ALL nvarchar columns in ALL tables that have them. I'll leave it as an exercise for you to figure out how to fix that.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-09 : 10:31:55

Sorry, posted the last bit after your reply.

You can declare a variable and concatenate all the commands into it, search for "CSV" and you'll find techniques for that.

Truth is it's probably not worth automatically executing this code, just copy the results, paste into QA, and run it manually. How often do you really need to do this?

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-09 : 10:42:25

I'll probably only do this once this decade, but since I was already into this hip deep, I thought I'd give it a shot.

I've got a pretty good feel for what you're doing, I tried assigning the whole statement into a variable @SQL to be executed,like this:

Declare @SQL varchar(3000)
Set @SQL = (your code here)

but received an error back

- something along the lines of "You idiot, you can't do that here, why don't you come back when you've got a friggin clue." or some such thing, well here's what it actually said - but it's about the same I think:

Server: Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Kevin the newbie
(who does understand how to use SELECT FROM WHERE ORDER BY, but has problems sometimes with GROUP BY, JOINS and CORRELATED SUBQUERIES)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-09 : 10:46:39

Correlated subqueries still confuse me, so don't let that bother you.

This should do it:

DECLARE @sql varchar(8000) --might as well max out your varchar declarations if you're gonna execute SQL with them
SELECT @sql='' --gotta set this to an empty string before you start concatenating, a Null will leave you with Null
SELECT @sql=@sql +
'ALTER TABLE [' +
C.TABLE_SCHEMA +
'].[' +
C.TABLE_NAME +
'] ALTER COLUMN [' +
C.COLUMN_NAME +
'] varchar(' +
cast(C.CHARACTER_MAXIMUM_LENGTH as varchar) +
') ' +
CASE C.IS_NULLABLE WHEN 'YES' THEN '' ELSE 'NOT ' END + 'NULL; '
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON C.TABLE_SCHEMA=T.TABLE_SCHEMA
AND C.TABLE_NAME=T.TABLE_NAME
WHERE C.DATA_TYPE='nvarchar'
AND T.TABLE_TYPE='BASE TABLE'
EXEC(@sql)

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-09 : 10:54:04

I'll try your code out after I've had another cup of tea.

Thanks for your time and effort, I think you're my new hero.

Kevin
The Newbie
(The non-programmer, who's havin a hard time breakin his procedural thinking)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources