remove by t-sql the cluster property

Question

remove by t-sql the cluster property

sam-433147

Ten Centuries

Points: 1221
More actions
August 30, 2007 at 2:42 am

#153784

how can i remove by t-sql the cluster property from a CLUSTERED PRIMARY KEY field without droping the index?
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

So you want to change your primary key index from clustered to non-clustered? I don't think you can - a clustered index has a totally different structure from a non-clustered index and so you'd need to drop the index first and then create a new non-clustered index. This will also involve dropping and recreating the primary key constraint in the process.

John

sam-433147 Ten Centuries Points: 1221 More actions · Answer 2

because it is primary key index droping the index fail

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

Since it's a pk, you'll need to drop the constraint, not the index

ALTER TABLE <tablename> DROP CONSTRAINT <pk name>

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

sam-433147 Ten Centuries Points: 1221 More actions · Answer 4

with

ALTER TABLE Elmn DROP CONSTRAINT PK_Elmn

i get:

Server: Msg 3725, Level 16, State 1, Line 1

The constraint 'PK_Elmn' is being referenced by table 'ElmSh', foreign key constraint 'FK_ElmSh_Elmn'.

Server: Msg 3727, Level 16, State 1, Line 1

Could not drop constraint.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Fun and games! I'm afraid you're also going to have to drop any foreign key constraints that reference your primary key, and recreate them afterwards.

John

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

there are forum threads regarding this issue ... I cannot recall them

But this spc may get you on track.

examine it and Test it

Create procedure Spc_ALZ_PK2Clustered

@ParentTbName varchar(128)

, @ParentTbOwner varchar(128) = NULL

, @Commentaar varchar(128) = ''

as

-- creatie test begin

if object_id('T_ParentMC') is null

begin

create table T_ParentMC (

col1 int identity(1,1) not null ,

col2 int not null,

col3 int not null,

col4 int not null,

col5 varchar(100) not null default ''

, CONSTRAINT PK_ParentMC PRIMARY KEY (col2,col3,col4)

&nbsp

print 'table T_ParentMC created'

end

go

if object_id('T_ChildMC') is null

begin

create table T_ChildMC (

col1 int identity(1,1) not null primary key,

col2FK1_1 int not null,

col3FK1_2 int not null,

col4FK1_3 int not null,

col5 varchar(100) not null default ''

, CONSTRAINT FK_C2P FOREIGN KEY (col2FK1_1, col3FK1_2, col4FK1_3)

REFERENCES T_ParentMC (col2,col3,col4)

&nbsp

print 'table T_ChildMC created'

end

go

-- creatie test ende

Declare @ParentTbName varchar(128)

, @ParentTbOwner varchar(128)

select @ParentTbName = 'T_ParentMC', @ParentTbOwner = NULL

SET nocount on

create table #tmpPKeys(

TABLE_QUALIFIER sysname,

TABLE_OWNER sysname not null,

TABLE_NAME sysname not null,

COLUMN_NAME sysname not null,

KEY_SEQ smallint not null,

PK_NAME sysname null )

Create index ix#tmpPKeys on #tmpPKeys (TABLE_QUALIFIER, TABLE_OWNER,TABLE_NAME, KEY_SEQ)

-- Get PK-info

insert into #tmpPKeys

exec sp_pkeys @table_name = @ParentTbName

, @table_owner = @ParentTbOwner

-- [ , [ @table_qualifier = ] 'qualifier' ] -- DBName

create table #tmpFKeys

(PKTABLE_QUALIFIER sysname not null,

PKTABLE_OWNER sysname not null,

PKTABLE_NAME sysname not null,

PKCOLUMN_NAME sysname not null,

FKTABLE_QUALIFIER sysname not null,

FKTABLE_OWNER sysname not null,

FKTABLE_NAME sysname not null,

FKCOLUMN_NAME sysname not null,

KEY_SEQ smallint not null,

UPDATE_RULE smallint not null,

DELETE_RULE smallint not null,

FK_NAME sysname not null,

PK_NAME sysname not null,

DEFERRABILITY int not null)

Create index #tmpFKeys on #tmpFKeys (FK_NAME, KEY_SEQ)

-- Get FK-info (all dependant objects)

insert into #tmpFKeys

exec sp_fkeys @pktable_name = @ParentTbName

, @pktable_owner = @ParentTbOwner

-- [ , [ @pktable_qualifier = ] 'pktable_qualifier' ]

-- { , [ @fktable_name = ] 'fktable_name' }

-- [ , [ @fktable_owner = ] 'fktable_owner' ]

-- [ , [ @fktable_qualifier = ] 'fktable_qualifier' ]

print 'print ''-- verwerking ' + @ParentTbName + ' / ' + @commentaar + ' / Begin '' + convert(varchar(25),getdate(),121) '

print 'Begin transaction trxAlterPK2ClIx --' + @ParentTbName

print ' '

print '-- Drop Referencing constraints'

select 'Alter Table [' + FKTABLE_OWNER + '].[' + FKTABLE_NAME + '] drop constraint [' + FK_NAME + ']' + char(13) + 'GO '

from #tmpFKeys

where Key_SEQ = 1

order by FKTABLE_OWNER, FKTABLE_NAME, FK_NAME

print '-- drop PK-constraint'

select 'Alter Table [' + TABLE_OWNER + '].[' + TABLE_NAME + '] drop constraint [' + PK_NAME + ']' + char(13) + 'GO '

from #tmpPKeys

where Key_SEQ = 1

order by TABLE_OWNER, TABLE_NAME

print '-- Create PK_Constraint'

select 'Alter Table [' + P1.TABLE_OWNER + '].[' + P1.TABLE_NAME + '] WITH NOCHECK add constraint ' + P1.PK_NAME + ' PRIMARY KEY CLUSTERED ( '+

P1.COLUMN_NAME

+ case when P2.COLUMN_NAME is null then '' else ', ' + P2.COLUMN_NAME end

+ case when P3.COLUMN_NAME is null then '' else ', ' + P3.COLUMN_NAME end

+ case when P4.COLUMN_NAME is null then '' else ', ' + P4.COLUMN_NAME end

+ case when P5.COLUMN_NAME is null then '' else ', ' + P5.COLUMN_NAME end

+ case when P6.COLUMN_NAME is null then '' else ', ' + P6.COLUMN_NAME end

+ case when P7.COLUMN_NAME is null then '' else ', ' + P7.COLUMN_NAME end

+ case when P8.COLUMN_NAME is null then '' else ', ' + P8.COLUMN_NAME end

+ case when P9.COLUMN_NAME is null then '' else ', ' + P9.COLUMN_NAME end

+ case when P10.COLUMN_NAME is null then '' else ', ' + P10.COLUMN_NAME end

+ case when P11.COLUMN_NAME is null then '' else ', ' + P11.COLUMN_NAME end

+ case when P12.COLUMN_NAME is null then '' else ', ' + P12.COLUMN_NAME end

+ case when P13.COLUMN_NAME is null then '' else ', ' + P13.COLUMN_NAME end

+ case when P14.COLUMN_NAME is null then '' else ', ' + P14.COLUMN_NAME end

+ case when P15.COLUMN_NAME is null then '' else ', ' + P15.COLUMN_NAME end

+ case when P16.COLUMN_NAME is null then '' else ', ' + P16.COLUMN_NAME end

+ case when P17.COLUMN_NAME is null then '' else ', ' + P17.COLUMN_NAME end

+ case when P18.COLUMN_NAME is null then '' else ', ' + P18.COLUMN_NAME end

+ ' ) ' + char(13) + 'GO '

from #tmpPKeys P1

left join #tmpPKeys P2

on P1.TABLE_OWNER = P2.TABLE_OWNER and P1.TABLE_NAME = P2.TABLE_NAME and P1.KEY_SEQ = 1 and P2.KEY_SEQ = 2

left join #tmpPKeys P3

on P1.TABLE_OWNER = P3.TABLE_OWNER and P1.TABLE_NAME = P3.TABLE_NAME and P1.KEY_SEQ = 1 and P3.KEY_SEQ = 3

left join #tmpPKeys P4

on P1.TABLE_OWNER = P4.TABLE_OWNER and P1.TABLE_NAME = P4.TABLE_NAME and P1.KEY_SEQ = 1 and P4.KEY_SEQ = 4

left join #tmpPKeys P5

on P1.TABLE_OWNER = P5.TABLE_OWNER and P1.TABLE_NAME = P5.TABLE_NAME and P1.KEY_SEQ = 1 and P5.KEY_SEQ = 5

left join #tmpPKeys P6

on P1.TABLE_OWNER = P6.TABLE_OWNER and P1.TABLE_NAME = P6.TABLE_NAME and P1.KEY_SEQ = 1 and P6.KEY_SEQ = 6

left join #tmpPKeys P7

on P1.TABLE_OWNER = P7.TABLE_OWNER and P1.TABLE_NAME = P7.TABLE_NAME and P1.KEY_SEQ = 1 and P7.KEY_SEQ = 7

left join #tmpPKeys P8

on P1.TABLE_OWNER = P8.TABLE_OWNER and P1.TABLE_NAME = P8.TABLE_NAME and P1.KEY_SEQ = 1 and P8.KEY_SEQ = 8

left join #tmpPKeys P9

on P1.TABLE_OWNER = P9.TABLE_OWNER and P1.TABLE_NAME = P9.TABLE_NAME and P1.KEY_SEQ = 1 and P9.KEY_SEQ = 9

left join #tmpPKeys P10

on P1.TABLE_OWNER = P10.TABLE_OWNER and P1.TABLE_NAME = P10.TABLE_NAME and P1.KEY_SEQ = 1 and P10.KEY_SEQ = 10

left join #tmpPKeys P11

on P1.TABLE_OWNER = P11.TABLE_OWNER and P1.TABLE_NAME = P11.TABLE_NAME and P1.KEY_SEQ = 1 and P11.KEY_SEQ = 11

left join #tmpPKeys P12

on P1.TABLE_OWNER = P12.TABLE_OWNER and P1.TABLE_NAME = P12.TABLE_NAME and P1.KEY_SEQ = 1 and P12.KEY_SEQ = 12

left join #tmpPKeys P13

on P1.TABLE_OWNER = P13.TABLE_OWNER and P1.TABLE_NAME = P13.TABLE_NAME and P1.KEY_SEQ = 1 and P13.KEY_SEQ = 13

left join #tmpPKeys P14

on P1.TABLE_OWNER = P14.TABLE_OWNER and P1.TABLE_NAME = P14.TABLE_NAME and P1.KEY_SEQ = 1 and P14.KEY_SEQ = 14

left join #tmpPKeys P15

on P1.TABLE_OWNER = P15.TABLE_OWNER and P1.TABLE_NAME = P15.TABLE_NAME and P1.KEY_SEQ = 1 and P15.KEY_SEQ = 15

left join #tmpPKeys P16

on P1.TABLE_OWNER = P16.TABLE_OWNER and P1.TABLE_NAME = P16.TABLE_NAME and P1.KEY_SEQ = 1 and P16.KEY_SEQ = 16

left join #tmpPKeys P17

on P1.TABLE_OWNER = P17.TABLE_OWNER and P1.TABLE_NAME = P17.TABLE_NAME and P1.KEY_SEQ = 1 and P17.KEY_SEQ = 17

left join #tmpPKeys P18

on P1.TABLE_OWNER = P18.TABLE_OWNER and P1.TABLE_NAME = P18.TABLE_NAME and P1.KEY_SEQ = 1 and P18.KEY_SEQ = 18

where P1.KEY_SEQ = 1

order by P1.TABLE_OWNER, P1.TABLE_NAME

print '-- Create FK-Constraints'

print '-- keep FK column-ordinal equal to PK column-ordinal'

select 'Alter Table [' + FK1.FKTABLE_OWNER + '].[' + FK1.FKTABLE_NAME + '] WITH NOCHECK add constraint [' + FK1.FK_NAME + '] FOREIGN KEY ( '+

FK1.FKCOLUMN_NAME

+ case when FK2.FKCOLUMN_NAME is null then '' else ', ' + FK2.FKCOLUMN_NAME end

+ case when FK3.FKCOLUMN_NAME is null then '' else ', ' + FK3.FKCOLUMN_NAME end

+ case when FK4.FKCOLUMN_NAME is null then '' else ', ' + FK4.FKCOLUMN_NAME end

+ case when FK5.FKCOLUMN_NAME is null then '' else ', ' + FK5.FKCOLUMN_NAME end

+ case when FK6.FKCOLUMN_NAME is null then '' else ', ' + FK6.FKCOLUMN_NAME end

+ case when FK7.FKCOLUMN_NAME is null then '' else ', ' + FK7.FKCOLUMN_NAME end

+ case when FK8.FKCOLUMN_NAME is null then '' else ', ' + FK8.FKCOLUMN_NAME end

+ case when FK9.FKCOLUMN_NAME is null then '' else ', ' + FK9.FKCOLUMN_NAME end

+ case when FK10.FKCOLUMN_NAME is null then '' else ', ' + FK10.FKCOLUMN_NAME end

+ case when FK11.FKCOLUMN_NAME is null then '' else ', ' + FK11.FKCOLUMN_NAME end

+ case when FK12.FKCOLUMN_NAME is null then '' else ', ' + FK12.FKCOLUMN_NAME end

+ case when FK13.FKCOLUMN_NAME is null then '' else ', ' + FK13.FKCOLUMN_NAME end

+ case when FK14.FKCOLUMN_NAME is null then '' else ', ' + FK14.FKCOLUMN_NAME end

+ case when FK15.FKCOLUMN_NAME is null then '' else ', ' + FK15.FKCOLUMN_NAME end

+ case when FK16.FKCOLUMN_NAME is null then '' else ', ' + FK16.FKCOLUMN_NAME end

+ case when FK17.FKCOLUMN_NAME is null then '' else ', ' + FK17.FKCOLUMN_NAME end

+ case when FK18.FKCOLUMN_NAME is null then '' else ', ' + FK18.FKCOLUMN_NAME end

+ ' ) REFERENCES [' + FK1.PKTABLE_OWNER + '].[' + FK1.PKTABLE_NAME + '] ( ' +

FK1.PKCOLUMN_NAME

+ case when FK2.PKCOLUMN_NAME is null then '' else ', ' + FK2.PKCOLUMN_NAME end

+ case when FK3.PKCOLUMN_NAME is null then '' else ', ' + FK3.PKCOLUMN_NAME end

+ case when FK4.PKCOLUMN_NAME is null then '' else ', ' + FK4.PKCOLUMN_NAME end

+ case when FK5.PKCOLUMN_NAME is null then '' else ', ' + FK5.PKCOLUMN_NAME end

+ case when FK6.PKCOLUMN_NAME is null then '' else ', ' + FK6.PKCOLUMN_NAME end

+ case when FK7.PKCOLUMN_NAME is null then '' else ', ' + FK7.PKCOLUMN_NAME end

+ case when FK8.PKCOLUMN_NAME is null then '' else ', ' + FK8.PKCOLUMN_NAME end

+ case when FK9.PKCOLUMN_NAME is null then '' else ', ' + FK9.PKCOLUMN_NAME end

+ case when FK10.PKCOLUMN_NAME is null then '' else ', ' + FK10.PKCOLUMN_NAME end

+ case when FK11.PKCOLUMN_NAME is null then '' else ', ' + FK11.PKCOLUMN_NAME end

+ case when FK12.PKCOLUMN_NAME is null then '' else ', ' + FK12.PKCOLUMN_NAME end

+ case when FK13.PKCOLUMN_NAME is null then '' else ', ' + FK13.PKCOLUMN_NAME end

+ case when FK14.PKCOLUMN_NAME is null then '' else ', ' + FK14.PKCOLUMN_NAME end

+ case when FK15.PKCOLUMN_NAME is null then '' else ', ' + FK15.PKCOLUMN_NAME end

+ case when FK16.PKCOLUMN_NAME is null then '' else ', ' + FK16.PKCOLUMN_NAME end

+ case when FK17.PKCOLUMN_NAME is null then '' else ', ' + FK17.PKCOLUMN_NAME end

+ case when FK18.PKCOLUMN_NAME is null then '' else ', ' + FK18.PKCOLUMN_NAME end

+ ' ) ' + char(13) + 'GO '

from #tmpFKeys FK1

left join #tmpFKeys FK2

on FK1.FK_NAME = FK2.FK_NAME and FK1.KEY_SEQ = 1 and FK2.KEY_SEQ = 2

left join #tmpFKeys FK3

on FK1.FK_NAME = FK3.FK_NAME and FK1.KEY_SEQ = 1 and FK3.KEY_SEQ = 3

left join #tmpFKeys FK4

on FK1.FK_NAME = FK4.FK_NAME and FK1.KEY_SEQ = 1 and FK4.KEY_SEQ = 4

left join #tmpFKeys FK5

on FK1.FK_NAME = FK5.FK_NAME and FK1.KEY_SEQ = 1 and FK5.KEY_SEQ = 5

left join #tmpFKeys FK6

on FK1.FK_NAME = FK6.FK_NAME and FK1.KEY_SEQ = 1 and FK6.KEY_SEQ = 6

left join #tmpFKeys FK7

on FK1.FK_NAME = FK7.FK_NAME and FK1.KEY_SEQ = 1 and FK7.KEY_SEQ = 7

left join #tmpFKeys FK8

on FK1.FK_NAME = FK8.FK_NAME and FK1.KEY_SEQ = 1 and FK8.KEY_SEQ = 8

left join #tmpFKeys FK9

on FK1.FK_NAME = FK9.FK_NAME and FK1.KEY_SEQ = 1 and FK9.KEY_SEQ = 9

left join #tmpFKeys FK10

on FK1.FK_NAME = FK10.FK_NAME and FK1.KEY_SEQ = 1 and FK10.KEY_SEQ = 10

left join #tmpFKeys FK11

on FK1.FK_NAME = FK11.FK_NAME and FK1.KEY_SEQ = 1 and FK11.KEY_SEQ = 11

left join #tmpFKeys FK12

on FK1.FK_NAME = FK12.FK_NAME and FK1.KEY_SEQ = 1 and FK12.KEY_SEQ = 12

left join #tmpFKeys FK13

on FK1.FK_NAME = FK13.FK_NAME and FK1.KEY_SEQ = 1 and FK13.KEY_SEQ = 13

left join #tmpFKeys FK14

on FK1.FK_NAME = FK14.FK_NAME and FK1.KEY_SEQ = 1 and FK14.KEY_SEQ = 14

left join #tmpFKeys FK15

on FK1.FK_NAME = FK15.FK_NAME and FK1.KEY_SEQ = 1 and FK15.KEY_SEQ = 15

left join #tmpFKeys FK16

on FK1.FK_NAME = FK16.FK_NAME and FK1.KEY_SEQ = 1 and FK16.KEY_SEQ = 16

left join #tmpFKeys FK17

on FK1.FK_NAME = FK17.FK_NAME and FK1.KEY_SEQ = 1 and FK17.KEY_SEQ = 17

left join #tmpFKeys FK18

on FK1.FK_NAME = FK18.FK_NAME and FK1.KEY_SEQ = 1 and FK18.KEY_SEQ = 18

where FK1.KEY_SEQ = 1

order by FK1.FKTABLE_OWNER, FK1.FKTABLE_NAME, FK1.FK_NAME

print '-- Create FK-Indexes'

print '-- keep FK column-ordinal / order equal to PK column-ordinal / order (asc/desc)'

select 'Create index [XFK_' + FK1.FK_NAME + '] on [' + FK1.FKTABLE_OWNER + '].[' + FK1.FKTABLE_NAME + '] ( '+