Query to find the names of all the unique indexed columns.

Question

Post reply

Query to find the names of all the unique indexed columns.

karthik srinivasan

Old Hand

Points: 317
More actions
May 6, 2005 at 12:18 pm

#88387

I need a query to find out the names of all the columns having unique indexes for a particular table in Sql Server 8.0.
Can anyone in this forum help me out by giving a query ?
This is urgent.
Thanks and regards,
Karthik

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = 'tablename' AND CONSTRAINT_TYPE = 'UNIQUE'

karthik srinivasan Old Hand Points: 317 More actions · Answer 2

Hi ...

Thanks a lot for your query and response.

Actually I need the names of all the columns (NOT THE NAMES OF CONSTRAINTS) having unique indexes for a particular table in Sql Server 8.0

INFORMATION_SCHEMA.TABLE_CONSTRAINTS will just give me the names of the constraints.

Can you help me with another query ?

I think we can join sysindexes, sysobjects, sysconstraints, syscolumns etc to arrive at a query. You can think about this.

Thanks and regards,

Karthik

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

This will give you the columns too... but you'll have to filter out the constraint types using the first query I gave you..

Select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'tname'

karthik srinivasan Old Hand Points: 317 More actions · Answer 4

Hi...

Thanks once again.

This view INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE does not return me all the columns/constraints of a particular table. It returned me only one constraint details and it seemed to be a PK constraint.

Also, even if we are able to achieve the requirement using this view, now my boss is not accepting it because he says that master.dbo.*views , information schema views can't be executed by all the intended audiences of this query.

So can you give me a query using sysindexes, sysobjects, syscolumns etc ?

Regards,

Karthik

Thanks and regards,

Karthik

karthik srinivasan Old Hand Points: 317 More actions · Answer 5

hello members ...

I'm still finding a query. has anyone got a query for my requirement ?

thanks and regards,

karthik

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Pipe the results from sp_helpindex into a table and query this.

The following query is not exactly what you've asked for, but I think this will get you going:

SELECT

CAST(SO.[name] AS CHAR(20)) AS TableName

, CAST(SI.[name] AS CHAR(30)) AS IndexName

, CAST(SC.[name] AS CHAR(15)) AS ColName

, CAST(ST.[name] AS CHAR(10)) AS TypeVal

, CASE

WHEN (SI.status & 16)<>0 THEN 'Yes' ELSE 'No'

END AS ClusteredIndex

FROM

SYSOBJECTS SO

INNER JOIN

SYSINDEXES SI

INNER JOIN

SYSINDEXKEYS SIK

ON

SIK.[id] = SI.[id]

AND

SIK.indid = SI.indid

INNER JOIN

SYSCOLUMNS SC

INNER JOIN

SYSTYPES ST

ON

SC.xtype = ST.xtype

ON

SIK.[id] = SC.[id]

AND

SIK.colid = SC.colid

ON

SO.[id] = SI.[id]

WHERE

SO.xtype = 'u'

AND

SI.indid > 0

AND

SI.indid < 255

AND

(SI.status & 64)=0

ORDER BY

TableName

, IndexName

, SIK.keyno

Tell you boss if he is planning to move to the next version of SQL Server, such queries might not work anymore.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

karthik srinivasan Old Hand Points: 317 More actions · Answer 7

Dear Helper ...

Thanks a lot for your query... I have modified your query for my requirement. Its completed now.

Really your query helped me. Below is the new query ... FYI ....

Regards,

karthik

SELECT

SC.[name] AS ColName

FROM

SYSOBJECTS SO

INNER JOIN

SYSINDEXES SI

INNER JOIN

SYSINDEXKEYS SIK

ON

SIK.[id] = SI.[id]

AND

SIK.indid = SI.indid

INNER JOIN

SYSCOLUMNS SC

INNER JOIN

SYSTYPES ST

ON

SC.xtype = ST.xtype

ON

SIK.[id] = SC.[id]

AND

SIK.colid = SC.colid

ON

SO.[id] = SI.[id]

WHERE

SO.xtype = 'U'

AND

(SI.status & 2) <> 0

AND

(SI.status & 4096) = 0

AND

SO.[name] = 'SID_ACTIVITY'

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]