How to find components of composite key

Question

How to find components of composite key

ib-321506

Valued Member

Points: 68
More actions
July 1, 2007 at 8:09 pm

#179601

Hello,
Say I have a composite index, how can I programmatically find the components knowing only the table name?
For example, an index was created as follows:
CREATE UNIQUE INDEX [some_table_index] ON [some_table]([col1], [col2]) ON [PRIMARY]
Now, I'd like to get back 'col1' and 'col2' when I search for 'some_table'.
Thank you

Viewing 9 posts - 1 through 8 (of 8 total)

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

The follow will give you the column names in every index for a table. You should be able to derive the CREATE UNDEX statements from this.

select object_name (SI.id), SI.name, SC.Name from sysindexes SI

Inner Join sysindexkeys SIK

ON SIK.INDID = SI.INDID

AND SIK.ID = SI.ID

Inner Join syscolumns SC

ON SC.COLID = SIK.ColID

AND SC.ID = SI.ID

Where Object_name (SI.ID) = 'table name'

Order By 1, 2, 3

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

You might want to add something else to exclude statisics...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

sp_helpindex 'indexname' should give you the details of the index and index keys.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 4

sp_helpindex will give index keys with comma separated, instead use sp_MShelpindex.

create table #table1(name varchar(1000),status int,

indid int,OrigFillFactor int,IndCol1 varchar(128),IndCol2 varchar(128),IndCol3 varchar(128),

IndCol4 varchar(128),IndCol5 varchar(128),IndCol6 varchar(128),IndCol7 varchar(128),

IndCol8 varchar(128),IndCol9 varchar(128),IndCol10 varchar(128),IndCol11 varchar(128),

IndCol12 varchar(128),IndCol13 varchar(128),IndCol14 varchar(128),IndCol15 varchar(128),

IndCol16 varchar(128),segname varchar(128),FullTextKey int,descending int,

computed int,istable int)

insert into #table1 exec sp_mshelpindex authors

select * from #table1 where indid > 0 and indid < 255 and (status & 64)=0 order by indid

drop table #table1

Regards
Shrikant Kulkarni

Lowell SSC Guru Points: 323518 More actions · Answer 5

hate to point out the obvious, but with only the tablename, sp_help lists the name of the index, and of course the column components of the indexes as well.

I had a snippet I wrote saved from a while back...I adapted it to a stored proc to return all indexes on a given tablename...this proc created the CREATE INDEX statements....

typical results:

CREATE UNIQUE CLUSTERED INDEX [PK__GMACT__1E05700A] ON [GMACT] (ACTTBLKEY) WITH FILLFACTOR = 90

CREATE UNIQUE INDEX [UQ__GMACT__1EF99443] ON [GMACT] (ACTNBR) WITH FILLFACTOR = 90

CREATE INDEX [ACTINDEX] ON [GMACT] (ACTNBR) WITH FILLFACTOR = 90

CREATE INDEX [ACTNAMEINDEX] ON [GMACT] (ACTNAME) WITH FILLFACTOR = 90

--PR_ListTableIndexes GMACT

CREATE PROCEDURE PR_ListTableIndexes (@TABLENAME VARCHAR(60))

AS

BEGIN

SELECT

REPLICATE(' ',4000) AS COLNAMES ,

OBJECT_NAME(I.ID) AS TABLENAME,

I.ID AS TABLEID,

I.INDID AS INDEXID,

I.NAME AS INDEXNAME,

I.STATUS,

INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') AS ISUNIQUE,

INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') AS ISCLUSTERED,

INDEXPROPERTY (I.ID,I.NAME,'INDEXFILLFACTOR') AS INDEXFILLFACTOR

INTO #TMP

FROM SYSINDEXES I

WHERE I.INDID > 0

AND I.INDID < 255

AND (I.STATUS & 64)=0

AND I.ID = OBJECT_ID(@TABLENAME)

--uncomment below to eliminate PK or UNIQUE indexes;

--what i call 'normal' indexes

--AND INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') =0

--AND INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') =0

DECLARE

@ISQL VARCHAR(4000),

@TABLEID INT,

@INDEXID INT,

@MAXTABLELENGTH INT,

@MAXINDEXLENGTH INT

--USED FOR FORMATTING ONLY

SELECT @MAXTABLELENGTH=MAX(LEN(TABLENAME)) FROM #TMP

SELECT @MAXINDEXLENGTH=MAX(LEN(INDEXNAME)) FROM #TMP

DECLARE C1 CURSOR FOR

SELECT TABLEID,INDEXID FROM #TMP

OPEN C1

FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID

WHILE @@FETCH_STATUS <> -1

BEGIN

SET @ISQL = ''

SELECT @ISQL=@ISQL + ISNULL(SYSCOLUMNS.NAME,'') + ','

FROM SYSINDEXES I

INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID

INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID

WHERE I.INDID > 0

AND I.INDID < 255

AND (I.STATUS & 64)=0

AND I.ID=@TABLEID AND I.INDID=@INDEXID

ORDER BY SYSCOLUMNS.COLID

UPDATE #TMP SET COLNAMES=@ISQL WHERE TABLEID=@TABLEID AND INDEXID=@INDEXID

FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID

END

CLOSE C1

DEALLOCATE C1

--AT THIS POINT, THE 'COLNAMES' COLUMN HAS A TRAILING COMMA

UPDATE #TMP SET COLNAMES=LEFT(COLNAMES,LEN(COLNAMES) -1)

SELECT 'CREATE '

+ CASE WHEN ISUNIQUE = 1 THEN ' UNIQUE ' ELSE ' ' END

+ CASE WHEN ISCLUSTERED = 1 THEN ' CLUSTERED ' ELSE ' ' END

+ ' INDEX [' + UPPER(INDEXNAME) + ']'

+ SPACE(@MAXINDEXLENGTH - LEN(INDEXNAME))

+' ON [' + UPPER(TABLENAME) + '] '

+ SPACE(@MAXTABLELENGTH - LEN(TABLENAME))

+ '(' + UPPER(COLNAMES) + ')'

+ CASE WHEN INDEXFILLFACTOR = 0 THEN '' ELSE ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) END --AS SQL

FROM #TMP

WHERE UPPER(TABLENAME) = UPPER(@TABLENAME)

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

That green code is sure hard to read, Lowell...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 7

thanks Jeff...force of habit I guess.... I like to make code different from comments, so it's obvious what to copy/paste.

I changed it to syntax highlighting as best I could...certainly more readable now.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 8

shrikant-279400

SSCommitted

Points: 1651

July 3, 2007 at 1:26 am

#716766

Its cool lowell!! gr8!!

Regards
Shrikant Kulkarni