SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is it safe to use "update syscolumns set colstat"


Is it safe to use "update syscolumns set colstat"

Author
Message
Aspet Golestanian Namagerdi
Aspet Golestanian Namagerdi
Old Hand
Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)

Group: General Forum Members
Points: 384 Visits: 836
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
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18474 Visits: 14896
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Michael Earl-395764
Michael Earl-395764
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4901 Visits: 23078
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
Aspet Golestanian Namagerdi
Old Hand
Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)

Group: General Forum Members
Points: 384 Visits: 836
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
The Dixie Flatline
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5398 Visits: 6900
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
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39813 Visits: 32645
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12225 Visits: 18574
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
The Dixie Flatline
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5398 Visits: 6900
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87255 Visits: 45272
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)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12225 Visits: 18574
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search