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.
| Author |
Topic |
|
silverkitten
Starting Member
5 Posts |
 Posted - 2004-05-17 : 06:23:49
|
| Hello All,I have a select statement,which needs to be executed on the tables, any scripts or help would be a appreciated.SELECT 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE ' nvarchar ' END + ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' + char(13) + char(10) + 'GO'FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('varchar', 'char')Thanks in advance! |
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-05-17 : 07:08:16
|
| Try something like this. Notice that the 'GO' is removed. Also note that if any columns are in a foreign key constraint the ALTER COLUMN won't work.DECLARE @Command VARCHAR(8000)SELECT @Command = ''SELECT @Command = @Command + 'ALTER TABLE ' + TABLE_NAME +' ALTER COLUMN ' + COLUMN_NAME +CASE WHEN DATA_TYPE = 'char' THEN ' char ' ELSE ' varchar ' END +' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ') 'FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN('varchar', 'char')EXEC (@Command)Raymond |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-17 : 07:37:49
|
| Be careful.Have a look at this - you will be wasting a lot of space and may get errors.I would never use alter table on a production system and sparingly on dev.http://www.nigelrivett.net/AlterTableProblems.htmlNot mentioned there is also the problem that not all t-sql statements follow the logical column pointer so you can get problems.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
silverkitten
Starting Member
5 Posts |
Posted - 2004-05-17 : 08:56:36
|
| Thankyou for the replies..but as warned, it didn't change the columns which had foreign keys defined on them..and i need to change the data types of all the columns in all tables programmatically.any help or scripts would be valuable!Thanks in advance! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-17 : 11:45:29
|
| Nothing to do with foreign keys but read the link I posted.When you alter a fixed length column the server will hold the old column and the new column but point the entry at the new column.This means that you are holding both columns and can quickly use up the max row size.Also some sql commands don't respect the redirection and expect columns to be stored physically in the order they appear in the table definition so you can get invalid results.For foreign keys - as you can see from the above you are creating a new column so the server won't allow it on a foreign key column. You would have to remove the constraint and add it again.But I would advise against this - create a new table, copy the data and dro[ the old one.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-05-21 : 12:44:26
|
quote:
but as warned, it didn't change the columns which had foreign keys defined on them..
Use GENERATE SQL in Enterprise Manager and JUST tick the "Script Primary key / Foreign key ..." box.Analysis the generated script and you should find that all the DROP FOREIGN KEY statements are grouped together (somewhere near the top of the script) and all the "CREATE" [actually ALTER, but.] FOREIGN KEY statements are also neatly grouped together (somewhere near the end!)Split those two blocks into two scripts and execute the DROP before your ALTER COLUMN stuff, and the "CREATE" afterwards.If the site is live whilst you are doing this you will have no FKeys for a while whilst everything is running ...The other caveats mentioned here still apply ...An alternative would be to script the whole database, globally change CHAR to NCHAR etc., make a new DB, run the new script (not including FKey stuff perhaps so that data import does not foul up), DTS the data across from the old DB, run the FKey create part of the script, and then Backup/Restore back into the original DB. This will give you nice clean tables, but may have other issues. Make sure you script everything on the first General Tab, and click on Create/Drop AND "Extended properties" on the Formatting Tab, and Object level permissions, indexes [both of them], triggers, PK/FK/Def/Chk which are NOT the default options. My preference is to also change the File Format to Windows Text as not all editors will cope with the default Unicode text.Kristen |
|
|
|
Loknath
Starting Member
2 Posts |
Posted - 2004-05-24 : 05:06:59
|
| Hi Raymond!you are right but you forgot declare the variable with datatype nvarchar in the following code..DECLARE @Command VARCHAR(8000)SELECT @Command = ''SELECT @Command = @Command + 'ALTER TABLE ' + TABLE_NAME +' ALTER COLUMN ' + COLUMN_NAME +CASE WHEN DATA_TYPE = 'char' THEN ' char ' ELSE ' varchar ' END +' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ') 'FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN('varchar', 'char')EXEC (@Command)Loknath |
|
|
|
|
|
|
|
|
how to integrate a select statement inside an exec