How to drop all primary key of all tables in DB?

How to drop all primary key of all tables in DB?
After reading a document for explaning how to use clustered index, I knew
set all primary key to be clustered is not correct for me.
I would like to drop them. recreate a new nonclustered primary key and
create other clustered indexes.
Now I would like to know how to drop all primary key of all tables in DB by
T-SQL, because there are too many tables to drop primary key by hand.
Thanks
--UsingSQL2005DevFrank
declare @.pklist table
(
ident int identity(1, 1),
pkname sysname,
tablename sysname
)
insert into
@.pklist
(
pkname,
tablename
)
select
constraint_name,
table_name
from
information_schema.table_constraints
where
constraint_type = 'primary key'
set @.counter = @.@.rowcount
while @.counter > 0
begin
select @.constraint = pkname, @.table = tablename from @.pklist where ident =
@.counter
exec ('alter table [' + @.table + '] drop constraint [' + @.constraint + ']')
set @.counter = @.counter - 1
end
"Frank Lee" <Reply@.to.newsgroup> wrote in message
news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
> How to drop all primary key of all tables in DB?
> After reading a document for explaning how to use clustered index, I knew
> set all primary key to be clustered is not correct for me.
> I would like to drop them. recreate a new nonclustered primary key and
> create other clustered indexes.
> Now I would like to know how to drop all primary key of all tables in DB
> by T-SQL, because there are too many tables to drop primary key by hand.
> Thanks
> --UsingSQL2005Dev
>|||Hi
You should extend Uri's solution so that all Foreign Keys that reference
your Primary Key is removed before trying to remove the Primary Key.
John
"Frank Lee" <Reply@.to.newsgroup> wrote in message
news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
> How to drop all primary key of all tables in DB?
> After reading a document for explaning how to use clustered index, I knew
> set all primary key to be clustered is not correct for me.
> I would like to drop them. recreate a new nonclustered primary key and
> create other clustered indexes.
> Now I would like to know how to drop all primary key of all tables in DB
> by T-SQL, because there are too many tables to drop primary key by hand.
> Thanks
> --UsingSQL2005Dev
>|||I see. Thx.
"John Bell" <jbellnewsposts@.hotmail.com> glsD:eNvQsxGEGHA.3892@.TK2MSFTNGP10.phx.g
bl...
> Hi
> You should extend Uri's solution so that all Foreign Keys that reference
> your Primary Key is removed before trying to remove the Primary Key.
> John
> "Frank Lee" <Reply@.to.newsgroup> wrote in message
> news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
>