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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-10 : 08:53:02
|
| Sumit writes "Sir, First i m giving breif info about me that i m working in a result portal and my responsibility is to handle the databases. because we are in a result portal we have to manage lots of databases.I m using Sql Server 2000. Sir generaly we have to change datatypes nvarchar to char in our tables. because if a feild is 1, 2, 3, upto 10 char there is no requirment of nvarchar and in our table we have lots of that kind of fields.Is is any possible Store Proc. which can check the size of a field as if field length less then 10 then datatype change itself nvarchar to char...Sir this is urgent required because we have not such time to change per column indivisually..Waiting for your ReplyThanks in advanceSumit" |
|
|
KnooKie
Aged Yak Warrior
623 Posts |
Posted - 2002-07-10 : 09:13:48
|
| Not sure how you could do that off the top of my head but i'm sure someone cleverer than i will reply soon. I would however suggest that you change the nvarchars to varchars rather than chars.Paul |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-10 : 10:22:37
|
You can get column information from the INFORMATION_SCHEMA.COLUMNS view:SELECT * FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='myTable'This will give you all of the columns for the "myTable" table. You could also try:SELECT * FROM INFORMATION_SCHEMA.COLUMNSWHERE CHARACTER_MAXIMUM_LENGTH<=10That will list all columns that have a character length of 10 or less.FYI, you cannot update this view in order to change the column sizes, you must use ALTER TABLE...ALTER COLUMN in order to do that. There's no really easy way to make all of these changes automatically, and I strongly advise against it anyway. Altering a table structure is not a trivial thing. Do ALL of the column changes by hand, one at a time. To do otherwise would risk data loss. And if you have foreign keys declared on the columns you want to change, you need to drop them first, change the columns, then recreate them. It's far too involved to do automatically.You might be able to do this more easily using Enterprise Manager, but it could take much longer to run.Start now and you'll get it done sooner  MAKE SURE YOU DO FULL DATABASE BACKUPS before you alter your table structures. |
|
|
|
|
|
|
|
|
About Data Types