Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Width of column Expand / Collapse
Author
Message
Posted Wednesday, October 3, 2007 1:13 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 8:32 AM
Points: 765, Visits: 552
Is it possible to change the width of column in sql server?

Thanks,
SR


Thanks,
SR
Post #406408
Posted Wednesday, October 3, 2007 1:36 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:01 PM
Points: 3,843, Visits: 3,836
Yes. If you want to lengthen a column, use ALTER TABLE to assign new data length. If you want to shorten the column, you'll want to drop/add the table or add the new column, copy the data, drop the existing column and sp_rename the column.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #406423
Posted Wednesday, October 3, 2007 1:38 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 8:32 AM
Points: 765, Visits: 552
Thanks John.

SR


Thanks,
SR
Post #406426
Posted Wednesday, October 3, 2007 1:44 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
CREATE TABLE #Test (a VARCHAR(30))
INSERT INTO #Test (a) SELECT REPLICATE('A', 29)
ALTER TABLE #Test
ALTER COLUMN a VARCHAR (29)
--this works fine

DROP TABLE #Test
GO


CREATE TABLE #Test (a VARCHAR(30))
INSERT INTO #Test (a) SELECT REPLICATE('A', 30)
ALTER TABLE #Test
ALTER COLUMN a VARCHAR (29)
--Throws error message because it would change the data
GO
--You can figure out which rows will return an error with a query like this one :
--SELECT * WHERE Column <> Convert(NewDataType, Column)
SELECT * FROM #Test WHERE a <> CONVERT(VARCHAR(29), a)

DROP TABLE #Test
Post #406433
Posted Thursday, October 4, 2007 9:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:19 AM
Points: 2,826, Visits: 8,462
Or you can do it the easy way with the GUI and get it done in about 4 clicks.


Post #406864
Posted Thursday, October 4, 2007 9:14 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
There's always that best practice of scripting everything, but you can always ignore it if you want. ;)
Post #406878
Posted Thursday, October 4, 2007 9:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:19 AM
Points: 2,826, Visits: 8,462
By Best Practices, do you mean make the change with the script, or save the script for historical purposes ?

When I make changes with the GUI, I have set my default to prompt me automatically to generate a script, so I can archive it as I do for SP and other changes.



Post #406910
Posted Thursday, October 4, 2007 9:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
I had ignored that option when I started to manually script things... maybe it's time to start to use other options ;).

Thanks for the idea!


It's also important to point out that I don't really like the way EM scripts things so maybe that's why I was always relunctant to use it... It's definitly a good learning experience however!
Post #406914
Posted Friday, October 5, 2007 2:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 5:17 AM
Points: 159, Visits: 429
It is good to have the option on to generate scripts anyway (however much you like/dislike the SQL produced) - as this reminds you that you should have scripted!

Pity the option is so weirdly hidden in Enterprise Manager - what were they thinking.

For those reading this thread who are unaware of this option - you can set SQL Enterprise Manager to always prompt you to save a change script when you change an object through the GUI - only way I have found to set this on in the GUI is make a change to an object (e.g. a table) and while you are doing it there is a little icon in the tool bar that looks like a scroll with a floppy disk in front of it - clicking this allows you to create a change script and on the bottom of the save script box there is a tick box to "Automatically generate change script on every save" - tick that and in future you will be presented with a create change option every time you change something (you can always answer no to the script option if you don't want one in future - but the reminder will be there).

For those happy with RegEdit the option actually ends up in

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Datatools as AutoSaveChangeScript dword 1 = do 0 = don't

Take the likes below into a text file - give it a .reg extension and you have a .reg file you can apply wherever you want this set:

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Datatools]
"AutoSaveChangeScript"=dword:00000001





James Horsley
Workflow Consulting Limited
Post #407215
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse