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.

Drop Trigger from Replicated Table?

I created a trigger on a replicated table in a publishing database on SQL Server 2000. When I attempt to ALTER TABLE ... DISABLE TRIGGER ..., I get a message that I cannot alter the table since it is part of a publication. Does anyone know if I would be able to issue a DROP TRIGGER or ALTER TRIGGER on a replicated table?

Thanks,

Gerald

you can not Disable trigger by alter statment in a replicated table. Drop the trigger is the only way...

Madhu

sql

Drop Index on System Tables

SQL SERVER 2000

System let's you alter the system tables and add indexes. However, it won't
let you drop the index afterward.

Anybody know how to drop an index on a system table?

Thanks,

Kevin"Kevin Haugen" <khaugen@.pacbell.net> wrote in message
news:3sgTc.6297$Ux.622@.newssvr29.news.prodigy.com. ..
> SQL SERVER 2000
> System let's you alter the system tables and add indexes. However, it
> won't
> let you drop the index afterward.
> Anybody know how to drop an index on a system table?
> Thanks,
> Kevin

You don't say which table or what error you get, but in any case Microsoft
does not support any modifications to system tables, so the best option is
probably to restore from a backup. If you can post more detailed
information, then someone may have a better suggestion, but in general you
shouldn't touch system tables at all.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:411dc8f8$1_2@.news.bluewin.ch...
> "Kevin Haugen" <khaugen@.pacbell.net> wrote in message
> news:3sgTc.6297$Ux.622@.newssvr29.news.prodigy.com. ..
> > SQL SERVER 2000
> > System let's you alter the system tables and add indexes. However, it
> > won't
> > let you drop the index afterward.
> > Anybody know how to drop an index on a system table?
> > Thanks,
> > Kevin
> You don't say which table or what error you get, but in any case Microsoft
> does not support any modifications to system tables, so the best option is
> probably to restore from a backup. If you can post more detailed
> information, then someone may have a better suggestion, but in general you
> shouldn't touch system tables at all.

And further more, with some of the tables, it may appear you've succeeded,
but in reality nothing has changed, or upon a restart things were the way
they started. sysjobs I believe is one such derived table.

> Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:411dc8f8$1_2@.news.bluewin.ch...
> "Kevin Haugen" <khaugen@.pacbell.net> wrote in message
> news:3sgTc.6297$Ux.622@.newssvr29.news.prodigy.com. ..
> > SQL SERVER 2000
> > System let's you alter the system tables and add indexes. However, it
> > won't
> > let you drop the index afterward.
> > Anybody know how to drop an index on a system table?
> > Thanks,
> > Kevin
> You don't say which table or what error you get, but in any case Microsoft
> does not support any modifications to system tables, so the best option is
> probably to restore from a backup. If you can post more detailed
> information, then someone may have a better suggestion, but in general you
> shouldn't touch system tables at all.
> Simon
>
I have an ERP application which constantly scans the sysobjects table for
xtype ''U'. I was attempting to speed up this particular SELECT statement
by adding an index to the xtype column (which seemed like a good idea at the
time). I ran a DBCC CHECKDB and it threw msg's 8951 and 8955. I actually
came across the problem when I tried to apply the script referenced in KB
293177 when it failed to complete.

I'm currently working on restoring the database into a new schema to resolve
the issue.

Kevin|||Kevin Haugen (khaugen@.pacbell.net) writes:
> I have an ERP application which constantly scans the sysobjects table
> for xtype ''U'. I was attempting to speed up this particular SELECT
> statement by adding an index to the xtype column (which seemed like a
> good idea at the time). I ran a DBCC CHECKDB and it threw msg's 8951
> and 8955.

It is dubious that even if sysobjects would be a normal table that this
would be a good thing to do. The column may not be selective enough.

In any case, in the next version of SQL Server, SQL 2005 which currently
is in beta, there is no longer any sysobjects table. There is a sysobjects
view which is built on top of the new catalog views, which in their turn
are built on tables that are not exposed at all.

> I actually came across the problem when I tried to apply the
> script referenced in KB 293177 when it failed to complete.

I have no tried that script, but it may be that you should throw in an
INSENSITIVE before CURSOR.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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 from tables

Hello,
I have two questions.
My goal is to create a script which drops identities for multiple tables in
a database. So for example if: alter table alter column X int NO IDENTITY
was valid...which is not I would be all set. Dropping and Recreating the
column could work as long as the column gets added in the same order from
where it was deleted, but I was hoping for something sleaker (see code below).
In process of testing some scenarios for the above I uncovered the following
"weirdness"
I created a test table with an identity field and I execute the following
select:
select * from syscolumns c inner join sysobjects o on c.id = o.id
where o.name = 'test'
The result set shows one row for each field. The data on the identity field
row seems ok up to column "usertype" but all remaining data values are
shifted one position out. So value for "prec" shows under "scale".. and so
on for 32 fields
I bet you can recreate this issue as well. Create a table with one identity
field and run the select sql below.
Question 1: Is this is a bug?
Question 2: Is the code below appropriate to drop identities or are there
any issues with updating the syscolumns table this way.
sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
update syscolumns
set colstat = 0,
autoval = NULL
where id = (select id from sysobjects where name = 'test')
and colstat = 1
sp_configure 'allow updates', 0;
RECONFIGURE;
Thank You in advance,
John
John,
Updating the system table is usually not a good practice. Can you just
create a new table and load the data from the existing table (once for each
table)? If you do decide to update the system tables I'd recommend you
backup the database first.
HTH
Jerry
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
|||"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
Updating system tables directly is the easiest route to corrupt and
unrecoverable data.
Add a new column. Populate it. Drop the old column. Forget about modifying
system tables.
David Portas
SQL Server MVP
|||John K wrote:
> Hello,
> I have two questions.
> SNIP
Create a new table with the same ddl, sans identity attribute. Insert
the data from old to new. Create indexes. Drop the old table. Rename the
new table. Run sp_recompile on each procedure that accesses the table.
If you have any DRI involved, it makes the process more difficult.
Messing with the system tables is a surefire way to cause the database
to be marked suspect or cause any number of other operational / support
issues.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Ok, no system table changes.
I don't care to move the data, but I do have hundreds of tables and I do
need the new column to be at the same physical order the old one existed. So
how do I script this operation to create a new column of int and drop the old
identity one in the same spot (column order)?
John
"David Gugick" wrote:

> John K wrote:
> Create a new table with the same ddl, sans identity attribute. Insert
> the data from old to new. Create indexes. Drop the old table. Rename the
> new table. Run sp_recompile on each procedure that accesses the table.
> If you have any DRI involved, it makes the process more difficult.
> Messing with the system tables is a surefire way to cause the database
> to be marked suspect or cause any number of other operational / support
> issues.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||John K wrote:
> Ok, no system table changes.
> I don't care to move the data, but I do have hundreds of tables and I
> do need the new column to be at the same physical order the old one
> existed. So how do I script this operation to create a new column of
> int and drop the old identity one in the same spot (column order)?
You need to create a new table as I mentioned in the previous post. You
can use the INFORMATION_SCHEMA.COLUMNS view to query the table and sort
by ORDINAL_POSITION to get the correct order. You can do this in your
favorite development tool (my preference) or do this from a stored
procedure using dynamic sql (a valid alternative, but the coding and
debugging is less elegant).
Indexes are more involved, but that information is also available from
the INFORMATION_SCHEMA views. Keys make it more involved as well. Also,
pay attention to the location of tables / clustered indexes and
non-clustered indexes / keys so you don't end up creating these objects
in a different location from where they were originally located.
If you want to see how SQL Enterprise does this, create a test table
with an identity column and change the identity attribute. Rather than
applying the change, have SQL EM script out the T-SQL.
Why is the physical order important? Physical positioning should not be
of any importance. You can always query the columns in the order you'd
like to see them returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com

DROP IDENTITY from tables

Hello,
I have two questions.
My goal is to create a script which drops identities for multiple tables in
a database. So for example if: alter table alter column X int NO IDENTITY
was valid...which is not I would be all set. Dropping and Recreating the
column could work as long as the column gets added in the same order from
where it was deleted, but I was hoping for something sleaker (see code below
).
In process of testing some scenarios for the above I uncovered the following
"weirdness"
I created a test table with an identity field and I execute the following
select:
select * from syscolumns c inner join sysobjects o on c.id = o.id
where o.name = 'test'
The result set shows one row for each field. The data on the identity field
row seems ok up to column "usertype" but all remaining data values are
shifted one position out. So value for "prec" shows under "scale".. and so
on for 32 fields
I bet you can recreate this issue as well. Create a table with one identity
field and run the select sql below.
Question 1: Is this is a bug?
Question 2: Is the code below appropriate to drop identities or are there
any issues with updating the syscolumns table this way.
sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
update syscolumns
set colstat = 0,
autoval = NULL
where id = (select id from sysobjects where name = 'test')
and colstat = 1
sp_configure 'allow updates', 0;
RECONFIGURE;
Thank You in advance,
JohnJohn,
Updating the system table is usually not a good practice. Can you just
create a new table and load the data from the existing table (once for each
table)? If you do decide to update the system tables I'd recommend you
backup the database first.
HTH
Jerry
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John|||"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
Updating system tables directly is the easiest route to corrupt and
unrecoverable data.
Add a new column. Populate it. Drop the old column. Forget about modifying
system tables.
David Portas
SQL Server MVP
--|||John K wrote:
> Hello,
> I have two questions.
> SNIP
Create a new table with the same ddl, sans identity attribute. Insert
the data from old to new. Create indexes. Drop the old table. Rename the
new table. Run sp_recompile on each procedure that accesses the table.
If you have any DRI involved, it makes the process more difficult.
Messing with the system tables is a surefire way to cause the database
to be marked suspect or cause any number of other operational / support
issues.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Ok, no system table changes.
I don't care to move the data, but I do have hundreds of tables and I do
need the new column to be at the same physical order the old one existed. S
o
how do I script this operation to create a new column of int and drop the ol
d
identity one in the same spot (column order)?
John
"David Gugick" wrote:

> John K wrote:
> Create a new table with the same ddl, sans identity attribute. Insert
> the data from old to new. Create indexes. Drop the old table. Rename the
> new table. Run sp_recompile on each procedure that accesses the table.
> If you have any DRI involved, it makes the process more difficult.
> Messing with the system tables is a surefire way to cause the database
> to be marked suspect or cause any number of other operational / support
> issues.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||John K wrote:
> Ok, no system table changes.
> I don't care to move the data, but I do have hundreds of tables and I
> do need the new column to be at the same physical order the old one
> existed. So how do I script this operation to create a new column of
> int and drop the old identity one in the same spot (column order)?
You need to create a new table as I mentioned in the previous post. You
can use the INFORMATION_SCHEMA.COLUMNS view to query the table and sort
by ORDINAL_POSITION to get the correct order. You can do this in your
favorite development tool (my preference) or do this from a stored
procedure using dynamic sql (a valid alternative, but the coding and
debugging is less elegant).
Indexes are more involved, but that information is also available from
the INFORMATION_SCHEMA views. Keys make it more involved as well. Also,
pay attention to the location of tables / clustered indexes and
non-clustered indexes / keys so you don't end up creating these objects
in a different location from where they were originally located.
If you want to see how SQL Enterprise does this, create a test table
with an identity column and change the identity attribute. Rather than
applying the change, have SQL EM script out the T-SQL.
Why is the physical order important? Physical positioning should not be
of any importance. You can always query the columns in the order you'd
like to see them returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com

DROP IDENTITY from tables

Hello,
I have two questions.
My goal is to create a script which drops identities for multiple tables in
a database. So for example if: alter table alter column X int NO IDENTITY
was valid...which is not I would be all set. Dropping and Recreating the
column could work as long as the column gets added in the same order from
where it was deleted, but I was hoping for something sleaker (see code below).
In process of testing some scenarios for the above I uncovered the following
"weirdness"
I created a test table with an identity field and I execute the following
select:
select * from syscolumns c inner join sysobjects o on c.id = o.id
where o.name = 'test'
The result set shows one row for each field. The data on the identity field
row seems ok up to column "usertype" but all remaining data values are
shifted one position out. So value for "prec" shows under "scale".. and so
on for 32 fields
I bet you can recreate this issue as well. Create a table with one identity
field and run the select sql below.
Question 1: Is this is a bug?
--
Question 2: Is the code below appropriate to drop identities or are there
any issues with updating the syscolumns table this way.
sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
update syscolumns
set colstat = 0,
autoval = NULL
where id = (select id from sysobjects where name = 'test')
and colstat = 1
sp_configure 'allow updates', 0;
RECONFIGURE;
--
Thank You in advance,
JohnJohn,
Updating the system table is usually not a good practice. Can you just
create a new table and load the data from the existing table (once for each
table)? If you do decide to update the system tables I'd recommend you
backup the database first.
HTH
Jerry
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John|||"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
Updating system tables directly is the easiest route to corrupt and
unrecoverable data.
Add a new column. Populate it. Drop the old column. Forget about modifying
system tables.
--
David Portas
SQL Server MVP
--|||John K wrote:
> Hello,
> I have two questions.
> SNIP
Create a new table with the same ddl, sans identity attribute. Insert
the data from old to new. Create indexes. Drop the old table. Rename the
new table. Run sp_recompile on each procedure that accesses the table.
If you have any DRI involved, it makes the process more difficult.
Messing with the system tables is a surefire way to cause the database
to be marked suspect or cause any number of other operational / support
issues.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Ok, no system table changes.
I don't care to move the data, but I do have hundreds of tables and I do
need the new column to be at the same physical order the old one existed. So
how do I script this operation to create a new column of int and drop the old
identity one in the same spot (column order)?
--
John
"David Gugick" wrote:
> John K wrote:
> > Hello,
> >
> > I have two questions.
> >
> > SNIP
> Create a new table with the same ddl, sans identity attribute. Insert
> the data from old to new. Create indexes. Drop the old table. Rename the
> new table. Run sp_recompile on each procedure that accesses the table.
> If you have any DRI involved, it makes the process more difficult.
> Messing with the system tables is a surefire way to cause the database
> to be marked suspect or cause any number of other operational / support
> issues.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||John K wrote:
> Ok, no system table changes.
> I don't care to move the data, but I do have hundreds of tables and I
> do need the new column to be at the same physical order the old one
> existed. So how do I script this operation to create a new column of
> int and drop the old identity one in the same spot (column order)?
You need to create a new table as I mentioned in the previous post. You
can use the INFORMATION_SCHEMA.COLUMNS view to query the table and sort
by ORDINAL_POSITION to get the correct order. You can do this in your
favorite development tool (my preference) or do this from a stored
procedure using dynamic sql (a valid alternative, but the coding and
debugging is less elegant).
Indexes are more involved, but that information is also available from
the INFORMATION_SCHEMA views. Keys make it more involved as well. Also,
pay attention to the location of tables / clustered indexes and
non-clustered indexes / keys so you don't end up creating these objects
in a different location from where they were originally located.
If you want to see how SQL Enterprise does this, create a test table
with an identity column and change the identity attribute. Rather than
applying the change, have SQL EM script out the T-SQL.
Why is the physical order important? Physical positioning should not be
of any importance. You can always query the columns in the order you'd
like to see them returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com

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 Create vs Alter in regards to stored procedures

Anyone have any compelling arguements for using one over the other?

Thanks

Mercy

I'm not sure what the argument would be.

That's like asking if there's an argument to use SELECT, INSERT, UPDATE, OR DELETE.

Adamus

|||

Adamus Turner wrote:

I'm not sure what the argument would be.

That's like asking if there's an argument to use SELECT, INSERT, UPDATE, OR DELETE.

Adamus

I am not sure what your saying.

The question is:

Is there any reason to use

'IF Procedure Exists DROP

Create Procedure'

as opposed to

'ALTER Procedure'

Mercy

|||

I still don't understand what the argument would be. It's purely based on your desired result.

Adamus

|||If the user owns an object and has had create privileges revoked since gaining ownership of the object the ONLY way that the user can make changes is with the ALTER command. This doesn't come up frequently, but it has come a few times.|||

ALTERing a procedure keeps the permissions the same, while DROP and CREATE will require you to set the permissions on the now new stored procedure.

In SQL 2000 and before some people prefer to use DROP and CREATE so that they can see when the procedures were last updated.

Personally I prefer the ALTER method so that any undocumented permissions are preserved instead of destroyed.

Drop Column problem

Hello,

I'm just returning to MS SQL Server after two years of dealing with
Sybase ASE. I need to drop a column, using the alter table command.
I keep getting an error indicating that a constraint is using the
column. Here is the create script for the table
create table mytable
(col1 char(1) not null,
col2 char(1) default 'A' not null
)

Here is the alter table command:
alter table mytable drop column col2

When I run it I get the following error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__mytable__col2__114A936A' is dependent on column
'col2'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col2 failed because one or more objects access
this column.

Is there anyway to tell the database to drop all column constraints
when the column is deleted?

Thanks,

James K.Unfortunately you have to drop the default first. Give your default a
meaningful name so that it's easier to refer to it in other statements:

create table mytable
(col1 char(1) not null,
col2 char(1) constraint DF_mytable_col2 default 'A' not null
)

ALTER TABLE mytable DROP CONSTRAINT DF_mytable_col2
ALTER TABLE mytable DROP COLUMN col2

--
David Portas
SQL Server MVP
--|||Hi

I don't think there is a way to easily do this, the syntax of the ALTER
table does not allow it without extra work.

You can drop the constraint before the column in the same statement, but to
do this you would need to know the name. As you haven't specified a name in
your create table statement the system generates one for you. It is
therefore easier to create the defaults in an alter table statement , you
can then specify the default constraint name.

create table mytable
(col1 char(1) not null,
col2 char(1) not null
)

ALTER TABLE mytable
ADD CONSTRAINT DF_mytable_col2 default 'A' FOR col2

alter table mytable
drop constraint DF_mytable_col2,
column col2

John
"James Knowlton" <jlknowlton@.hotmail.com> wrote in message
news:bde3b38b.0407010716.62a5b53a@.posting.google.c om...
> Hello,
> I'm just returning to MS SQL Server after two years of dealing with
> Sybase ASE. I need to drop a column, using the alter table command.
> I keep getting an error indicating that a constraint is using the
> column. Here is the create script for the table
> create table mytable
> (col1 char(1) not null,
> col2 char(1) default 'A' not null
> )
> Here is the alter table command:
> alter table mytable drop column col2
> When I run it I get the following error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__mytable__col2__114A936A' is dependent on column
> 'col2'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col2 failed because one or more objects access
> this column.
> Is there anyway to tell the database to drop all column constraints
> when the column is deleted?
> Thanks,
> James K.

drop column fails

Hi all,
Want to drop a column of an existing table with
alter table TE20_AREA
drop column E20IN_NODE_NUMBER
But get:
The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
'E20IN_NODE_NUMBER'.
What is the object DF... checked for indexes, constraints, relations
haven't found somthing with this name '
BTW, deleting the row in the gui works like a charme '? (Does not
help because db modification needs to be in a batch job :-( )
Any help is highly appreciated.
TIA
DanHi Dan,
An object name starting with DF_ and ending in a number is a automatically
generated name for a default constraint. You will see these names when you
create a column with a default in a table in Enterprise Manager, or with for
example CREATE TABLE some_table(some_column INT DEFAULT 0). You can
explicitly name default constraints with:
CREATE TABLE some_table(some_column INT CONSTRAINT
DF_some_table__some_column_is_zero DEFAULT 0).
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
"Dan Ackermann" <dummy@.intos.ch> wrote in message
news:%2313rW$4$DHA.692@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Want to drop a column of an existing table with
> alter table TE20_AREA
> drop column E20IN_NODE_NUMBER
> But get:
> The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
> 'E20IN_NODE_NUMBER'.
> What is the object DF... checked for indexes, constraints, relations
> haven't found somthing with this name '
> BTW, deleting the row in the gui works like a charme '? (Does not
> help because db modification needs to be in a batch job :-( )
> Any help is highly appreciated.
> TIA
> Dan
>

DROP COLUMN

If I execute ALTER TABLE DROP COLUMN XX but the column XX has Constraints
there will be an error.
I should first delete the related Constraints.
Is there any way to delete all of the Constraints related to this column?
Should I navigate before for all of the constraints of the table and delete
the related ones? In this case, how could I do this in VB?
Regards.
Jose Nuez
Montevideo
Jose,
The INFORMATION_SCHEMA view contain what you are after. Here is a sample
query that may be helpful to you:
select y.TABLE_NAME AS ConstrainingTable, x.CONSTRAINT_NAME AS
ConstraintToDisable,
z.TABLE_NAME AS ConstrainedTable, zz.COLUMN_NAME as ConstrainedColumn,
z.CONSTRAINT_NAME AS ConstrainedConstraint
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS x
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE y
on x.CONSTRAINT_NAME = y.CONSTRAINT_NAME
join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE z
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE zz
on z.CONSTRAINT_NAME = zz.CONSTRAINT_NAME
on x.UNIQUE_CONSTRAINT_NAME = z.CONSTRAINT_NAME
where y.COLUMN_NAME = 'MyKeyColumn' and z.TABLE_NAME = 'MyConstrainedTable'
RLF
"Jose Nunez" <josenunez70@.hotmail.com> wrote in message
news:uBIE0B7LIHA.5208@.TK2MSFTNGP04.phx.gbl...
> If I execute ALTER TABLE DROP COLUMN XX but the column XX has Constraints
> there will be an error.
> I should first delete the related Constraints.
> Is there any way to delete all of the Constraints related to this column?
> Should I navigate before for all of the constraints of the table and
> delete the related ones? In this case, how could I do this in VB?
> Regards.
> Jose Nuez
> Montevideo
>

drop and create constraint

Hi There,
I've to alter the default constraint, So i drop then create agains. Before
applying this changes to all database, do i need to tell users to log off. o
r
will it work even they are connected to the system
Thanks
GaneshOn Thu, 18 May 2006 09:11:01 -0700, Ganesh wrote:

>Hi There,
>I've to alter the default constraint, So i drop then create agains. Before
>applying this changes to all database, do i need to tell users to log off.
or
>will it work even they are connected to the system
Hi Ganesh,
You can drop and create constraints while users are connected to the
database.
That being said - if possible, do this during maintenance window or
during times of low usage. The creation of the constraint (except a
DEFAULT constraint) will also cause SQL Server to check all existing
data . On a big table, this will take some time - and others won't be
able to access the table during that time.
Hugo Kornelis, SQL Server MVP