How to modify column datatypes in SQL Server 2000

[canguro]

Hello,

I'm new to using SQL Server 2000. I recently created a table with about 12 columns by importing an already existing spreadsheet. When I look at the table in SQL Server Enterprise Manager, the grid of data is rather wide and I have to scroll to the right to see all columns. Even though I drag each of the column boudaries to the left to try to 'shrink' the view sizes, once I exit Enterprise Manager the changes are not applied/saved.

I would like to see all columns in one screen. Can someone advise if I can do it?

Also, many of the columns have sizes e.g., 8 or 10, that are larger than I need. Is there a simple way to modify column datatypes in a GUI view? Somehow, Design View does not let me make changes to the datatype sizes.

Thank you in advance.

 

[RichardHayes]

I don't think that Enterprise Manager is really designed for displaying/editing table data.

If you use Query Analyzer, or even attach the table to an Access database via a DSN, you will have more luck with tailoring the layout of your output.

 

[canguro]

Thank you for your suggestion. I actually did try to modify the datatype of one column called High in my table, which I call BreakoutWatchList. I tried it in Query Analyzer but got an error message, and somehow I don't see where the obvious error is. Here is what I coded:

alter table BreakoutWatchList
modify High float(7)

Query Analyzer gave me the following message(s):

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'High'.

Please excuse me if my mistake is silly and obvious, since I have just begun playing with SQL Server.

I would appreciate any further advice you can offer. Thanks again.

 

[johnherman]

I can't remember if the database will let you shrink columns, but the age-old solution is to create a temporary column of the size you need (see ALTER TABLE statement), then write a SQL Statement to populate that column. Then DROP the original column (ALTER TABLE again). Then create the column anew with the proper name (ALTER again). The write a SQL statement to move the data back from the Temporary column to the newly resized column. Then DROP the temporary column (ALTER).

You would need to do this for each column you want to "shrink".

 

[SQLSister]

Yor alter table syntax is wrong. You should look it up the syntax in Books Online when you get a syntax error.

alter table BreakoutWatchList
Alter column High float(7) Not null
Or
alter table BreakoutWatchList
Alter column High float(7) null

Depending on whether the column is NUll or not null.

Of course depending on what the datatype currently is, you may or may not be able to convert the data. Also sometimes, for reasons unknown to me, when I have converted numbers to other kinds of numeric data I have lost decimal places even when both data types have them,so I wouldn't do this without making sure you have a current backup. This is always a good policy when convetring data as soometimes things get lost intransalation and need to go back and try something else.

 

[canguro]

My thanks to everyone for your replies and interesting suggestions.

They will be useful in helping me tailor the columns in my table.