December 3, 2009 at 8:18 pm
Hi there
i am getting this error whats wrong with this statement?
ALTER TABLE xyz alter column value1 varchar(5000) NOT NULL DEFAULT('')
the table "xyz" has lots of records already i want to change the "value1" column size from 4000 - 5000
but i am getting this error. pls help
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DEFAULT'.
Note:
There is no constraints, there is no NULL value in that column
Cheers
December 3, 2009 at 8:30 pm
http://technet.microsoft.com/en-us/library/ms190273.aspx
Add column with default
USE AdventureWorks ;
GO
CREATE TABLE dbo.doc_exf ( column_a INT) ;
GO
INSERT INTO dbo.doc_exf VALUES (1) ;
GO
ALTER TABLE dbo.doc_exf
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO
Add default to existing column
CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.doc_exz ;
GO
DROP TABLE dbo.doc_exz ;
GO
ALTER TABLE xyz alter column value1 varchar(5000) NOT NULL DEFAULT('')
Would be
ALTER TABLE xyz
ADD CONSTRAINT value1 _def
DEFAULT '' FOR value1 ;
GO
If you are just changing the column data size, omit the default piece
ALTER TABLE xyz alter column value1 varchar(5000) NOT NULL
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply