-3

I would like to dynamically change the name of my clustered indexes on my table, lets say its called (MY_TABLE), so that the Clustered Index created takes the form: PK_MY_TABLE. The problem is that to begin with the indexes have the form: "PK_MY_TABLE_E86234JKFDS" etc. Is there a way to reference such an index in an SQL Statement to reference the Clustered Index of a Table despite the fact that the "_E86234JKFDS" part is unique?

I am looking to create a query that will always work and since index names are always unique that seems to be difficult. Maybe there is a way to reference clustered index of table since there is only one?

Thanks

aruuuuu
  • 1,605
  • 2
  • 22
  • 32
  • 3
    Don't be so lazy -- http://msdn.microsoft.com/en-us/library/ms174979.aspx, read the syntax for constraint with name -- where the constraint is the primary key. – Gary Walker May 30 '14 at 18:13
  • No, I did not overlook your two-part question. I thought maybe you would google this yourself, after being reminded you were being lazy -- Apparently not. Drop the existing clustered index, then create a new clustered index, and likely create a non-clustered version of the original index. You can use sp_rename to rename many things, but since the new index will presumably index different fields than the original index, so sp_rename is not likely to be useful in this case. – Gary Walker May 30 '14 at 18:29
  • http://stackoverflow.com/questions/16020028/sql-server-query-to-find-clustered-indexes -- Any finally, here is a prior question that specifically addresses finding the existing clustered indexes – Gary Walker May 30 '14 at 18:31
  • 1
    My friend. Index names are generated by the database in this case and are unique. How do you suggest I reference them when they have all these unique characters after? – aruuuuu May 30 '14 at 18:31
  • create table ( fields ... constraint name primary key(fields) ) – Gary Walker May 30 '14 at 18:31
  • 1
    I'm not sure but my guess is you've got -2 because you've asked something that can be resolved either by looking up the manual or by searching for an existing solution. – Andriy M May 30 '14 at 18:33
  • Yup... and the link I sent you has not been asked a million times and is not searchable on google either – aruuuuu May 30 '14 at 18:35
  • As a side point Gary I saw that link you sent me , it is not helpful. If you will kindly read my question again I am trying to find one clustered index for one table – aruuuuu May 30 '14 at 18:37
  • 2
    Getting the name of the CI for a table is trivial: `SELECT name FROM sys.indexes WHERE type = 1 AND object_id = OBJECT_ID('schema.MyTable')` – JNK May 30 '14 at 18:44

2 Answers2

1

This should get you started, although it's not clear what you're really trying to do with them.

SELECT
    'PK_' + tables.name NewName,
    indexes.name CurrentName
FROM sys.indexes join sys.tables ON indexes.object_id = tables.object_id
WHERE indexes.type_desc = 'CLUSTERED'

Further, keep in mind that you can have collisions if you have named tables the same in separate schemas. For example MySchema.SomeTable and AnotherSchema.SomeTable would both try to use the clustered index name PK_SomeTable, which is not allowable.

Yuck
  • 49,664
  • 13
  • 105
  • 135
0

First of all understand one thing which is Primary key constraint on table create clustered index automatically if no clustered index already exist on the table.So solution to your first question requires you to first create table without creating primary key.And after the table is creating without primary key,just use the below syntax to dynamically create clustered index on table.

Create clustered index [Index_Name] on [TableName] (ColumnName)

And for renaming clustered index on table dynamically you can use below T-SQL Script

EXEC sp_rename N'SchemaName.TableName.IndexName', N'New_IndexName', N'INDEX';

Mitesh
  • 108
  • 1
  • 11