Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Is it safe to use "update syscolumns set...
Add to briefcase
15 posts, Page 1 of 2
12»»

Is it safe to use "update syscolumns set colstat" Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Aspet Golestanian Namagerdi
Posted Monday, December 29, 2008 12:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 174, Visits: 663
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
Post #626777
Jack Corbett
Posted Monday, December 29, 2008 12:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871
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

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
Post #626788
Michael Earl-395764
Posted Monday, December 29, 2008 12:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.
Post #626789
Aspet Golestanian Namagerdi
Posted Monday, December 29, 2008 1:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 174, Visits: 663
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.
Post #626799
The Dixie Flatline
Posted Monday, December 29, 2008 1:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 3,790, Visits: 5,548
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? -- Stephen Stills
Post #626806
Grant Fritchey
Posted Monday, December 29, 2008 1:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383, Visits: 25,189
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #626815
Matt Miller (#4)
Posted Monday, December 29, 2008 1:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 7:57 PM
Points: 6,998, Visits: 13,949
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?
Post #626824
The Dixie Flatline
Posted Monday, December 29, 2008 1:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 3,790, Visits: 5,548
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? -- Stephen Stills
Post #626831
GilaMonster
Posted Monday, December 29, 2008 1:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 37,742, Visits: 30,021
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 2008, MVP
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
Post #626834
Matt Miller (#4)
Posted Monday, December 29, 2008 2:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 7:57 PM
Points: 6,998, Visits: 13,949
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?
Post #626839
« Prev Topic | Next Topic »

Add to briefcase
15 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.