Solved

Where is the 'unique' property and details about the columns covered by an index stored?

Posted on 2003-10-29
7
290 Views
Last Modified: 2012-08-14
Hello All,

How do I find out wether an index is unique in my sql code? Also, how can I get the names of the columns covered by an index (composite or non-composite) in plain text.

I made a fake unique non clustered index on 2 fields and from enterprise manager when I double click on the index it correctly shows that it is unique and the fields it is set on, but I can't find the table this is stored. I tried looking in the INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE table but there is no constraint entry there (isn't unique a constraint ?) Also, the closest thing to finding what columns are covered by the index is the 'keys' column in the sysindexes table but that is saved in a binary format which I don't exactly know how to convert to plain text

I have to go now because all this blue font is making me dizzy

And YES... I am smarter than the average bear

(:0)->-<
------------------------------------------------------------------------

0
Comment
Question by:YogiMySonMySon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 

Author Comment

by:YogiMySonMySon
ID: 9647850
basically my aim is to end up with a way of finding columns covered by an index and wether or not it is unique through Transact-SQL
0
 
LVL 34

Accepted Solution

by:
arbert earned 50 total points
ID: 9647897
Does:

sp_helpidex 'tablename'

return all the information you need?  Of course, since you are smarter than the average bear (lol), you can insert the results of the procedure into a temp table and use it however you need it...

Brett
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9651908
Brett,

There is a typo there, it should be:
sp_helpindex 'tablename'

Can you remind me the syntax to insert the data from a stored procedure into a temporary table, I cannot seem to figure it out.  I know it is something like:

Select * Into #Temp From exec sp_helpindex 'tablename'

But that is not right.

Anthony
As Winnie-the-Pooh would say, "I am a bear with a very small brain"
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 34

Expert Comment

by:arbert
ID: 9653290
oops...

Not that easy, you have to create the table first, then issue the insert.

create table blah (
whatever
)

insert into blah
exec sp_helpindex


tigger
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9653447
"I see" said the blind man.

Thanks,
Anthony
0
 

Author Comment

by:YogiMySonMySon
ID: 9655150
Thanks for the help mate. Sorry I did not reply yesterday, I had to pick someone up.



0
 
LVL 34

Expert Comment

by:arbert
ID: 9655298
That's cool.  Wasn't sure if that would do what you needed or not.

Brett
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question