Changing data type in multiple tables
-
Hello, I am trying to change certain columns in different tables to different data types. the two columns I need showing table name and column name are put into @Temptable but when I do the ALTER, table_name and column_name are not being filled in with the appropriate values. Does anybody have any ideas? Thanks Ned.
declare @Rows integer
declare @TempTable table
(
GroupID int identity (1,1),
table_name varchar(60),
column_name varchar (50)
PRIMARY KEY (GroupID, table_name, column_name)
)
insert into @TempTable
(table_name, column_name)
select table_name, column_name from information_schema.columns
where table_name not like 'Temp_%'
and data_type = 'nvarchar'
select @Rows = count(*) from @TempTable
IF (@Rows=0)
BEGIN
RETURN
END
while (@Rows>0)
Begin
ALTER TABLE table_name ALTER COLUMN column_name varchar(70) NOT NULL
Select @Rows = @Rows -1
End
-
I can only assume you have not posted all of your code; but rather a shell to get the idea across? I am not sure where you are getting table_name and column_name from in your ALTER TABLE and ALTER COLUMN statement. I would have assumed these would be @Variables and would be EXECUTED dynamically.
I must be missing something...
I wasn't born stupid - I had to study.
-
First of all you need dynamic sql to do this.
Second, I see you are assuming that all nVarChar columns in your database are size 50. This tells me that it's a very small DB and you can remember every single column you created which means you could do this task by hand since it would take only a few minutes, or it's a very poorly designed DB. I've never seen a db where are string columns are all the same size.
I'm not trying to hurt you in any way but I don't think this task is best handled by a script unless you REALLY know what you are doing and you need to apply this task to many db and servers at the same time... but even then I'd be relunctant to use a script like this because you could suffer data loss either by changing from nvarchar to varchar but also by mistakenly changing from size 200 to 50.
-
Hello, In my temporary table there are two columns one is the table_name column the other is the column_name column. All the tables and the columns I want to change the data type are here in the temporary table(I used a print statement to make sure). I am not sure how to go into all of those tables listed in that temp table and using an Alter statement and row count to change all the data types of all those columns listed. If that makes any sense.
also don't worry about data types not working(nvarchar to varchar), I just changed some code because I couldn't post actual code. data type size will be the same.
I updated original post to show correct data type.
-
I think this is along the lines of what you want. But!!!, I would heed Remi's advice. This is very big change and you need much more code to ensure you are not truncating data. This will script out code making everything varchar(70). You can copy and paste that code into Query Analyzer and alter each statement to be the correct length or you can play with this for a while and get the current length of each field and add that to your table and insert into the Dynamic SQL.
This is very powerful and very dangerous. I would highly recommend playing with this on a dummy database before you try this in production. All of us have made flubbed stuff in the past
and we hate to see someone else even getting near that edge. You stated you were not sure how to get into the @TempTable and ALTER all of the tables. Please, keep this code in the PRINT mode and learn how to use it before you make such big changes to your database. DECLARE @Rows integer,
@TableName varchar(50),
@ColumnName varchar(50),
@SQL varchar(2000)
DECLARE @TempTable table
( GroupID int identity (1,1),
table_name varchar(60),
column_name varchar (60)
PRIMARY KEY (GroupID, table_name, column_name) )
INSERT INTO @TempTable( table_name, column_name)
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name NOT LIKE 'Temp_%'
AND data_type = 'nvarchar'
SELECT @Rows = COUNT(*) FROM @TempTable
IF( @Rows = 0)
BEGIN
RETURN
END
WHILE( @Rows > 0)
BEGIN
SELECT @TableName = (SELECT table_name FROM @TempTable WHERE GroupID = @Rows)
SELECT @ColumnName = (SELECT column_name FROM @TempTable WHERE GroupID = @Rows)
SELECT @SQL = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' varchar(50) NOT NULL'
PRINT @SQL
SELECT @Rows = @Rows -1
END
I wasn't born stupid - I had to study.
-
Thanks Farrell, thats exactly what I needed. Don't worry I'm working on a local test database. Thanks, Ned.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply