Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jbibby
Starting Member
2 Posts |
 Posted - 2009-03-16 : 22:46:00
|
| Not sure if there is a nice slick way to do this... basically I have a database with a few hundred tables. (and live data) and all the primary keys are clustered (default in 2k5).We actually need to remove the clustered keys and readd the primary keys as nonclustered. Before I script this by hand, I was wondering if anyone know a shortcut/scripting approach/black magic that would make the process a little less error prone and a lot less painful.Thanks in advance for your help!--JB |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-17 : 00:09:08
|
| I would like to hear why you are doing this, since it sounds like a really bad idea to do it.CODO ERGO SUM |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2009-03-17 : 04:34:48
|
I want to add my voice to the concern expressed by Michael Valentine Jones (only louder). Nonetheless, one way to do it is this:1. Run this queryselect 'alter table ' + Table_Name + ' drop constraint ' + constraint_namefrom INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_type = 'primary key' 2. Copy and paste the output of this query, look through it carefully and execute it and hold your breath.This looks only for primary keys, not whether they are clustered.Alternatively, you can use the undocumented sp_msforeachtable to access each table, pick up the primary key and drop it. I like that less because in the way I have described above, you get to see the query before you execute it. (And, I have an irrational fear of undocumented features such as ms_spforeachtable, master.dbo.spt_values etc.) |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2009-03-17 : 05:18:57
|
Also, you can query sys.indexes table to find out clustered indexes attached to the primary key columns:select 'alter table ' + object_name(object_id)+ ' drop constraint ' + [name]from sys.indexes where type = 1 and is_primary_key = 1 Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
jbibby
Starting Member
2 Posts |
Posted - 2009-03-17 : 05:24:03
|
| Nonclustered primary keys are a requirement for the software I am using (Content Management). I did find a way to drop all of the constraints using the sp_msforeachtable. The challenge here is that I end up with NO primary keys, when all I want is the same primary keys, but unclustered. Is there perhaps a script similar to:select 'alter table ' + Table_Name + ' drop constraint ' + constraint_namefrom INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_type = 'primary key'Which would also generate the create scripts?I tried using SQL Servers Management console to script the primary keys, but it seems to only want to do this inline with the table create/drop.--JB |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-03-17 : 07:49:52
|
| Just FYI you should *really* create another clustered index on the table then, a table without a clustered index will be defined as a heap and can cause a lot of fragmentation. Look up "Heap Structures" in BOL and also look at this article: http://www.mssqltips.com/tip.asp?tip=1254- Lumbago |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-17 : 09:36:02
|
quote:
Originally posted by jbibby
Nonclustered primary keys are a requirement for the software I am using (Content Management)...
That doesn't make much sense. If this is vendor supplied sofware, why didn't they create the tables the way they wanted?I have never heard of a reason why a "Content Management" application would require non-clustered primary keys.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Un-clustering the clustered primary key.