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