Dropping table Column in SQL server 6.5

I'm trying to drop a table column in SQL Server 6.5. I used the following
command and got error:
ALTER TABLE tablename
DROP COLUMN columnname
GO
It works in SQL Server 2000 version
Please I need help.
Thanks.
Ebon.
Hi,
You cant delete a column in sql 6.5
Only way is :-
1. put the data into a new table (select * into table_backup from
real_table)
2. script the table and dependant objetcs
3. change the table script with out the unwanted column
4. Insert into table from table_backup
5. create dependant objects , indexes..
Thanks
Hari
MCDBA
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> I'm trying to drop a table column in SQL Server 6.5. I used the following
> command and got error:
> ALTER TABLE tablename
> DROP COLUMN columnname
> GO
> It works in SQL Server 2000 version
> Please I need help.
> Thanks.
> Ebon.
>
|||Many thanks! Hari.
Egbon.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtjNXHFoEHA.2588@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi,
> You cant delete a column in sql 6.5
> Only way is :-
> 1. put the data into a new table (select * into table_backup from
> real_table)
> 2. script the table and dependant objetcs
> 3. change the table script with out the unwanted column
> 4. Insert into table from table_backup
> 5. create dependant objects , indexes..
> Thanks
> Hari
> MCDBA
> "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
following
>

Dropping table Column in SQL server 6.5

I'm trying to drop a table column in SQL Server 6.5. I used the following
command and got error:
ALTER TABLE tablename
DROP COLUMN columnname
GO
It works in SQL Server 2000 version
Please I need help.
Thanks.
Ebon.Hi,
You cant delete a column in sql 6.5
Only way is :-
1. put the data into a new table (select * into table_backup from
real_table)
2. script the table and dependant objetcs
3. change the table script with out the unwanted column
4. Insert into table from table_backup
5. create dependant objects , indexes..
Thanks
Hari
MCDBA
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> I'm trying to drop a table column in SQL Server 6.5. I used the following
> command and got error:
> ALTER TABLE tablename
> DROP COLUMN columnname
> GO
> It works in SQL Server 2000 version
> Please I need help.
> Thanks.
> Ebon.
>|||Many thanks! Hari.
Egbon.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtjNXHFoEHA.2588@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You cant delete a column in sql 6.5
> Only way is :-
> 1. put the data into a new table (select * into table_backup from
> real_table)
> 2. script the table and dependant objetcs
> 3. change the table script with out the unwanted column
> 4. Insert into table from table_backup
> 5. create dependant objects , indexes..
> Thanks
> Hari
> MCDBA
> "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> > I'm trying to drop a table column in SQL Server 6.5. I used the
following
> > command and got error:
> >
> > ALTER TABLE tablename
> > DROP COLUMN columnname
> > GO
> >
> > It works in SQL Server 2000 version
> > Please I need help.
> >
> > Thanks.
> >
> > Ebon.
> >
> >
>

Dropping An Indexed Column

I have inherited a table with dozens of columns that I no longer want. I want to drop these columns.

So I tried
"ALTER TABLE mydata DROP BLOCK_ID"

and it get an error of: cannot delete a field that is part of an index. How do I get around this?

(BLOCK_ID is the field name of my indexed column)
(The non-indexed ones drop fine.)First remove the column you want to drop from all the indexes that refer it. If there are indexes that refer only that column just drop those. Then you can drop the column.

Cheers,
Suren.|||Yes, I get that I have to drop the index(es) -- but how do I find out which indexes this column is in?

I'm building up to write some scripts to automatically drop a long-list of unwanted columns - how does one go about figuring out what index a field is in? And/or is there a sql way of saying "drop this column and it's indexes" ?|||Well to do that the mist easiest way is to use a graphical tool that organise indexes unser each table and to go through the index and remove the coloms.

If you are thinking of writing scripts then you should select from catalog tables such as user_indexes and user_inx_cols. I think I got the names correct.

Dropping an auto numbered primary key and add a new one

The table I am using have a column called Key which is the primary key of the table and a auto number. This primary key is not a foreign key in any other table.

I need to write SQL to drop the current primary key and add a new one Say "RecordId"

as the new primary key and which should be a autonumber too.

any idea.

thanks in advance

droping and adding a primary key on a column wont affest its 'identity' property, so simply drop the primary key and add again with the new name u want...

alter table tablename drop constraint oldpk

alter table tablename add constraint newpk primary key(id)

|||

What's the point? It is easy enough, just drop the column and add another one, but there might be an easier way to do what you are wanting to do.

Here is a script that does it:

set nocount on
drop table test
go
create table test
(
testId int identity constraint PKtest primary key,
value datetime default (getdate())
)
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
go
select *
from test
go
alter table test
drop PKtest
go
alter table test
drop column testId
go
select *
from test
go
alter table test
add testId int identity (2,2) constraint PKtest primary key
go
select *
from test --the identity values will start at 2 and go up by steps of 2 (so you can see the diff)

sql

Dropped column being "dropped" before drop ??

(SQL Server 2000 SP3, Developer Edition, Windows XP Professional)
I am getting an error "Invalid column name 'sys_login_name'." when running
the following query. The query is a minor conversion of a table between
versions. One column (userid_fk) had been added in a previous batch. In
this batch several columns are being dropped IF they exist. In the first
column 'sys_login_name', before it is dropped, data is pulled in from
another table (sy_user) before the sys_user.sys_login_name is dropped. I've
used col_length() as a quick way to detect if a column exists (returns null
if it doesn't exist). In the code below, note the section where "if
col_length('sys_user', 'sys_login_name') is not null" which means it only
gets executed when sys_login_name DOES exist. The UPDATE statement in that
IF block pulls data into sys_user from sy_user based upon the sys_login_name
field. The next statement then DROPS the sys_login_name field. In Query
Analyzer, I'm getting an "Invalid column name 'sys_login_name'" error that
points back to the UPDATE statement, but the rest of the batch is executing.
The output from Query Analyzer is:
=====OUTPUT
START===================================
====================================
====
doing v2->v3 on sys_user
updating sys_user
dropping sys_login_name
dropping other columns
Server: Msg 207, Level 16, State 3, Line 14
Invalid column name 'sys_login_name'.
=====OUTPUT
END=====================================
====================================
==
Oddly enough, the error is after the PRINT statement's output, but I've seen
that asynchronous-ness (?) of PRINT and error output enough before to not be
alarmed.
Here's the batch:
=====BATCH
START===================================
====================================
====
-- v2->v3: Check if sy integration changes need to be done STEP 2: convert
and drop fields
if dbo.fn_sy_get_table_version(N'sys_user') = 2
begin
print 'doing v2->v3 on sys_user'
-- if sys_login_name exists, pull data from sy_user for conversion
-- and drop sys_login_name
if col_length('sys_user', 'sys_login_name') is not null
begin
-- fill in userid_fk from sy_user.userid_pk via login name
-- and set password to 'test' for all accounts
-- before dropping login name; entry may not exist in sy_user
print 'updating sys_user'
update sys_user
set userid_fk = isnull(SY.userid_pk, 0),
sys_password = '098f6bcd4621d373cade4e832627b4f6'
from sys_user SYS left join sy_user SY on SYS.sys_login_name =
SY.login
print 'dropping sys_login_name'
alter table sys_user drop column sys_login_name
end
print 'dropping other columns'
-- drop sys_user_first if it exists
if col_length('sys_user', 'sys_user_first') is not null
alter table sys_user drop column sys_user_first
-- drop sys_user_last if it exists
if col_length('sys_user', 'sys_user_last') is not null
alter table sys_user drop column sys_user_last
-- drop timestamp if it exists
if col_length('sys_user', 'timestamp') is not null
alter table sys_user drop column [timestamp]
exec sp_sy_addextprops N'PPD_Version', 3, N'USER', N'dbo', N'TABLE',
N'sys_user'
end
go
=====BATCH
END=====================================
===================================
Here's a sample I did to test to see if ALTER TABLE DROP COLUMN somehow gets
executed before the UPDATE, but it doesn't:
=====SAMPLE
START===================================
====================================
=
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
insert testdrop (col1, col2) values (1,2)
update testdrop set col2 = 22 where col1=1
select * from testdrop
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
go
=====SAMPLE
END=====================================
===================================
Thanks for any help!
Mike JansenOK, I was able to reproduce the problem by enhancing my sample:
========= BEGIN SAMPLE ================
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
create table testdrop2
(
pk int identity(100,1) not null primary key,
col1 int null
)
go
insert testdrop2 (col1) values (1)
insert testdrop2 (col1) values (2)
insert testdrop (col1, col2) values (1,0)
insert testdrop (col1, col2) values (2,0)
insert testdrop (col1, col2) values (3,0)
select * from testdrop
update testdrop
set col2 = T2.pk
from testdrop T1 left join testdrop2 T2 on T1.col1=T2.col1
select * from testdrop
--go
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
drop table testdrop2
go
========= END SAMPLE ====================
Note that if you uncomment the one GO statement, it works. If the ALTER
TABLE DROP COLUMN is in the same batch as the UPDATE with the JOIN in the
FROM clause, it has the error.
Thanks,
Mike|||Does anyone have any idea about the following problem with the UPDATE
statement not working (get "Invalid Column" error) when you use a column in
the UPDATE's FROM clause (in a JOIN) and then drop that column via ALTER
TABLE in the same batch?
I can work around the problem, but I'd like to know if this is a SQL bug or
if I am ignorant of something fundamental in SQL Server (working with the
guys I work with, I had to qualify what I might be ignorant about or they
might pipe in all too quickly to confirm that I'm just ignorant <g> )
Thanks,
Mike
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:ek5KSIaVFHA.1508@.tk2msftngp13.phx.gbl...
> OK, I was able to reproduce the problem by enhancing my sample:
> ========= BEGIN SAMPLE ================
> create table testdrop
> (
> ident int identity(100,1) not null primary key,
> col1 int null,
> col2 int null
> )
> go
> create table testdrop2
> (
> pk int identity(100,1) not null primary key,
> col1 int null
> )
> go
> insert testdrop2 (col1) values (1)
> insert testdrop2 (col1) values (2)
> insert testdrop (col1, col2) values (1,0)
> insert testdrop (col1, col2) values (2,0)
> insert testdrop (col1, col2) values (3,0)
> select * from testdrop
> update testdrop
> set col2 = T2.pk
> from testdrop T1 left join testdrop2 T2 on T1.col1=T2.col1
> select * from testdrop
> --go
> alter table testdrop drop column col2
> select * from testdrop
> go
> drop table testdrop
> drop table testdrop2
> go
> ========= END SAMPLE ====================
> Note that if you uncomment the one GO statement, it works. If the ALTER
> TABLE DROP COLUMN is in the same batch as the UPDATE with the JOIN in the
> FROM clause, it has the error.
> Thanks,
> Mike
>|||Dropping the column forces a recompile of the entire batch. That's why
you get an error. It's the expected behaviour.
For this and other reasons try to keep DDL and DML code entirely
separate. Put your ALTER statements in a separate batch.
David Portas
SQL Server MVP
--|||1. If it's recompiling the batch because of the DDL, why does my "simple
sample" work where I have an UPDATE with no FROM clause but I SET the column
and then drop it in the next line with an ALTER TABLE? If it were
recompiling the batch, I'd think that it would fail on that as well.
Here's a re-post of my simple sample that works:
==== BEGIN SAMPLE ==============
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
insert testdrop (col1, col2) values (1,2)
update testdrop set col2 = 22 where col1=1
select * from testdrop
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
go
==== END SAMPLE ==============
2. What are the other reasons for putting DDL and DML in separate batches
(besides the re-compile issue)?
Thanks for your help,
Mike
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1115815055.958304.192040@.g47g2000cwa.googlegroups.com...
> Dropping the column forces a recompile of the entire batch. That's why
> you get an error. It's the expected behaviour.
> For this and other reasons try to keep DDL and DML code entirely
> separate. Put your ALTER statements in a separate batch.
> --
> David Portas
> SQL Server MVP
> --
>|||I did a little research and found the answer to question #2 (What are the
other reasons for putting DDL and DML in separate batches - besides the
re-compile issue): It's related to the re-compile issue: performance. The
recompilation obviously causes performance issues. Since the compilation of
the batches is probably 1% or less of the time in the scenario I'm talking
about and it's a once-in-a-while script, that isn't really a significant
factor. I did end up changing my script though to put the DDL and DML in
separate batches since I'm still getting the "invalid column" error -- which
probably is related to the re-compiling (perhaps in the simple example
something is optimized in such a way that the batch didn't need to be
re-compiled ')
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:O1nV6niVFHA.2420@.TK2MSFTNGP12.phx.gbl...
> 1. If it's recompiling the batch because of the DDL, why does my "simple
> sample" work where I have an UPDATE with no FROM clause but I SET the
column
> and then drop it in the next line with an ALTER TABLE? If it were
> recompiling the batch, I'd think that it would fail on that as well.
> Here's a re-post of my simple sample that works:
> ==== BEGIN SAMPLE ==============
> create table testdrop
> (
> ident int identity(100,1) not null primary key,
> col1 int null,
> col2 int null
> )
> go
> insert testdrop (col1, col2) values (1,2)
> update testdrop set col2 = 22 where col1=1
> select * from testdrop
> alter table testdrop drop column col2
> select * from testdrop
> go
> drop table testdrop
> go
> ==== END SAMPLE ==============
> 2. What are the other reasons for putting DDL and DML in separate batches
> (besides the re-compile issue)?
>
> Thanks for your help,
> Mike
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1115815055.958304.192040@.g47g2000cwa.googlegroups.com...
>

Droping/Removing Identity from a Column

I want to remove Identity from a column within my table. I know that I can do it via the Enterprise Manager. But I need to remove it via a script. Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.
Sorry, you can't add or remove the identity property through ALTER TABLE.
You can do what enterprise manager does behind the scenes, or a slight
variation:
- add an INT column, move the data, drop the old column, rename the new
column
- create a new table, move the data over, drop the old table, rename the new
table
I'll leave it as an exercise to the reader to figure out which is which...
but both will accomplish the goal.
http://www.aspfaq.com/
(Reverse address to reply.)
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
> I want to remove Identity from a column within my table. I know that I
can do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
> I've tried the following:
> Alter Table FX_Operator_List drop Identity Operator_ID
> go
> In the above statment I want to drop "Identity" from column "Operator_ID"
> in the table "FX_Operator_List".
> I can't get it to work.
|||Behind the scenes, EM re-creates the table without the identity property.
You'll have to do the same.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.
sql

Droping/Removing Identity from a Column

I want to remove Identity from a column within my table. I know that I can do it via the Enterprise Manager. But I need to remove it via a script. Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.Sorry, you can't add or remove the identity property through ALTER TABLE.
You can do what enterprise manager does behind the scenes, or a slight
variation:
- add an INT column, move the data, drop the old column, rename the new
column
- create a new table, move the data over, drop the old table, rename the new
table
I'll leave it as an exercise to the reader to figure out which is which...
but both will accomplish the goal.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
> I want to remove Identity from a column within my table. I know that I
can do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
> I've tried the following:
> Alter Table FX_Operator_List drop Identity Operator_ID
> go
> In the above statment I want to drop "Identity" from column "Operator_ID"
> in the table "FX_Operator_List".
> I can't get it to work.|||Behind the scenes, EM re-creates the table without the identity property.
You'll have to do the same.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.

Droping/Removing Identity from a Column

I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script. Do
es any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.Sorry, you can't add or remove the identity property through ALTER TABLE.
You can do what enterprise manager does behind the scenes, or a slight
variation:
- add an INT column, move the data, drop the old column, rename the new
column
- create a new table, move the data over, drop the old table, rename the new
table
I'll leave it as an exercise to the reader to figure out which is which...
but both will accomplish the goal.
http://www.aspfaq.com/
(Reverse address to reply.)
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
> I want to remove Identity from a column within my table. I know that I
can do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
> I've tried the following:
> Alter Table FX_Operator_List drop Identity Operator_ID
> go
> In the above statment I want to drop "Identity" from column "Operator_ID"
> in the table "FX_Operator_List".
> I can't get it to work.|||Behind the scenes, EM re-creates the table without the identity property.
You'll have to do the same.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.

droping a CONSTRAINT

I am having problem to find the right syntax to DROP a column with contrainst and recrate it
I get an error

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='myTable'
and COLUMN_NAME='myDate' )
ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
GO

ALTER TABLE [dbo].[myTable] WITH NOCHECK ADD
myDate datetime CONSTRAINT [DF_myDate] DEFAULT (GetDate())
GO

Query Analyser says :
Server: Msg 5074, Level 16, State 1, Line 5
The object 'DF_myDate' is dependent on column 'myDate'.
Server: Msg 4922, Level 16, State 1, Line 5
ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.
Server: Msg 2705, Level 16, State 4, Line 2
Column names in each table must be unique. Column name 'myDate' in table 'dbo.myTable' is specified more than once.

thank you for helpingYou do know that the first statement failed, and since it was isolated in it's own batch by the GO, the second statement tried to run. So that's uderstandable, since the column did not drop, you can't re-add it.

Do this, go in to Enterprise Manager, right click on the table and chose design make your changes, DON'T SAVE them, and click on the save script icon

It will show you exactly what to do|||I get an incredible script !!

I really dont understant
here I dop the column

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='myTable'
and COLUMN_NAME='myDate' )
ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
GO

then it doesnt exist any more ! why I cannot create it after ?|||because u failed to drop it....... u got error message :
Server: Msg 5074, Level 16, State 1, Line 5
The object 'DF_myDate' is dependent on column 'myDate'.
Server: Msg 4922, Level 16, State 1, Line 5
ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.

u cant drop the column, coz it has constraint 'DF_myDate'. drop the constraint first, then drop the column, then create the column with constraint again.

Dropdownlist in Textfield like Excel 'autofilter'

I'd like to build a dropdownlist in the texfield which describes the columns. This dropdownlist should show all distinct values of the column like the 'autofilter' in excel. This sholuld be done directly in the report not as a parameter in the head. Any ideas ?
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=fb75b6199de248a2988f407b48fc9791
*****************************************Hi
This really interests me 2. Have you already received feedback on this issue?
Koen
"Psycho Dad via SQLMonster.com" wrote:
> I'd like to build a dropdownlist in the texfield which describes the columns. This dropdownlist should show all distinct values of the column like the 'autofilter' in excel. This sholuld be done directly in the report not as a parameter in the head. Any ideas ?
> *****************************************
> * This message was posted via http://www.sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> * http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=fb75b6199de248a2988f407b48fc9791
> *****************************************
>sql

drop not null

hi

i'm using mssql server 2000

i want to remove a not null column constraint from the column
answertext in table answertext.

i tried the following line
ALTER TABLE AnswerText ALTER COLUMN AnswerText DROP NOT NULL;

it didn't work and this error message apeared (sorry about the german)
Server: Nachr.-Nr. 156, Schweregrad 15, Status 1, Zeile 6
Falsche Syntax in der Nhe des NOT-Schlsselwortes.

is there a way to drop not null column constraint or do i have to save
the data, drop the table and recreate it?

i created the table with the following ddl code
CREATE TABLE AnswerText(
...
,AnswerText VARCHAR(512) NOT NULL
...
)

tanks for your helpcreate table foo (blah varchar(10) not null)

alter table foo alter column blah varchar(10)null

I found the best way to learn stuff like this is either find it in BOL
or run prfiler on myself and do it in enterprise manager and then look
at the syntax.

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Drop identitycolumn

hi.

i want to drop identity column in a table but not though design view, so plz tell the script for that.

Unfortunately, you cannot 'just' remove the IDENTITY property of a column. You have to completely remove the column.

Code Snippet

ALTER TABLE MyTable

DROP COLUMN MyColumn

If you wish to keep the values in the column, and just remove the IDENTITY property, you must first add another column, copy the existing values to the new column, and then DROP the existing IDENTITY column.

|||

This resource might be helpful:

http://www.bennadel.com/index.cfm?dax=blog:24.view

Drop Identity property of a column

Is there a way to remove the Identity property of a column in SQL Server 2000?

The statement:

<code>

ALTER TABLE <table name> ALTER COLUMN <column name> DROP IDENTITY

</code>

returns a syntax error.

Thank you,

Jeff

T-SQL's ALTER TABLE statement doesn't support dropping the IDENTITY property in SQL Server2000 or 7.0. Your only option for deleting an IDENTITY column is tocreate a new table structure without the IDENTITY column, then copy thedata into this structure.|||

Thanks Darrell.

Jeff

|||

One more question. Why will SQL Server 2000 allow you to delete the identity property in the designer, but not script the same change?

Jeff

|||you can delete the identity property but you would still have the numbers already generated in the column. New number wouldnt be generated though.|||

Dinakar,

I actually want to do that. I need to turn off the identity property via script, copy data into the table, then turn the identity property back on. I want all the data to stay in the identity column, just not auto-increment during the copy.

Jeff

|||

In that case you can copy all the columns xcept the Identity column into the new table. Then add the Identity column to the new table.

|||In that case you don't really need to drop the column, you're justturning it off. I thought you wanted to remove the IDENTITYcolumn forever.
|||

DarrellNorton wrote:

In that case you don't really need to drop the column, you're justturning it off. I thought you wanted to remove the IDENTITYcolumn forever.

But you can't just turn it off. With SQL Server you would have tocreate a new non-identity column, populate it with the data from theidentity column, then remove the idenitity column. If you need tohave to maintain the same column name then you have to add in somecolumn renaming goodness.

|||

Hello...

from search:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=482&lngWId=5
do test before go to production with big rows table
i just tested with couple of rows in my test table...

-- ----------

USE pubs
GO

EXEC sp_configure 'allow update', '1'
RECONFIGURE WITH OVERRIDE
GO

DECLARE @.col varchar(128), @.table varchar(128)

-- for find identity column (if colstat =1 then identity is on)
SELECT @.col=name
FROM syscolumns
WHERE id=OBJECT_ID('pubs..test') AND (colstat & 1 <> 0)

-- SELECT @.col
IF (@.col IS NOT null) BEGIN

UPDATE syscolumns
SET colstat = colstat ^ 1
WHERE id = OBJECT_ID('pubs..test') and name = @.col

END

EXEC sp_configure 'allow update', '0'
RECONFIGURE WITH OVERRIDE
GO
-- ----------

|||But actually you can just turn the identity property off. You can't just turn it off in code. You can turn it off in the designer but not in the code. This must be a bug in SQL Server 2000 because all functionality in the table designer should be scriptable.
Jeff|||hello...
to set identity column on/off during the insertion.
this is the way,

SET IDENTITY_INSERT [database.[owner.] ] {table} { ON | OFF }
from Books Online:

Remarks

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft? SQL Server? returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

Drop identity propert

Can use this staement
Alter table |<table name>
alter column <Column name>
drop identity
DB2 allows this....
How can I drop identity propery of a column with out first moving the data
out from that table and later getting the data back in that same table.
--
Sr DBA
Pier 1 Imports
mabbas@.Pier1.comYou cannot remove the identity property. If you use the GUI, you will see that it creates a new
table, copy data etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Moh" <mabbas@.Pier1.com> wrote in message news:C8DE380E-B90F-4039-B174-92ED35BB3FC9@.microsoft.com...
> Can use this staement
> Alter table |<table name>
> alter column <Column name>
> drop identity
> DB2 allows this....
> How can I drop identity propery of a column with out first moving the data
> out from that table and later getting the data back in that same table.
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com|||Moh
CREATE TABLE #T (c INT NOT NULL IDENTITY(1,1),c1 CHAR(1))
GO
INSERT INTO #T (c1) VALUES ('c')
GO
ALTER TABLE #T DROP COLUMN c
DROP TABLE #T
"Moh" <mabbas@.Pier1.com> wrote in message
news:C8DE380E-B90F-4039-B174-92ED35BB3FC9@.microsoft.com...
> Can use this staement
> Alter table |<table name>
> alter column <Column name>
> drop identity
> DB2 allows this....
> How can I drop identity propery of a column with out first moving the data
> out from that table and later getting the data back in that same table.
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com

drop IDENTITY attribute

Is there any way to remove IDENTITY column attribute without dropping the column?i have not come accross any method other than doing it thru enterprise manager.|||I knew about that but in this case SQL Server drops the table and recreates it with the new description (including indexes, constraints, triggers, etc.). - a hell of a script - checked with Profiler
I thought it might be another way to do it smoothly.

Thanks anyway,

Originally posted by rohitkumar
i have not come accross any method other than doing it thru enterprise manager.|||you can do "alter table tbl drop column column_name" and then add a column without the identity. the only problem is it will append the column at the end of the field list. i think yukon will have the ability to drop/add properties.

DROP IDENTITY ?

Is there a way to drop the Identity property of a column in a table using an
Alter Table?
I would like to do it in Query Analyzer, not EM, and I do not want to have
to create a new table and copy the data to the new table (This is how EM
performs this operation.)
I'm looking for some kind of ALTER TABLE or a system procedure. Is it
possible?
(BTW - I don't like the idea of changing the STATUS bit in syscolumns with
an UPDATE syscolumns T-SQL either.)
Thanksdrop the column and recreate it without identity property.
Ex:
create table i(i int not null identity, ii varchar(6000))
go
alter table i drop column i
go
alter table i add i int
go
Note: you can explicitly insert the values into identity column of a table by doing session level
setting of IDENTITY_INSERT.
Ex:
SET IDENTITY_INSERT <table> ON
- Vishal|||You can drop the column if you don't care about losing the
data, otherwise you may need to use EM.
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>Is there a way to drop the Identity property of a column
in a table using an
>Alter Table?
>I would like to do it in Query Analyzer, not EM, and I do
not want to have
>to create a new table and copy the data to the new table
(This is how EM
>performs this operation.)
>I'm looking for some kind of ALTER TABLE or a system
procedure. Is it
>possible?
>(BTW - I don't like the idea of changing the STATUS bit
in syscolumns with
>an UPDATE syscolumns T-SQL either.)
>Thanks
>
>.
>|||cw3,
There is no option in the ALTER TABLE command to drop an IDENTITY property.
If you want to preserve the data in the identity column, include a copy
step. Here's a slight modification of Vishal's solution:
create table MyTable (i int not null identity, ii varchar(6000))
go
insert MyTable values ('X')
insert MyTable values ('Y')
go
alter table MyTable add i2 int
go
update MyTable set i2 = i
go
alter table MyTable drop column i
go
alter table MyTable add i int
go
update MyTable set i = i2
go
alter table MyTable drop column i2
The main disadvantage of this approach is that the column order may not be
what you wanted, because ALTER TABLE puts new columns at the end.
To control column order, you need to do something like what Enterprise
Manager does. You can get the EM script and tweak it yourself before
applying it.
Ron
--
Ron Talmage
SQL Server MVP
"cw3" <cw@.3mc.com> wrote in message
news:ugM9gSEjDHA.1964@.TK2MSFTNGP12.phx.gbl...
> Is there a way to drop the Identity property of a column in a table using
an
> Alter Table?
> I would like to do it in Query Analyzer, not EM, and I do not want to have
> to create a new table and copy the data to the new table (This is how EM
> performs this operation.)
> I'm looking for some kind of ALTER TABLE or a system procedure. Is it
> possible?
> (BTW - I don't like the idea of changing the STATUS bit in syscolumns with
> an UPDATE syscolumns T-SQL either.)
> Thanks
>

Drop Default values in Columns

I have Column A and Column B in my Table they have Default values 'A' and 0 respectively.

I want to alter table.

I wrote

ALTER TABLE EMPLOYEE

DROP DEFAULT FOR COLUMN A,

DROP DEFAULT FOR COLUMN B

GO

It does not work. I am new to Sql Server. Can you help me how to write alter table statement for dropping those default values.

I will really appreciate it.

Nature:

Run an SP_HELP on your table:

sp_help employee

and look for something like this:

-- constraint_type
-- -
-- DEFAULT on column a

-- constraint_name
-- -
-- DF__EMPLOYEE__A__461FBB34

And then execute something like this:

alter table dropDefault
drop constraint DF__EMPLOYEE__A__461FBB34

Dave

|||Sorry, that table name in the DROP statement must be EMPLOYEE and not "dropDefault"|||

Mugambo wrote:

Sorry, that table name in the DROP statement must be EMPLOYEE and not "dropDefault"

You can edit your posts...|||

Thanks, Phil, I keep forgetting. PLEASE keep reminding me about this until I get it right.

Dave

Drop Column with default constraint in T-SQL

Hello,
I'm making an SQL script that has to drop some columns. The problem is that
the column has a default value and therefore I can't use the DROP COLUMN
directly. I've got to drop the 'default' constraint, but the problem is that
we have to use this script on different database and then the constraint nam
e
is not always the same.
(example:)
DB1 -> DF_COLUMNX_ddf87d67s68
DB2 -> DF_COLUMNX_ddf79djks90
I know how to get the Constraint name but I can't use that as a variable in
the DROP CONSTRAINT function.
Has someone a solution for this problem? It has to be done with scripting!examnotes (Martijn@.discussions.microsoft.com) writes:
> I'm making an SQL script that has to drop some columns. The problem is
> that the column has a default value and therefore I can't use the DROP
> COLUMN directly. I've got to drop the 'default' constraint, but the
> problem is that we have to use this script on different database and
> then the constraint name is not always the same.
> (example:)
> DB1 -> DF_COLUMNX_ddf87d67s68
> DB2 -> DF_COLUMNX_ddf79djks90
> I know how to get the Constraint name but I can't use that as a variable
> in the DROP CONSTRAINT function.
> Has someone a solution for this problem? It has to be done with scripting!
SELECT @.default_name = o2.name
FROM syscolumns c
JOIN sysobjects o ON c.id = o.id
JOIN sysobjects o2 ON c.cdefault = o2.id
WHERE o.name = @.tbl
AND c.name = @.col
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You'll have to resort to dynamic SQL:
declare @.constraint varchar(8000), @.str varchar (8000)
set @.constraint = 'DF_COLUMNX_ddf87d67s68'
set @.str = 'alter table MyTable drop constraint ' + @.constraint
exec (@.str)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Martijn" <Martijn@.discussions.microsoft.com> wrote in message
news:3C62E773-C60A-46ED-A8A0-32E5AFDC7ACF@.microsoft.com...
Hello,
I'm making an SQL script that has to drop some columns. The problem is that
the column has a default value and therefore I can't use the DROP COLUMN
directly. I've got to drop the 'default' constraint, but the problem is that
we have to use this script on different database and then the constraint
name
is not always the same.
(example:)
DB1 -> DF_COLUMNX_ddf87d67s68
DB2 -> DF_COLUMNX_ddf79djks90
I know how to get the Constraint name but I can't use that as a variable in
the DROP CONSTRAINT function.
Has someone a solution for this problem? It has to be done with scripting!|||To have more control over constraint names, create constraints using the
ALTER TABLE:
alter table owner.table
add constraint constraint_name
default (<default_value | default_expression> )
for column_name
with values
go
This way you control the names of constraints (in this case the name of the
default constraint).
ML

Drop column with default constraint

Hello
I have a table with column which have default constraint
(SQL server generated name of constraint).
Is there a way to drop this column without re-creating the table?
Example:
create table abc (xxx int, yyy int default 0);
alter table abc drop column yyy;
Thanks
TibXSee thread:
SQL Help with Drop Column
http://tinyurl.com/65nva
Bryce|||It's a good idea to give a default a meaningful name when you create
it. Query Analyzer will show you the name of the constraint in the
object browser (the name of a default begins with DF and includes the
table and column name). Then you can drop it in the usual way:
ALTER TABLE abc DROP CONSTRAINT DF__abc__yyy__208E6DA8;
ALTER TABLE abc DROP COLUMN yyy;
David Portas
SQL Server MVP
--|||Try,
use northwind
go
create table t (
colA int,
colB varchar(25) default ('aaaaa')
)
go
declare @.sql nvarchar(4000)
declare @.cnstname sysname
select
@.cnstname = [name]
from
sysobjects as so
where
xtype = 'D'
and parent_obj = object_id('dbo.t')
and col_name(parent_obj, info) = 'colB'
if @.cnstname is not null
begin
set @.sql = N'alter table dbo.t drop constraint ' + @.cnstname
execute sp_executesql @.sql
end
go
alter table dbo.t
drop column colB
go
alter table dbo.t
add colB varchar(25)
go
alter table dbo.t
add constraint df_t_colB default ('aaaaa') for colB with values
go
alter table dbo.t
drop constraint df_t_colB
go
alter table dbo.t
drop column colB
go
alter table dbo.t
add colB varchar(25)
go
create default df_t_colB as 'aaaaa'
go
execute sp_bindefault df_t_colB, 't.colB'
go
execute sp_unbindefault 't.colB'
go
drop default df_t_colB
go
drop table dbo.t
go
AMB
"TibX" wrote:

> Hello
> I have a table with column which have default constraint
> (SQL server generated name of constraint).
> Is there a way to drop this column without re-creating the table?
>
> Example:
> create table abc (xxx int, yyy int default 0);
> alter table abc drop column yyy;
>
> Thanks
> TibX
>|||The problem is that I have several installations
with generated name of that default constraint
and I need drop column by a script.
TibX
David Portas wrote:
> It's a good idea to give a default a meaningful name when you create
> it. Query Analyzer will show you the name of the constraint in the
> object browser (the name of a default begins with DF and includes the
> table and column name). Then you can drop it in the usual way:
> ALTER TABLE abc DROP CONSTRAINT DF__abc__yyy__208E6DA8;
> ALTER TABLE abc DROP COLUMN yyy;
>|||Try this:
DECLARE @.df SYSNAME
SET @.df =
(SELECT OBJECT_NAME(cdefault)
FROM SYSCOLUMNS
WHERE id = OBJECT_ID('dbo.abc')
AND name = 'yyy')
IF @.df IS NOT NULL
BEGIN
EXEC sp_rename @.df, 'df_to_drop', 'OBJECT'
ALTER TABLE dbo.abc DROP CONSTRAINT df_to_drop
END
ALTER TABLE dbo.abc DROP COLUMN yyy
David Portas
SQL Server MVP
--|||Thanks for your solutions.
I use
select name from sysobjects ...
TibX

Drop Column with Constraint

Hello !

I am using Microsoft SQL Server 2000

I am trying to drop a column that has a constraint, executing the script inside a transaction:

BEGIN TRANSACTION

ALTER TABLE MOPTeste DROP CONSTRAINT FK_IDMOPPais

ALTER TABLE MOPTeste DROP COLUMN IDMOPPais

COMMIT

If i dont commit the drop constraint, it wont let me drop the column

Solutions?

Seems your code is OK:

IF OBJECT_ID('T1') IS NOT NULL
drop table T1;

IF OBJECT_ID('T2') IS NOT NULL
drop table T2;

Create Table T1 (
num int primary key,
[Name] sysname
)

Create Table T2 (
Id int primary key,
num int,
Constraint T2_FK Foreign Key(num) References T1(num)
)

BEGIN TRANSACTION
ALTER TABLE T2 DROP CONSTRAINT T2_FK
ALTER TABLE T2 DROP COLUMN num;
COMMIT

The reason it require drop constraint first, is because T2_FK depends on column num,
Just like you can't drop a table if the table reference it not droped.

Thanks,

zuomin

|||

It should be work because the ALTER TABLE has an implicit transactions. More , I tested your situations and the things worked good. I thing your error come to the other part.

|||Yes my mistake. The error was coming from default constraint.. nevermind it! Tks for your help!