October 3, 2007 at 1:13 pm
Is it possible to change the width of column in sql server?
Thanks,
SR
Thanks,
SR
October 3, 2007 at 1:36 pm
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.
October 3, 2007 at 1:38 pm
Thanks John.
SR
Thanks,
SR
October 3, 2007 at 1:44 pm
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
October 4, 2007 at 9:05 am
Or you can do it the easy way with the GUI and get it done in about 4 clicks.
October 4, 2007 at 9:14 am
There's always that best practice of scripting everything, but you can always ignore it if you want. 😉
October 4, 2007 at 9:40 am
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.
October 4, 2007 at 9:45 am
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!
October 5, 2007 at 2:11 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply