Is it safe to use "update syscolumns set colstat"

Question

Is it safe to use "update syscolumns set colstat"

Aspet Golestanian Namagerdi

SSCarpal Tunnel

Points: 4444
More actions
December 29, 2008 at 12:43 pm

#217026

Hello guys,
I have a very large table(100,000,000 records), and I have an identity field ,which is primary key.I want to take out this identity property from my primary key.Is it safe to work with system tables and fallowing TSQL:
sp_configure 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it's an identity column */
where id = object_id('Table1')
and name = 'Id'
go
exec sp_configure 'allow update', 0
go
reconfigure with override
go

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

I don't believe you can update the system tables in SQL Server 2005. I wouldn't do it even if you can.

The usual suggestion for this type of issue is to do a select into new_table and then drop the old table and do an sp_rename on the new table.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

I agree - really bad idea.

Use an ALTER TABLE script or copy the data into another table. Don't try to do this manually in the system tables.

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 3

but the problem is that,the speed is very important for my operation.if I use a new table and then transfer the data,it takes a long time cause of large amount of data.but I want to do in less than 1 minutes.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Can ALTER TABLE affect the IDENTITY property? I'm looking at help online for ALTER TABLE and can't see it. If you search "identity columns/modifying properties", it just points you to Object Explorer.

This is probably why the OP is asking the question in the first place.

Why on earth does this have to be done in one minute? Are you going to turn it right back on again? After 100 million rows, you just decided identity wasn't the right approach? Or are you just wanting to slip a couple of new rows in where previous ones have been deleted and you are trying to maintain the numbering scheme? Something is amiss here.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 5

You don't have access to system tables in 2005. Those are views that you're seeing.

I wouldn't do something like that on a bet, especially on a production system that I was paid to care for.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

What's the goal? Is it important to drop the column, or are you only looking to change the PRIMARY KEY constraint? If the latter, you don't have to drop the column, you could simply delete the PRIMARY KEY constraint and rebuild it. If you don't force a change to the clustered index, it shouldn't be slow at all.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

Matt, that was my first thought, but there's a problem if they just defined the identity property for the column without expressly creating a constraint.

Try this:

Create table dbo.dummy (ID int identity(1,1) primary key, filler char(100))

select * from information_schema.constraint_column_usage

where table_name = 'dummy'

There's a constraint on the ID column for the primary key, but not one for identity. You can drop that constraint and ID is no longer the primary key, but it is still an identity column.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Aspet Golestanian Namagerdi (12/29/2008)
Is it safe to work with system tables and fallowing TSQL:

If your goal is to corrupt your database and cause downtime and loss of data, sure.

It is never safe to fiddle with the system tables, on any version. If you're not really careful and know exactly what you;re doing, you can cause major problems, and that's in SQL 2000.

In 2005 the system tables are hidden and aren't even readable. syscolumns is a view, and is only there for backward compatibility with SQL 2000. The actual table is syscolpars.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Bob Hovious (12/29/2008)
Matt, that was my first thought, but there's a problem if they just defined the identity property for the column without expressly creating a constraint.
Try this:
Create table dbo.dummy (ID int identity(1,1) primary key, filler char(100))
select * from information_schema.constraint_column_usage
where table_name = 'dummy'
There's a constraint on the ID column for the primary key, but not one for identity. You can drop that constraint and ID is no longer the primary key, but it is still an identity column.

Ack - read that backwards.... He wants to KEEP the PK, just not the Identity property.

Agreed - no way to do that without what will end up being a "drop and rebuild table" process. Even doing it from SSMS will drop and recreate the whole table.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 10

WHY? WHY do you want to drop the IDENTITY attribute? It's served you quite well for a hundred million rows... why are you trying to change horses in the middle of the stream and how do you think you're gonna provide new numbers for that column with out it taking a month of Sunday's for each row?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 11

Calm down, Jeff. Have some Eggnog. Maybe a pork chop. 😀

I think the OP has abandoned us anyway. Don't know whether it was because of the comments saying "bad idea" or several of us asking "why?" 😎

Merry Christmas and Happy New Year to all !!

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 12

Bob Hovious (12/29/2008)
Calm down, Jeff. Have some Eggnog. Maybe a pork chop. 😀
I think the OP has abandoned us anyway. Don't know whether it was because of the comments saying "bad idea" or several of us asking "why?" 😎
Merry Christmas and Happy New Year to all !!

Heh... you should know me by now, Bob... No calming down when someone is doing something that appears to be, well... nevermind... Happy New Year.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

john.moreno Default port Points: 1485 More actions · Answer 13

GilaMonster (12/29/2008)
Aspet Golestanian Namagerdi (12/29/2008)
Is it safe to work with system tables and fallowing TSQL:
If your goal is to corrupt your database and cause downtime and loss of data, sure.
It is never safe to fiddle with the system tables, on any version. If you're not really careful and know exactly what you;re doing, you can cause major problems, and that's in SQL 2000.
In 2005 the system tables are hidden and aren't even readable. syscolumns is a view, and is only there for backward compatibility with SQL 2000. The actual table is syscolpars.

For someone that is just curious and not planning on doing this on a proudction system...

You can't read the actual table without using a DAC connection, and can't modify it without putting the SERVER in single user mode.

MS (rightly) really, really, really doesn't want you modifying the system tables directly. As for this particular change, it's easy enough to do if you're willing to do so.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 14

Good lord, a blast from the past.

John, have you discovered actual documentation for DBCC TIMEWARP?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills