Unique key indexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Unique key indexes

Author

Topic

ourspt
Starting Member

33 Posts

Posted - 2005-06-15 : 05:39:17

Hi,

I want to know the way to identify which tables have a unique key index and for those indexes, which keys are a part of it, in a SQL Server database.

In sysindexes table, I would know which tables have unique indexes. Also, the columns on which the unique index is built are available as 'key' field. But how do I get the actual column names?

Thanks in advance
ourspt

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-15 : 07:22:33

[code]
Select Table_Name, Column_Name from Information_Schema.CONSTRAINT_COLUMN_USAGE
where left(Constraint_name,2)='UQ'
[/code]

Madhivanan

Failing to plan is Planning to fail

ourspt
Starting Member

33 Posts

Posted - 2005-06-23 : 01:38:15

Dear Madhivanan,

Your query works only if I follow the naming convention that all unique indexes start with 'UQ'. What if no naming convention was followed? Is there any column that tells that the particular index is a unique index, instead of basing our output on 'naming convention'?

Thanks in advance
ourspt

Tubes
Starting Member

1 Post

Posted - 2005-06-24 : 05:49:31

You can output from a stored procedure into a temporary table i.e.

CREATE TABLE TEMPINDS (TABLE_QUALIFIER sysname, TABLE_OWNER sysname, TABLE_NAME sysname, NON_UNIQUE smallint NULL, INDEX_QUALIFIER sysname NULL, INDEX_NAME sysname NULL, TYPE smallint, SEQ_IN_INDEX smallint NULL, COLUMN_NAME sysname NULL, COLLATION char(1) NULL, CARDINALITY int, PAGES int, FILTER_CONDITION varchar(128) NULL)

INSERT TEMPINDS EXEC sp_statistics @table_name

Now it's simply a case of SELECTing from the temp table. See T_SQL help (sp_statistics) for a description of each column.

Regards

Tubes.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-24 : 06:00:11

I think this is enough
sp_helpindex 'TableName'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources