Solved

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

Posted on 2003-10-29
7
284 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 34

Expert Comment

by:arbert
Comment Utility
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
Comment Utility
"I see" said the blind man.

Thanks,
Anthony
0
 

Author Comment

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



0
 
LVL 34

Expert Comment

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

Brett
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now