Hi, How to alter a table with default value?
I am using the below statement, But, it is not working..Any pointers?
Thx..
----------
alter table action_item ALTER COLUMN STATUS default 0ALTER TABLE ACTION_ITEM ADD CONSTRAINT
DF_ACTION_ITEM_STATUS DEFAULT 0 FOR STATUS
GO
UPDATE ACTION_ITEM SET STATUS =0 where STATUS IS NULL
GO
Showing posts with label default. Show all posts
Showing posts with label default. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Altering a Default- unknown name...
SyetwemHi There.
I'd like to alter a DEFAULT I have set up on a database table.
Unfortunately, I don't know the name of the default as it was created when
the table was created without specifying a name.
Q: How do I find the name of a column DEFAULT (from INFORMATION_SCHEMA or
System tables)?...
Or is there a more straightforward way to alter a DEFAULT?
any help would be appreciated!
My final lines of code should look something like:
ALTER TABLE [dbo].[MyTable] DROP
DEFAULT <defaultname>FOR [Column1]
GO
ALTER TABLE [dbo].[MyTable] ADD
CONSTRAINT [DF_MyTableColumn1] DEFAULT ('DefaultValue') FOR [Column1]
GOselect OBJECT_NAME(constid),scl.name
FROM sysconstraints scr
INNER JOIN syscolumns scl
ON scr.id = scl.id
AND scr.colid = scl.colid
WHERE OBJECT_NAME(scr.id) = 'YourColumnname'
AND scr.status = 2069
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"len" <len@.discussions.microsoft.com> wrote in message
news:9DFCC435-DAC9-495F-AC64-4FEC20841F04@.microsoft.com...
> SyetwemHi There.
> I'd like to alter a DEFAULT I have set up on a database table.
> Unfortunately, I don't know the name of the default as it was created when
> the table was created without specifying a name.
> Q: How do I find the name of a column DEFAULT (from INFORMATION_SCHEMA or
> System tables)?...
> Or is there a more straightforward way to alter a DEFAULT?
> any help would be appreciated!
> My final lines of code should look something like:
> ALTER TABLE [dbo].[MyTable] DROP
> DEFAULT <defaultname>FOR [Column1]
> GO
> ALTER TABLE [dbo].[MyTable] ADD
> CONSTRAINT [DF_MyTableColumn1] DEFAULT ('DefaultValue') FOR [Column1]
> GO
>|||Thanks - I just tried that though and it seems to only return defaults that
have been named explicitly (If I remove the WHERE clause I can see a list of
all explicitly created defaults from my database)
perhaps there's a way of dropping the default on a column without knowing
the name?...
"Roji. P. Thomas" wrote:
> select OBJECT_NAME(constid),scl.name
> FROM sysconstraints scr
> INNER JOIN syscolumns scl
> ON scr.id = scl.id
> AND scr.colid = scl.colid
> WHERE OBJECT_NAME(scr.id) = 'YourColumnname'
> AND scr.status = 2069
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "len" <len@.discussions.microsoft.com> wrote in message
> news:9DFCC435-DAC9-495F-AC64-4FEC20841F04@.microsoft.com...
>
>|||> perhaps there's a way of dropping the default on a column without knowing
> the name?...
Here you go
DECLARE @.defname VARCHAR(100), @.cmd VARCHAR(1000)
SET @.defname =
(SELECT name
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = 'YourTableName'
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id('dbo.YourTableName) AND
name = 'YourColumnName'))
SET @.cmd = 'ALTER TABLE YourTableName DROP CONSTRAINT '
+ @.defname
EXEC(@.cmd)
Its taken from the following article by Ron Talmage. Have a look
http://msdn.microsoft.com/library/d.../>
ql00a11.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"len" <len@.discussions.microsoft.com> wrote in message
news:EA55D84B-A1D3-4BBF-B979-E85371D2F2DA@.microsoft.com...
> Thanks - I just tried that though and it seems to only return defaults
> that
> have been named explicitly (If I remove the WHERE clause I can see a list
> of
> all explicitly created defaults from my database)
> perhaps there's a way of dropping the default on a column without knowing
> the name?...
> "Roji. P. Thomas" wrote:
>|||You can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"len" <len@.discussions.microsoft.com> wrote in message
news:EA55D84B-A1D3-4BBF-B979-E85371D2F2DA@.microsoft.com...
> Thanks - I just tried that though and it seems to only return defaults
> that
> have been named explicitly (If I remove the WHERE clause I can see a list
> of
> all explicitly created defaults from my database)
> perhaps there's a way of dropping the default on a column without knowing
> the name?...
> "Roji. P. Thomas" wrote:
>|||Thanks guys - they worked a treat!!sql
I'd like to alter a DEFAULT I have set up on a database table.
Unfortunately, I don't know the name of the default as it was created when
the table was created without specifying a name.
Q: How do I find the name of a column DEFAULT (from INFORMATION_SCHEMA or
System tables)?...
Or is there a more straightforward way to alter a DEFAULT?
any help would be appreciated!
My final lines of code should look something like:
ALTER TABLE [dbo].[MyTable] DROP
DEFAULT <defaultname>FOR [Column1]
GO
ALTER TABLE [dbo].[MyTable] ADD
CONSTRAINT [DF_MyTableColumn1] DEFAULT ('DefaultValue') FOR [Column1]
GOselect OBJECT_NAME(constid),scl.name
FROM sysconstraints scr
INNER JOIN syscolumns scl
ON scr.id = scl.id
AND scr.colid = scl.colid
WHERE OBJECT_NAME(scr.id) = 'YourColumnname'
AND scr.status = 2069
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"len" <len@.discussions.microsoft.com> wrote in message
news:9DFCC435-DAC9-495F-AC64-4FEC20841F04@.microsoft.com...
> SyetwemHi There.
> I'd like to alter a DEFAULT I have set up on a database table.
> Unfortunately, I don't know the name of the default as it was created when
> the table was created without specifying a name.
> Q: How do I find the name of a column DEFAULT (from INFORMATION_SCHEMA or
> System tables)?...
> Or is there a more straightforward way to alter a DEFAULT?
> any help would be appreciated!
> My final lines of code should look something like:
> ALTER TABLE [dbo].[MyTable] DROP
> DEFAULT <defaultname>FOR [Column1]
> GO
> ALTER TABLE [dbo].[MyTable] ADD
> CONSTRAINT [DF_MyTableColumn1] DEFAULT ('DefaultValue') FOR [Column1]
> GO
>|||Thanks - I just tried that though and it seems to only return defaults that
have been named explicitly (If I remove the WHERE clause I can see a list of
all explicitly created defaults from my database)
perhaps there's a way of dropping the default on a column without knowing
the name?...
"Roji. P. Thomas" wrote:
> select OBJECT_NAME(constid),scl.name
> FROM sysconstraints scr
> INNER JOIN syscolumns scl
> ON scr.id = scl.id
> AND scr.colid = scl.colid
> WHERE OBJECT_NAME(scr.id) = 'YourColumnname'
> AND scr.status = 2069
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "len" <len@.discussions.microsoft.com> wrote in message
> news:9DFCC435-DAC9-495F-AC64-4FEC20841F04@.microsoft.com...
>
>|||> perhaps there's a way of dropping the default on a column without knowing
> the name?...
Here you go
DECLARE @.defname VARCHAR(100), @.cmd VARCHAR(1000)
SET @.defname =
(SELECT name
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = 'YourTableName'
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id('dbo.YourTableName) AND
name = 'YourColumnName'))
SET @.cmd = 'ALTER TABLE YourTableName DROP CONSTRAINT '
+ @.defname
EXEC(@.cmd)
Its taken from the following article by Ron Talmage. Have a look
http://msdn.microsoft.com/library/d.../>
ql00a11.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"len" <len@.discussions.microsoft.com> wrote in message
news:EA55D84B-A1D3-4BBF-B979-E85371D2F2DA@.microsoft.com...
> Thanks - I just tried that though and it seems to only return defaults
> that
> have been named explicitly (If I remove the WHERE clause I can see a list
> of
> all explicitly created defaults from my database)
> perhaps there's a way of dropping the default on a column without knowing
> the name?...
> "Roji. P. Thomas" wrote:
>|||You can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"len" <len@.discussions.microsoft.com> wrote in message
news:EA55D84B-A1D3-4BBF-B979-E85371D2F2DA@.microsoft.com...
> Thanks - I just tried that though and it seems to only return defaults
> that
> have been named explicitly (If I remove the WHERE clause I can see a list
> of
> all explicitly created defaults from my database)
> perhaps there's a way of dropping the default on a column without knowing
> the name?...
> "Roji. P. Thomas" wrote:
>|||Thanks guys - they worked a treat!!sql
Alter View permission?
MSDN says the following at
http://msdn.microsoft.com/library/d...rl=/library/en-
us/tsqlref/ts_aa-az_2gtz.asp:
"ALTER VIEW permissions default to members of the db_owner and
db_ddladmin fixed database roles, and to the view owner. These
permissions are not transferable.
To alter a view, the user must have ALTER VIEW permission along with
SELECT permission on the tables, views, and table-valued functions being
referenced in the view, ..."
The BOL documentation for GRANT doesn't show ALTER VIEW as a permissible
statement type to grant permissions on. Trying to GRANT ALTER VIEW
doesn't work. How can I give a user ALTER VIEW permission? Does "not
transferable" mean I can't?
Thanks.
David WalkerYou can add the user to the db_owner or db_ddladmin fixed database roles.
AMB
"DWalker" wrote:
> MSDN says the following at
> http://msdn.microsoft.com/library/d...rl=/library/en-
> us/tsqlref/ts_aa-az_2gtz.asp:
> "ALTER VIEW permissions default to members of the db_owner and
> db_ddladmin fixed database roles, and to the view owner. These
> permissions are not transferable.
> To alter a view, the user must have ALTER VIEW permission along with
> SELECT permission on the tables, views, and table-valued functions being
> referenced in the view, ..."
> The BOL documentation for GRANT doesn't show ALTER VIEW as a permissible
> statement type to grant permissions on. Trying to GRANT ALTER VIEW
> doesn't work. How can I give a user ALTER VIEW permission? Does "not
> transferable" mean I can't?
> Thanks.
> David Walker
>|||I ran across the DDLAdmin role, but BOL makes it sound like you can
grant ALTER VIEW permissions. Maybe I'm misreading it -- I suppose
those permissions are only given to members of db_owner and db_ddladmin,
and they can't be granted to anyone else.
The phrase "ALTER VIEW permissions default to members of the db_owner
and db_ddladmin fixed database roles" should have "default to" replaced
by "are restricted to" if that's the case.
It would be nice to be able to grant ALTER VIEW permission on one view
to one user or role.
Thanks, AlejandroMesa.
David Walker
"examnotes"
<AlejandroMesa@.discussions.microsoft.com> wrote in
news:668F79B3-3335-4BC3-860F-67F467C12CEF@.microsoft.com:
> You can add the user to the db_owner or db_ddladmin fixed database
> roles.
>
> AMB
> "DWalker" wrote:
>
>|||You cannot give a user (that is not member of db_owner and db_ddladmin
fixed dabase roles) the permission to alter a specific view (that he
does not own). This is exactly what "not transferable" means (at least,
this is what it means to me).
As I see it, you have the following alternatives:
a) make the user a member of db_ddladmin role: this will enable him to
make any modification to the objects in the database, including
creating and deleting other objects (tables, views, procedures, etc)
b) grant the user the "CREATE VIEW" permission; the views that are
created by him will be will be owned by him, and so he will be able to
modify them
c) create the views in his name, by prefixing them with his user name
instead of "dbo" (without granting him the "CREATE VIEW" permission).
He will be able to modify those views (and to delete them), but he
won't be able to create new views (or other objects).
The problem with the b) and c) alternatives is that only that user will
be able to access those views by specifying only the view's name (the
other users must prefix the view's name with the user name).
Razvan|||It would be NICE if I could grant a user permission to alter a specific
view, but we'll take what we can get...
I'll probably add the user to the ddladmin role, although that's more
power than I would like to give. But the other choices aren't great
either. Thanks.
David
"Razvan Socol" <rsocol@.gmail.com> wrote in
news:1112639463.986674.297650@.f14g2000cwb.googlegroups.com:
> You cannot give a user (that is not member of db_owner and db_ddladmin
> fixed dabase roles) the permission to alter a specific view (that he
> does not own). This is exactly what "not transferable" means (at
> least, this is what it means to me).
> As I see it, you have the following alternatives:
> a) make the user a member of db_ddladmin role: this will enable him to
> make any modification to the objects in the database, including
> creating and deleting other objects (tables, views, procedures, etc)
> b) grant the user the "CREATE VIEW" permission; the views that are
> created by him will be will be owned by him, and so he will be able to
> modify them
> c) create the views in his name, by prefixing them with his user name
> instead of "dbo" (without granting him the "CREATE VIEW" permission).
> He will be able to modify those views (and to delete them), but he
> won't be able to create new views (or other objects).
> The problem with the b) and c) alternatives is that only that user
> will be able to access those views by specifying only the view's name
> (the other users must prefix the view's name with the user name).
> Razvan
>
http://msdn.microsoft.com/library/d...rl=/library/en-
us/tsqlref/ts_aa-az_2gtz.asp:
"ALTER VIEW permissions default to members of the db_owner and
db_ddladmin fixed database roles, and to the view owner. These
permissions are not transferable.
To alter a view, the user must have ALTER VIEW permission along with
SELECT permission on the tables, views, and table-valued functions being
referenced in the view, ..."
The BOL documentation for GRANT doesn't show ALTER VIEW as a permissible
statement type to grant permissions on. Trying to GRANT ALTER VIEW
doesn't work. How can I give a user ALTER VIEW permission? Does "not
transferable" mean I can't?
Thanks.
David WalkerYou can add the user to the db_owner or db_ddladmin fixed database roles.
AMB
"DWalker" wrote:
> MSDN says the following at
> http://msdn.microsoft.com/library/d...rl=/library/en-
> us/tsqlref/ts_aa-az_2gtz.asp:
> "ALTER VIEW permissions default to members of the db_owner and
> db_ddladmin fixed database roles, and to the view owner. These
> permissions are not transferable.
> To alter a view, the user must have ALTER VIEW permission along with
> SELECT permission on the tables, views, and table-valued functions being
> referenced in the view, ..."
> The BOL documentation for GRANT doesn't show ALTER VIEW as a permissible
> statement type to grant permissions on. Trying to GRANT ALTER VIEW
> doesn't work. How can I give a user ALTER VIEW permission? Does "not
> transferable" mean I can't?
> Thanks.
> David Walker
>|||I ran across the DDLAdmin role, but BOL makes it sound like you can
grant ALTER VIEW permissions. Maybe I'm misreading it -- I suppose
those permissions are only given to members of db_owner and db_ddladmin,
and they can't be granted to anyone else.
The phrase "ALTER VIEW permissions default to members of the db_owner
and db_ddladmin fixed database roles" should have "default to" replaced
by "are restricted to" if that's the case.
It would be nice to be able to grant ALTER VIEW permission on one view
to one user or role.
Thanks, AlejandroMesa.
David Walker
"examnotes"
<AlejandroMesa@.discussions.microsoft.com> wrote in
news:668F79B3-3335-4BC3-860F-67F467C12CEF@.microsoft.com:
> You can add the user to the db_owner or db_ddladmin fixed database
> roles.
>
> AMB
> "DWalker" wrote:
>
>|||You cannot give a user (that is not member of db_owner and db_ddladmin
fixed dabase roles) the permission to alter a specific view (that he
does not own). This is exactly what "not transferable" means (at least,
this is what it means to me).
As I see it, you have the following alternatives:
a) make the user a member of db_ddladmin role: this will enable him to
make any modification to the objects in the database, including
creating and deleting other objects (tables, views, procedures, etc)
b) grant the user the "CREATE VIEW" permission; the views that are
created by him will be will be owned by him, and so he will be able to
modify them
c) create the views in his name, by prefixing them with his user name
instead of "dbo" (without granting him the "CREATE VIEW" permission).
He will be able to modify those views (and to delete them), but he
won't be able to create new views (or other objects).
The problem with the b) and c) alternatives is that only that user will
be able to access those views by specifying only the view's name (the
other users must prefix the view's name with the user name).
Razvan|||It would be NICE if I could grant a user permission to alter a specific
view, but we'll take what we can get...
I'll probably add the user to the ddladmin role, although that's more
power than I would like to give. But the other choices aren't great
either. Thanks.
David
"Razvan Socol" <rsocol@.gmail.com> wrote in
news:1112639463.986674.297650@.f14g2000cwb.googlegroups.com:
> You cannot give a user (that is not member of db_owner and db_ddladmin
> fixed dabase roles) the permission to alter a specific view (that he
> does not own). This is exactly what "not transferable" means (at
> least, this is what it means to me).
> As I see it, you have the following alternatives:
> a) make the user a member of db_ddladmin role: this will enable him to
> make any modification to the objects in the database, including
> creating and deleting other objects (tables, views, procedures, etc)
> b) grant the user the "CREATE VIEW" permission; the views that are
> created by him will be will be owned by him, and so he will be able to
> modify them
> c) create the views in his name, by prefixing them with his user name
> instead of "dbo" (without granting him the "CREATE VIEW" permission).
> He will be able to modify those views (and to delete them), but he
> won't be able to create new views (or other objects).
> The problem with the b) and c) alternatives is that only that user
> will be able to access those views by specifying only the view's name
> (the other users must prefix the view's name with the user name).
> Razvan
>
ALTER TABLE/COLUMN syntax
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
AndrisSimply add the default with an ALTER TABLE:
ALTER TABLE firmNoliktava_test
ADD CONSTRAINT DF1_firmNoliktava_test
DEFAULT 1 FOR valstsID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Andris" <spameris@.gmail.com> wrote in message
news:eY4DCdHiGHA.3956@.TK2MSFTNGP02.phx.gbl...
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
Andris|||On Mon, 05 Jun 2006 11:08:02 +0300, Andris wrote:
>Hi!
>I want a add default value to existing column with int type with
>following syntax:
>ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
>but got error
>Msg 156, Level 15, State 1, Line 2
>Incorrect syntax near the keyword 'SET'.
>Server SQL 2005 x64, in server Help Contents i see example
>ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
>Corporation'
>
>What i do wrong ?
Hi Andris,
The example you have seen is not for SQL Server, but for SQL Server
Mobile edition. There are many syntax difference between "normal" SQL
Server and the mobile version. I've been tricked by this myself quite a
few times already - just remember to always check the heading of the
subject in Books Online to check if you're looking at a Mobile or a
T-SQL subject.
Hugo Kornelis, SQL Server MVP
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
AndrisSimply add the default with an ALTER TABLE:
ALTER TABLE firmNoliktava_test
ADD CONSTRAINT DF1_firmNoliktava_test
DEFAULT 1 FOR valstsID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Andris" <spameris@.gmail.com> wrote in message
news:eY4DCdHiGHA.3956@.TK2MSFTNGP02.phx.gbl...
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
Andris|||On Mon, 05 Jun 2006 11:08:02 +0300, Andris wrote:
>Hi!
>I want a add default value to existing column with int type with
>following syntax:
>ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
>but got error
>Msg 156, Level 15, State 1, Line 2
>Incorrect syntax near the keyword 'SET'.
>Server SQL 2005 x64, in server Help Contents i see example
>ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
>Corporation'
>
>What i do wrong ?
Hi Andris,
The example you have seen is not for SQL Server, but for SQL Server
Mobile edition. There are many syntax difference between "normal" SQL
Server and the mobile version. I've been tricked by this myself quite a
few times already - just remember to always check the heading of the
subject in Books Online to check if you're looking at a Mobile or a
T-SQL subject.
Hugo Kornelis, SQL Server MVP
ALTER TABLE/COLUMN syntax
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
AndrisSimply add the default with an ALTER TABLE:
ALTER TABLE firmNoliktava_test
ADD CONSTRAINT DF1_firmNoliktava_test
DEFAULT 1 FOR valstsID
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Andris" <spameris@.gmail.com> wrote in message
news:eY4DCdHiGHA.3956@.TK2MSFTNGP02.phx.gbl...
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
Andris|||On Mon, 05 Jun 2006 11:08:02 +0300, Andris wrote:
>Hi!
>I want a add default value to existing column with int type with
>following syntax:
>ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
>but got error
>Msg 156, Level 15, State 1, Line 2
>Incorrect syntax near the keyword 'SET'.
>Server SQL 2005 x64, in server Help Contents i see example
>ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
>Corporation'
>
>What i do wrong ?
Hi Andris,
The example you have seen is not for SQL Server, but for SQL Server
Mobile edition. There are many syntax difference between "normal" SQL
Server and the mobile version. I've been tricked by this myself quite a
few times already - just remember to always check the heading of the
subject in Books Online to check if you're looking at a Mobile or a
T-SQL subject.
--
Hugo Kornelis, SQL Server MVP
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
AndrisSimply add the default with an ALTER TABLE:
ALTER TABLE firmNoliktava_test
ADD CONSTRAINT DF1_firmNoliktava_test
DEFAULT 1 FOR valstsID
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Andris" <spameris@.gmail.com> wrote in message
news:eY4DCdHiGHA.3956@.TK2MSFTNGP02.phx.gbl...
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
Andris|||On Mon, 05 Jun 2006 11:08:02 +0300, Andris wrote:
>Hi!
>I want a add default value to existing column with int type with
>following syntax:
>ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
>but got error
>Msg 156, Level 15, State 1, Line 2
>Incorrect syntax near the keyword 'SET'.
>Server SQL 2005 x64, in server Help Contents i see example
>ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
>Corporation'
>
>What i do wrong ?
Hi Andris,
The example you have seen is not for SQL Server, but for SQL Server
Mobile edition. There are many syntax difference between "normal" SQL
Server and the mobile version. I've been tricked by this myself quite a
few times already - just remember to always check the heading of the
subject in Books Online to check if you're looking at a Mobile or a
T-SQL subject.
--
Hugo Kornelis, SQL Server MVP
Alter table with Merge replication
Hi,
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.
Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegr oups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.
Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegr oups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>
Alter table with Merge replication
Hi,
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegroups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegroups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>
Alter table with Merge replication
Hi,
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegroups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegroups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>
Thursday, March 22, 2012
alter table set default value for money type column
I run the sql like the following
Alter table ItemStone add ISPurPrice money default 0
then when I select the itemStone table, I find the field ISPurPrice is still
Null, not 0, why?
I'm using SQL Server ver 8.0 (2000)
Thx!!
Kei,
Use the WITH VALUES option in your statement, or (probably better)
declare your new column as NOT NULL. Here are the choices:
Alter table ItemStone add ISPurPrice money NOT NULL default 0
Alter table ItemStone add ISPurPrice money default 0 WITH VALUES
From Books Online, topic ALTER TABLE:
WITH VALUES
Specifies that the value given in DEFAULT constant_expression is stored
in a new column added to existing rows. WITH VALUES can be specified
only when DEFAULT is specified in an ADD column clause. If the added
column allows null values and WITH VALUES is specified, the default
value is stored in the new column added to existing rows. If WITH VALUES
is not specified for columns that allow nulls, the value NULL is stored
in the new column in existing rows. If the new column does not allow
nulls, the default value is stored in new rows regardless of whether
WITH VALUES is specified.
Steve Kass
Drew University
kei wrote:
>I run the sql like the following
>Alter table ItemStone add ISPurPrice money default 0
>then when I select the itemStone table, I find the field ISPurPrice is still
>Null, not 0, why?
>I'm using SQL Server ver 8.0 (2000)
>Thx!!
>
Alter table ItemStone add ISPurPrice money default 0
then when I select the itemStone table, I find the field ISPurPrice is still
Null, not 0, why?
I'm using SQL Server ver 8.0 (2000)
Thx!!
Kei,
Use the WITH VALUES option in your statement, or (probably better)
declare your new column as NOT NULL. Here are the choices:
Alter table ItemStone add ISPurPrice money NOT NULL default 0
Alter table ItemStone add ISPurPrice money default 0 WITH VALUES
From Books Online, topic ALTER TABLE:
WITH VALUES
Specifies that the value given in DEFAULT constant_expression is stored
in a new column added to existing rows. WITH VALUES can be specified
only when DEFAULT is specified in an ADD column clause. If the added
column allows null values and WITH VALUES is specified, the default
value is stored in the new column added to existing rows. If WITH VALUES
is not specified for columns that allow nulls, the value NULL is stored
in the new column in existing rows. If the new column does not allow
nulls, the default value is stored in new rows regardless of whether
WITH VALUES is specified.
Steve Kass
Drew University
kei wrote:
>I run the sql like the following
>Alter table ItemStone add ISPurPrice money default 0
>then when I select the itemStone table, I find the field ISPurPrice is still
>Null, not 0, why?
>I'm using SQL Server ver 8.0 (2000)
>Thx!!
>
Tuesday, March 20, 2012
ALTER TABLE DEFAULT
/* for the google index */
ALTER TABLE
DEFAULT COLUMN
DEFAULT VALUE
ALTER TABLE
DEFAULT COLUMN
DEFAULT VALUE
I've worked out several stored procedures for altering the default column
values in a table. They were compiled from books and code snippets found
here. It was a pain to work out so I've decided to share my work and
research here. This post is just my way of saying thanks to several others
here for posting with their wisdom and intelligence.
Michael
simpsonAT(dot)cts(dot)com
This procedure gets the constraint name. If you use the design view to
setup a default value, you won't know the system assigned constraint name.
This proc makes it an non issue. This code was gleened from this news
group.
CREATE PROCEDURE [DBO].[GetConstraintName]
(
@.tablename sysname,
@.columnName sysname,
@.constraintName sysname OUTPUT
)
as
SELECT
@.constraintName = o1.name
FROM
sysobjects o1
INNER JOIN
syscolumns c ON o1.id = c.cdefault
INNER JOIN
sysobjects o2 ON o1.parent_obj = o2.id
WHERE (o2.name = @.tablename) AND (c.name = @.columnName)
This procedure changes the default value for a column that is a numeric. It
uses the previously define stored procedure to get the constraint name. A
text version of this procedure can be created by removing the cast, defining
the input parameter "newConstraint" as varchar(255).
CREATE PROCEDURE [dbo].[ChangeIntConstraint]
(
@.tableName sysname,
@.columnName sysname,
@.newConstraint int
)
AS
Declare @.conName sysname
exec GetConstraintName @.tableName, @.columnName, @.constraintName = @.conName
OUT
declare @.sql nvarchar(1024)
set @.sql = 'ALTER TABLE ' + @.tableName + ' drop constraint ' + @.conName
exec(@.sql)
set @.sql = 'ALTER TABLE ' + @.tableName + ' ADD CONSTRAINT ' + @.conName + '
DEFAULT (' + CAST(@.newConstraint AS varchar(255)) + ') FOR ' + @.columnName
exec(@.sql)Why not make @.newConstraint character.
Then you can get the column type in the SP and cater for numeric and
character columns and dates.|||I am by no means an expert. If you want to post that change here, please do.
I think I know what you mean, but it would take even more time for me to
hack it out.
I did a Google news search on the subject. I saw that it had been asked
numerous times in the past. Some people responded with dropping and
recreating the table. I was just sharing what took me several hours to hash
out.
Michael
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:7d6610e9.0311021918.19e6c409@.posting.google.c om...
> Why not make @.newConstraint character.
> Then you can get the column type in the SP and cater for numeric and
> character columns and dates.|||Haven't tested it but this sould be quite close. Change the constraint
value parameter to character
The relies on dates being passed in in a good format - e.g. 'yyyymmdd
hh:mm:ss'
declare @.datatype varchar(10)
select @.datatype = case when DATA_TYPE like '%char%' then 'char'
when DATA_TYPE like '%date%' then 'date'
else 'numeric'
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @.tableName
andCOLUMN_NAME = @.columnName
select @.sql = 'ALTER TABLE ' + @.tableName
select@.sql = @.sql +' ADD CONSTRAINT ' + @.conName
if @.datatype = numeric
select@.sql = @.sql + ' DEFAULT (' + @.newConstraint + ')'
else
select@.sql = @.sql + ' DEFAULT (''' + @.newConstraint + ''')'
select@.sql = @.sql + ' FOR ' + @.columnName
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
ALTER TABLE dateAuto
I must add to an existing TABLE a column DateInsert
with a default value = date auto
if a new row is added the column must add datetime.now automaticly (like in acccess 2000) how can I do it ?
for MS SQL 2000
thank youCREATE TABLE #patp (
id INT IDENTITY
, asof DATETIME NOT NULL
DEFAULT GetDate()
, other VARCHAR(10) NULL
)
INSERT #patp (other) VALUES ('One')
INSERT #patp (other) VALUES ('Two')
INSERT #patp (other) VALUES ('Three')
SELECT * FROM #patp
-PatP|||alter table MyTable Add
DateAuto datetime CONSTRAINT DF_DateAuto DEFAULT (GetDate())|||thank you Pat Phelan and hmscott
great ! and fast !!!
with a default value = date auto
if a new row is added the column must add datetime.now automaticly (like in acccess 2000) how can I do it ?
for MS SQL 2000
thank youCREATE TABLE #patp (
id INT IDENTITY
, asof DATETIME NOT NULL
DEFAULT GetDate()
, other VARCHAR(10) NULL
)
INSERT #patp (other) VALUES ('One')
INSERT #patp (other) VALUES ('Two')
INSERT #patp (other) VALUES ('Three')
SELECT * FROM #patp
-PatP|||alter table MyTable Add
DateAuto datetime CONSTRAINT DF_DateAuto DEFAULT (GetDate())|||thank you Pat Phelan and hmscott
great ! and fast !!!
Monday, March 19, 2012
Alter table - add default value
Helo Group,
In my database I have table :
idDoc (int) IDENTITY (1, 1),
UploadDate (datetime)
DocName (varchar).
Now I ought too add default value (getdate()) for new document.
How I can use Alter table for update structure my table.
thx
PawelRHi Pawel
The Books Online page for ALTER TABLE has a section called "Adding a default
constraint to an existing column". Books Online should always be the first
place you look for syntax help. I realize that ALTER TABLE is a long
article, but the information you need is there.
ALTER TABLE my_table
ADD CONSTRAINT col_uploadDate_def
DEFAULT getdate() FOR uploadDate
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"PawelR" <pawelratajczak;-at-;poczta;dot;onet;dot;pl> wrote in message
news:umBxwWVbGHA.4292@.TK2MSFTNGP04.phx.gbl...
> Helo Group,
> In my database I have table :
> idDoc (int) IDENTITY (1, 1),
> UploadDate (datetime)
> DocName (varchar).
> Now I ought too add default value (getdate()) for new document.
> How I can use Alter table for update structure my table.
> thx
> PawelR
>|||You can update default constraint using Enterprise Manager.
In table design, you can spcify default value for uploaddate column.
"PawelR"?? ??? ??:
> Helo Group,
> In my database I have table :
> idDoc (int) IDENTITY (1, 1),
> UploadDate (datetime)
> DocName (varchar).
> Now I ought too add default value (getdate()) for new document.
> How I can use Alter table for update structure my table.
> thx
> PawelR
>
>
In my database I have table :
idDoc (int) IDENTITY (1, 1),
UploadDate (datetime)
DocName (varchar).
Now I ought too add default value (getdate()) for new document.
How I can use Alter table for update structure my table.
thx
PawelRHi Pawel
The Books Online page for ALTER TABLE has a section called "Adding a default
constraint to an existing column". Books Online should always be the first
place you look for syntax help. I realize that ALTER TABLE is a long
article, but the information you need is there.
ALTER TABLE my_table
ADD CONSTRAINT col_uploadDate_def
DEFAULT getdate() FOR uploadDate
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"PawelR" <pawelratajczak;-at-;poczta;dot;onet;dot;pl> wrote in message
news:umBxwWVbGHA.4292@.TK2MSFTNGP04.phx.gbl...
> Helo Group,
> In my database I have table :
> idDoc (int) IDENTITY (1, 1),
> UploadDate (datetime)
> DocName (varchar).
> Now I ought too add default value (getdate()) for new document.
> How I can use Alter table for update structure my table.
> thx
> PawelR
>|||You can update default constraint using Enterprise Manager.
In table design, you can spcify default value for uploaddate column.
"PawelR"?? ??? ??:
> Helo Group,
> In my database I have table :
> idDoc (int) IDENTITY (1, 1),
> UploadDate (datetime)
> DocName (varchar).
> Now I ought too add default value (getdate()) for new document.
> How I can use Alter table for update structure my table.
> thx
> PawelR
>
>
ALTER Table
I am trying to use the ALTER table Alter column command to set the default
value of a column and to drop the default value of a column. can anyone pls
give me the syntax.I tried the one given in sql help file but gives me a
syntax error.
thanks!To add a default after the table has been created:
alter table <table name>
add constraint <constraint name>
default (<expression> )
for <column name>
To drop a default:
alter table <table name>
drop constraint <constraint name>
Is that what you need?
ML
http://milambda.blogspot.com/|||No i need to alter a column's default... like set a new default value or dro
p
default.but iam getting syntax errors.
"ML" wrote:
> To add a default after the table has been created:
> alter table <table name>
> add constraint <constraint name>
> default (<expression> )
> for <column name>
> To drop a default:
> alter table <table name>
> drop constraint <constraint name>
> Is that what you need?
>
> ML
> --
> http://milambda.blogspot.com/|||You cannot change the default in a single statement. You have to drop the
old default constraint, and add the new one, like ML illustrated.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"HP" <HP@.discussions.microsoft.com> wrote in message
news:50D57999-EC50-4E7C-9E04-D538FFB2EE7A@.microsoft.com...
> No i need to alter a column's default... like set a new default value or
> drop
> default.but iam getting syntax errors.
> "ML" wrote:
>
>|||Are you talking about a default as a database object that you have bound to
a
column? Have you tried sp_unbindefault?
ML
http://milambda.blogspot.com/
value of a column and to drop the default value of a column. can anyone pls
give me the syntax.I tried the one given in sql help file but gives me a
syntax error.
thanks!To add a default after the table has been created:
alter table <table name>
add constraint <constraint name>
default (<expression> )
for <column name>
To drop a default:
alter table <table name>
drop constraint <constraint name>
Is that what you need?
ML
http://milambda.blogspot.com/|||No i need to alter a column's default... like set a new default value or dro
p
default.but iam getting syntax errors.
"ML" wrote:
> To add a default after the table has been created:
> alter table <table name>
> add constraint <constraint name>
> default (<expression> )
> for <column name>
> To drop a default:
> alter table <table name>
> drop constraint <constraint name>
> Is that what you need?
>
> ML
> --
> http://milambda.blogspot.com/|||You cannot change the default in a single statement. You have to drop the
old default constraint, and add the new one, like ML illustrated.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"HP" <HP@.discussions.microsoft.com> wrote in message
news:50D57999-EC50-4E7C-9E04-D538FFB2EE7A@.microsoft.com...
> No i need to alter a column's default... like set a new default value or
> drop
> default.but iam getting syntax errors.
> "ML" wrote:
>
>|||Are you talking about a default as a database object that you have bound to
a
column? Have you tried sp_unbindefault?
ML
http://milambda.blogspot.com/
Sunday, March 11, 2012
alter table
Using the alter table command i create a new column on a existing table.
When I creater this new column I want to set the default value as 0. For
some reason the column name is null.
SQL statement:
ALTER TABLE [dbo].[DMINFORMATION] WITH NOCHECK ADD
[ACTIVEFLAGS] [bit] DEFAULT ((0))
Any ideas or the issue with SQL statement?
Thanks,
Big DBig D,
The default value becomes 0 for new rows, but I'll guess what
you want is for the value of this column in existing rows to be 0. In
order to apply a DEFAULT to existing rows, add WITH VALUES to
the statement (and remove NOCHECK, because it doesn't make any
sense for a DEFAULT constraint).
ALTER TABLE [dbo].[DMINFORMATION]
ADD [ACTIVEFLAGS] [bit] DEFAULT 0 WITH VALUES
Steve Kass
Drew University
Big D wrote:
>Using the alter table command i create a new column on a existing table.
>When I creater this new column I want to set the default value as 0. For
>some reason the column name is null.
>SQL statement:
>ALTER TABLE [dbo].[DMINFORMATION] WITH NOCHECK ADD
>[ACTIVEFLAGS] [bit] DEFAULT ((0))
>Any ideas or the issue with SQL statement?
>Thanks,
>Big D
>
>
When I creater this new column I want to set the default value as 0. For
some reason the column name is null.
SQL statement:
ALTER TABLE [dbo].[DMINFORMATION] WITH NOCHECK ADD
[ACTIVEFLAGS] [bit] DEFAULT ((0))
Any ideas or the issue with SQL statement?
Thanks,
Big DBig D,
The default value becomes 0 for new rows, but I'll guess what
you want is for the value of this column in existing rows to be 0. In
order to apply a DEFAULT to existing rows, add WITH VALUES to
the statement (and remove NOCHECK, because it doesn't make any
sense for a DEFAULT constraint).
ALTER TABLE [dbo].[DMINFORMATION]
ADD [ACTIVEFLAGS] [bit] DEFAULT 0 WITH VALUES
Steve Kass
Drew University
Big D wrote:
>Using the alter table command i create a new column on a existing table.
>When I creater this new column I want to set the default value as 0. For
>some reason the column name is null.
>SQL statement:
>ALTER TABLE [dbo].[DMINFORMATION] WITH NOCHECK ADD
>[ACTIVEFLAGS] [bit] DEFAULT ((0))
>Any ideas or the issue with SQL statement?
>Thanks,
>Big D
>
>
Wednesday, March 7, 2012
ALTER COLUMN?
I want to change a column in a table. The data type is int, and it allows
NULLs. I want to make it non-nullable and give it a DEFAULT value of 0. I've
looked up ALTER TABLE in BOL but I don't seem to see what the syntax is to
accomplish this. Is it even possible? TIA!You can do someting like this
CREATE TABLE TESTDEFAULTS (
ID INT)
INSERT INTO TESTDEFAULTS
VALUES (1)
SELECT *
FROM TESTDEFAULTS
ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
INSERT INTO TESTDEFAULTS
DEFAULT VALUES
SELECT *
FROM TESTDEFAULTS
--This will give an error now
INSERT INTO TestDefaultS VALUES (NULL)
DROP TABLE TESTDEFAULTS
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks! At first I got an error but once I UPDATEd the column to 0 WHERE it
IS NULL, voila! Again, thank you!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147462107.839063.66230@.d71g2000cwd.googlegroups.com...
> You can do someting like this
> CREATE TABLE TESTDEFAULTS (
> ID INT)
> INSERT INTO TESTDEFAULTS
> VALUES (1)
> SELECT *
> FROM TESTDEFAULTS
> ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
> ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
> INSERT INTO TESTDEFAULTS
> DEFAULT VALUES
>
> SELECT *
> FROM TESTDEFAULTS
> --This will give an error now
> INSERT INTO TestDefaultS VALUES (NULL)
> DROP TABLE TESTDEFAULTS
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
NULLs. I want to make it non-nullable and give it a DEFAULT value of 0. I've
looked up ALTER TABLE in BOL but I don't seem to see what the syntax is to
accomplish this. Is it even possible? TIA!You can do someting like this
CREATE TABLE TESTDEFAULTS (
ID INT)
INSERT INTO TESTDEFAULTS
VALUES (1)
SELECT *
FROM TESTDEFAULTS
ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
INSERT INTO TESTDEFAULTS
DEFAULT VALUES
SELECT *
FROM TESTDEFAULTS
--This will give an error now
INSERT INTO TestDefaultS VALUES (NULL)
DROP TABLE TESTDEFAULTS
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks! At first I got an error but once I UPDATEd the column to 0 WHERE it
IS NULL, voila! Again, thank you!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147462107.839063.66230@.d71g2000cwd.googlegroups.com...
> You can do someting like this
> CREATE TABLE TESTDEFAULTS (
> ID INT)
> INSERT INTO TESTDEFAULTS
> VALUES (1)
> SELECT *
> FROM TESTDEFAULTS
> ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
> ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
> INSERT INTO TESTDEFAULTS
> DEFAULT VALUES
>
> SELECT *
> FROM TESTDEFAULTS
> --This will give an error now
> INSERT INTO TestDefaultS VALUES (NULL)
> DROP TABLE TESTDEFAULTS
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
ALTER COLUMN?
I want to change a column in a table. The data type is int, and it allows
NULLs. I want to make it non-nullable and give it a DEFAULT value of 0. I've
looked up ALTER TABLE in BOL but I don't seem to see what the syntax is to
accomplish this. Is it even possible? TIA!You can do someting like this
CREATE TABLE TESTDEFAULTS (
ID INT)
INSERT INTO TESTDEFAULTS
VALUES (1)
SELECT *
FROM TESTDEFAULTS
ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
INSERT INTO TESTDEFAULTS
DEFAULT VALUES
SELECT *
FROM TESTDEFAULTS
--This will give an error now
INSERT INTO TestDefaultS VALUES (NULL)
DROP TABLE TESTDEFAULTS
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks! At first I got an error but once I UPDATEd the column to 0 WHERE it
IS NULL, voila! Again, thank you!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147462107.839063.66230@.d71g2000cwd.googlegroups.com...
> You can do someting like this
> CREATE TABLE TESTDEFAULTS (
> ID INT)
> INSERT INTO TESTDEFAULTS
> VALUES (1)
> SELECT *
> FROM TESTDEFAULTS
> ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
> ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
> INSERT INTO TESTDEFAULTS
> DEFAULT VALUES
>
> SELECT *
> FROM TESTDEFAULTS
> --This will give an error now
> INSERT INTO TestDefaultS VALUES (NULL)
> DROP TABLE TESTDEFAULTS
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
NULLs. I want to make it non-nullable and give it a DEFAULT value of 0. I've
looked up ALTER TABLE in BOL but I don't seem to see what the syntax is to
accomplish this. Is it even possible? TIA!You can do someting like this
CREATE TABLE TESTDEFAULTS (
ID INT)
INSERT INTO TESTDEFAULTS
VALUES (1)
SELECT *
FROM TESTDEFAULTS
ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
INSERT INTO TESTDEFAULTS
DEFAULT VALUES
SELECT *
FROM TESTDEFAULTS
--This will give an error now
INSERT INTO TestDefaultS VALUES (NULL)
DROP TABLE TESTDEFAULTS
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks! At first I got an error but once I UPDATEd the column to 0 WHERE it
IS NULL, voila! Again, thank you!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147462107.839063.66230@.d71g2000cwd.googlegroups.com...
> You can do someting like this
> CREATE TABLE TESTDEFAULTS (
> ID INT)
> INSERT INTO TESTDEFAULTS
> VALUES (1)
> SELECT *
> FROM TESTDEFAULTS
> ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
> ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
> INSERT INTO TESTDEFAULTS
> DEFAULT VALUES
>
> SELECT *
> FROM TESTDEFAULTS
> --This will give an error now
> INSERT INTO TestDefaultS VALUES (NULL)
> DROP TABLE TESTDEFAULTS
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
Alter column to set default
I know that the correct syntax to set the default on a column in SQL Server 2005 is:
Alter Table <TableName> Add Constraint <ConstraintName> Default <DefaultValue> For <ColumnName>
But from what I can gather, the SQL-92 syntax is:
Alter Table <TableName> Alter Column <ColumnName> Set Default <DefaultValue>
This generates an error on SQL Server 2005.
Am I wrong about the standard syntax for this statement? If this is the standard, why doesn't SQL Server 2005 support it? I am trying to avoid code that will only work on certain database managers.
Thanks.
SQL Server in only entry level SQL-92 compliant. There are however some features that are full level and so on. The alter table syntax to add a default is not supported yet. If you want to use code that works on various database systems then it is best to stick to CREATE TABLE DDL with basic SQL-92 syntax. This will have a better chance of executing against more database systems. So define the defaults/constraints etc as part of the CREATE TABLE itself. You have to use ALTER TABLE on a case-by-case basis. The syntax differences are huge between ANSI SQL standard, SQL Server, Oracle and DB2 for various DDLs.alter column to not null that has null values
I have to change numeric columns in 2005 table to not null and default value 0.
What I usually do is an update on the columns setting value to 0 where is null. I know you can use 'with values' when adding a column with default 0 and not null to an existing table.
Can something like this be done for altering a column or do I need to do the update?
Thanks
You need to use UPDATE first and then ALTER. ALTER TABLE table ALTER COLUMN only supports changing the type definition, collation and nullability.Alter Column Set Default
I've done some research on setting the default value of a column and found the following code:
ALTER TABLE [table1] ALTER COLUMN [column1] SET DEFAULT 0
I am trying to set column1 within table1 to a default value of 0, column1 is an int data type. But it doesn't work within MS SQL SERVER 2000.
Can anyone tell me what's wrong?what you want is this:
ALTER TABLE table1
ADD CONSTRAINT DF_table1_column1
DEFAULT 0 FOR column1|||Thanks very much! It's appreciated.
ALTER TABLE [table1] ALTER COLUMN [column1] SET DEFAULT 0
I am trying to set column1 within table1 to a default value of 0, column1 is an int data type. But it doesn't work within MS SQL SERVER 2000.
Can anyone tell me what's wrong?what you want is this:
ALTER TABLE table1
ADD CONSTRAINT DF_table1_column1
DEFAULT 0 FOR column1|||Thanks very much! It's appreciated.
Saturday, February 25, 2012
Alter Column datatype with Default constraint
I need to alter the datatype of a column from smallint to decimal (14,2) but the column was originally created with the following:
alter my_table
add col_1 smallint Not Null
constraint df_my_table__col_1 default 0
go
I want to keep the default constraint, but i get errors when I try to do the following to alter the datatype:
alter table my_table
alter column col_1 decimal(14,2) Not Null
go
Do I need to drop the constraint before I alter the column and then rebuild the constraint? An example would be helpful.
Thxyes thats right,
the constraint has a dependency on the column and hence the data type of the column.
If you change the data type then you change the column and then this affects the constraint which SQL Server will not allow.
drop the constriant, then do what you need to do to the column
Cheers
alter my_table
add col_1 smallint Not Null
constraint df_my_table__col_1 default 0
go
I want to keep the default constraint, but i get errors when I try to do the following to alter the datatype:
alter table my_table
alter column col_1 decimal(14,2) Not Null
go
Do I need to drop the constraint before I alter the column and then rebuild the constraint? An example would be helpful.
Thxyes thats right,
the constraint has a dependency on the column and hence the data type of the column.
If you change the data type then you change the column and then this affects the constraint which SQL Server will not allow.
drop the constriant, then do what you need to do to the column
Cheers
Subscribe to:
Posts (Atom)