Saturday, February 25, 2012

ALTER COLUMN

Hi All,
I'm trying to use the ALTER TABLE....ALTER COLUMN command to drop the
DEFAULT attribute from a table. I have 2 questions:
1. I try to alter multiple columns in one call but it never works. I get
error messages. Do you need to use multiple ALTER TABLE calls to modify
multiple columns?
2. How to drop the DEFAULT attribute of a column?
Thank you for your help.
Conway1. Pretty sure you will need to do that in multiple calls, you can put it
in a tran if you need to
2. You find the name of the object if you dont know it:
create table test
(
value1 varchar(10) default (10),
value2 varchar(10)
)
go
select name as column_name, object_name(cdefault)
from syscolumns
where cdefault <> 0
Then:
ALTER TABLE test
DROP Constraint DF__test__value1__78B3EFCA
Your name will be different...
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Conax" <ConaxLiu@.hotmail.com> wrote in message
news:eCUeNWgoFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I'm trying to use the ALTER TABLE....ALTER COLUMN command to drop the
> DEFAULT attribute from a table. I have 2 questions:
> 1. I try to alter multiple columns in one call but it never works. I get
> error messages. Do you need to use multiple ALTER TABLE calls to modify
> multiple columns?
> 2. How to drop the DEFAULT attribute of a column?
> Thank you for your help.
> Conway
>|||Here is a slightly modified example from BOL on dropping multiple columns in
a single call
CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL
,column_c varchar(20) null)
GO
ALTER TABLE doc_exb DROP COLUMN column_b,column_c
GO
go
EXEC sp_help doc_exb
GO
DROP TABLE doc_exb
GO
HTH...
http://zulfiqar.typepad.com
BSEE, MCP
"Louis Davidson" wrote:

> 1. Pretty sure you will need to do that in multiple calls, you can put it
> in a tran if you need to
> 2. You find the name of the object if you dont know it:
> create table test
> (
> value1 varchar(10) default (10),
> value2 varchar(10)
> )
> go
> select name as column_name, object_name(cdefault)
> from syscolumns
> where cdefault <> 0
> Then:
> ALTER TABLE test
> DROP Constraint DF__test__value1__78B3EFCA
> Your name will be different...
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Conax" <ConaxLiu@.hotmail.com> wrote in message
> news:eCUeNWgoFHA.2484@.TK2MSFTNGP15.phx.gbl...
>
>|||On Tue, 16 Aug 2005 13:55:15 +1200, "Conax" <ConaxLiu@.hotmail.com> wrote:
in <eCUeNWgoFHA.2484@.TK2MSFTNGP15.phx.gbl>

>Hi All,
>I'm trying to use the ALTER TABLE....ALTER COLUMN command to drop the
>DEFAULT attribute from a table. I have 2 questions:
>1. I try to alter multiple columns in one call but it never works. I get
>error messages. Do you need to use multiple ALTER TABLE calls to modify
>multiple columns?
>2. How to drop the DEFAULT attribute of a column?
>Thank you for your help.
>Conway
>
I think you want this if you want to drop the default constraint on multiple
columns at once.
ALTER TABLE tablename DROP
CONSTRAINT DF_firstconstraintname,
CONSTRAINT DF_secondconstraintname
Stefan Berglund|||Yes, droping stuff work with multiple columns/constraints, but alter will
not (though I might be wrong, but I don't thik so)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"ZULFIQAR SYED" <DRSQLnospam2005@.hotmail.com> wrote in message
news:C2BDF777-720A-4CF0-90FA-ADFDB680B75F@.microsoft.com...
> Here is a slightly modified example from BOL on dropping multiple columns
> in
> a single call
> CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL
> ,column_c varchar(20) null)
> GO
> ALTER TABLE doc_exb DROP COLUMN column_b,column_c
> GO
> go
> EXEC sp_help doc_exb
> GO
> DROP TABLE doc_exb
> GO
> HTH...
> --
> http://zulfiqar.typepad.com
> BSEE, MCP
>
> "Louis Davidson" wrote:
>|||Thanks to all who replied.
Unfortunately I won't know the constrant name beforehand so I can't really
DROP CONSTRAINT name...
The thing is, I work on development server, and then pass a SQL script to
another person to update the live server. I cannot be 100% sure that the
constraint names on the live server will be exactly same as the test server.
Therefore, looks like we will have to manually delete the defaults from
Enterprise Manager.
But thanks again for your kind helps.
Regards,
Conway
"Conax" <ConaxLiu@.hotmail.com> wrote in message
news:eCUeNWgoFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I'm trying to use the ALTER TABLE....ALTER COLUMN command to drop the
> DEFAULT attribute from a table. I have 2 questions:
> 1. I try to alter multiple columns in one call but it never works. I get
> error messages. Do you need to use multiple ALTER TABLE calls to modify
> multiple columns?
> 2. How to drop the DEFAULT attribute of a column?
> Thank you for your help.
> Conway
>|||>> I cannot be 100% sure that the constraint names on the live server will
Why, were they built using different scripts? Why?
If you want control over this, you should only deploy scripts where
constraints are explicitly defined and named, and deploy the same script(s)
to all environments. Then you can run the same script in both places and it
will always work.
> Therefore, looks like we will have to manually delete the defaults from
> Enterprise Manager.
Why? You can generate this script easily, but please see my point above.
This will generate a script to drop all PK, FK and UQ constraints:
SELECT 'ALTER TABLE '+TABLE_NAME+' DROP CONSTRAINT '+CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
And this will get the defaults:
SELECT 'ALTER TABLE '+t.TABLE_NAME+' DROP CONSTRAINT '+o.name
FROM sysconstraints c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON OBJECT_ID(t.TABLE_SCHEMA+'.'+t.TABLE_NAME) = c.id
INNER JOIN sysobjects o
ON o.id = c.constid
WHERE o.type='D'
Once you generate these alter statements, you can copy them all to the top
pane and run them (and if you want, you can browse through them and remove
the ones you don't want to execute).|||Thanks a lot Aaron. Your information is pretty useful. Much appreciated.
Conway
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eNsGr0yoFHA.3552@.TK2MSFTNGP10.phx.gbl...
> Why, were they built using different scripts? Why?
> If you want control over this, you should only deploy scripts where
> constraints are explicitly defined and named, and deploy the same
script(s)
> to all environments. Then you can run the same script in both places and
it
> will always work.
>
> Why? You can generate this script easily, but please see my point above.
> This will generate a script to drop all PK, FK and UQ constraints:
> SELECT 'ALTER TABLE '+TABLE_NAME+' DROP CONSTRAINT '+CONSTRAINT_NAME
> FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> And this will get the defaults:
> SELECT 'ALTER TABLE '+t.TABLE_NAME+' DROP CONSTRAINT '+o.name
> FROM sysconstraints c
> INNER JOIN INFORMATION_SCHEMA.TABLES t
> ON OBJECT_ID(t.TABLE_SCHEMA+'.'+t.TABLE_NAME) = c.id
> INNER JOIN sysobjects o
> ON o.id = c.constid
> WHERE o.type='D'
> Once you generate these alter statements, you can copy them all to the top
> pane and run them (and if you want, you can browse through them and remove
> the ones you don't want to execute).
>

No comments:

Post a Comment