Solved

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

Posted on 2003-10-29
7
287 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

821 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