Changing PK Names

Hi,
I need to change the primary key names from
the 'PK_tablename' format to 'tablename_pk' format. Is
there any other place where the constraint name stored
than sysobjects and sysindexes table '
Is there an easy way to do this for 100 user tables ?
Thanks.Hi,
A PRIMARY KEY has a references in sysobjects as well as sysindexes. You can
use the below script to rename the Primary Key
sp_rename 'PK_tablename' , 'tablename_pk' , 'object'
Thanks
Hari
MCDBA
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:15b3501c41678$2b9d1d20$a501280a@.phx
.gbl...
> Hi,
> I need to change the primary key names from
> the 'PK_tablename' format to 'tablename_pk' format. Is
> there any other place where the constraint name stored
> than sysobjects and sysindexes table '
> Is there an easy way to do this for 100 user tables ?
> Thanks.|||This may help:
DECLARE @.vTableName varchar(128)
DECLARE @.vObjectID int -- the object id of the table
DECLARE @.vIndexID smallint -- the index id of an index
DECLARE @.vIndexName sysname
DECLARE @.vNewName sysname
/*
Open Cursor over Tables which have pk indexes
*/
DECLARE table_cursor CURSOR LOCAL STATIC FOR
SELECT '[dbo].['+[name]+']' AS table_name, [id]
FROM sysobjects
WHERE xtype = 'U' AND (status & 64)=0
AND status > 0
AND [id] IN (
SELECT [id]
FROM sysindexes
WHERE indid > 0
AND indid < 255
AND (status & 64)=0
AND [name] LIKE 'PK_%')
OPEN table_cursor
FETCH table_cursor INTO @.vTableName, @.vObjectID
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
DECLARE index_cursor CURSOR LOCAL STATIC FOR
SELECT indid, [name]
FROM sysindexes
WHERE id = @.vObjectID
AND indid > 0
AND indid < 255
AND (status & 64)=0
AND [name] LIKE 'PK_%'
ORDER BY indid
OPEN index_cursor
FETCH index_cursor INTO @.vIndexID, @.vIndexName
-- IF NO INDEX, QUIT
IF @.@.FETCH_STATUS < 0
BEGIN
DEALLOCATE index_cursor
RAISERROR(15472,-1,-1) --'Object does not have any indexes.'
RETURN (0)
END
WHILE @.@.fetch_status >= 0
BEGIN
SET @.vNewname = REPLACE(LOWER(@.vIndexName), 'pk_', '') + '_pk'
/*
TODO: Change print to EXEC .. EXEC('EXEC sp_rename...)
*/
PRINT 'EXEC sp_rename ' + @.vIndexName + ', ' + @.vNewName
FETCH index_cursor INTO @.vIndexID, @.vIndexName
END
CLOSE index_cursor
DEALLOCATE index_cursor
END
FETCH NEXT FROM table_cursor INTO @.vTableName, @.vObjectID
END
CLOSE table_cursor
DEALLOCATE table_cursor
Christopher Winn
Business Intelligence Engineer
Edugration
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:15b3501c41678$2b9d1d20$a501280a@.phx
.gbl...
> Hi,
> I need to change the primary key names from
> the 'PK_tablename' format to 'tablename_pk' format. Is
> there any other place where the constraint name stored
> than sysobjects and sysindexes table '
> Is there an easy way to do this for 100 user tables ?
> Thanks.|||Thanks.

>--Original Message--
>This may help:
>DECLARE @.vTableName varchar(128)
>DECLARE @.vObjectID int -- the object id of the table
>DECLARE @.vIndexID smallint -- the index id of an index
>DECLARE @.vIndexName sysname
>DECLARE @.vNewName sysname
>/*
> Open Cursor over Tables which have pk indexes
>*/
>DECLARE table_cursor CURSOR LOCAL STATIC FOR
> SELECT '[dbo].['+[name]+']' AS table_name, [id]
> FROM sysobjects
> WHERE xtype = 'U' AND (status & 64)=0
> AND status > 0
> AND [id] IN (
> SELECT [id]
> FROM sysindexes
> WHERE indid > 0
> AND indid < 255
> AND (status & 64)=0
> AND [name] LIKE 'PK_%')
>OPEN table_cursor
>FETCH table_cursor INTO @.vTableName, @.vObjectID
>WHILE (@.@.fetch_status <> -1)
>BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
>
> DECLARE index_cursor CURSOR LOCAL STATIC FOR
> SELECT indid, [name]
> FROM sysindexes
> WHERE id = @.vObjectID
> AND indid > 0
> AND indid < 255
> AND (status & 64)=0
> AND [name] LIKE 'PK_%'
> ORDER BY indid
> OPEN index_cursor
> FETCH index_cursor INTO @.vIndexID, @.vIndexName
> -- IF NO INDEX, QUIT
> IF @.@.FETCH_STATUS < 0
> BEGIN
> DEALLOCATE index_cursor
> RAISERROR(15472,-1,-1) --'Object does not have any
indexes.'
> RETURN (0)
> END
> WHILE @.@.fetch_status >= 0
> BEGIN
> SET @.vNewname = REPLACE(LOWER(@.vIndexName), 'pk_', '')
+ '_pk'
> /*
> TODO: Change print to EXEC .. EXEC('EXEC
sp_rename...)
>*/
>PRINT 'EXEC sp_rename ' + @.vIndexName + ', ' + @.vNewName
> FETCH index_cursor INTO @.vIndexID, @.vIndexName
> END
> CLOSE index_cursor
> DEALLOCATE index_cursor
>
> END
> FETCH NEXT FROM table_cursor INTO @.vTableName, @.vObjectID
>END
>CLOSE table_cursor
>DEALLOCATE table_cursor
>Christopher Winn
>Business Intelligence Engineer
>Edugration
>"Frank" <anonymous@.discussions.microsoft.com> wrote in
message
> news:15b3501c41678$2b9d1d20$a501280a@.phx
.gbl...
>
>.
>