February 13, 2002 at 6:38 pm
I am trying to put together a SQL-DMO application that will aid in moving a large number of indexes to different filegroups.
Two questions:
1) How can I tell if the column(s) in the index are in ascending vs descending order?
2) What is the main difference between the Key object and the Index object?
I've been through the usual suspects (BOL, Google, MSDN, etc.) but haven't found an answer.
Thank-you.
Steve Hendricks
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
February 14, 2002 at 8:30 am
From BOL, this about the key object:
SQL Server tables can contain key constraints. The constraints apply declarative referential integrity to the data contained in the table. Keys can be primary or foreign. A single primary key can be defined on a table, though many foreign keys can be defined, constraining data for a column or columns to values existing as primary key values in other tables.
With the Key object, you can:
·Define a PRIMARY KEY constraint for a SQL Server table.
·Remove a PRIMARY KEY constraint from a SQL Server table.
·Define a FOREIGN KEY constraint for a SQL Server table.
·Remove a FOREIGN KEY constraint from a SQL Server table.
·Rebuild the index used to maintain a PRIMARY KEY constraint on a table.
Its where the line blurs between constraints and indexes. I think this will give you what you're looking for.
Dim oServer As SQLDMO.SQLServer
Dim otable As SQLDMO.Table
Dim oIndex As SQLDMO.Index2
Dim oColumn As SQLDMO.Column
Dim vColumns As Variant
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect
End With
Set otable = oServer.Databases("Northwind").Tables("Categories")
For Each oIndex In otable.Indexes
Set vColumns = oIndex.ListIndexedColumns
For Each oColumn In vColumns
Debug.Print oIndex.Name, oColumn.Name, oIndex.GetIndexedColumnDESC(oColumn.Name)
Next
Set vColumns = Nothing
Next
Set otable = Nothing
oServer.DisConnect
Set oServer = Nothing
Andy
February 14, 2002 at 9:50 am
Andy,
Thank-you. This is useful information.
The .GetIndexedColumnDESC is in the Object Browser (Surprise! Surprise!). I must have been blurry-eyed when I was trying to find this.
It looks like the Key object also handles the Unique constraint index information.
Is it safe to say that all Keys are Indexes but that not all Indexes are Keys?
Thanks, again.
Steve
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
February 14, 2002 at 10:59 am
I think that is true about index vs constraint. Just works out that some constraints are implemented with indexes. As far as the object browser if you happened to look at index instead of index2 that would explain missing it. Index even has a write only property - don't see those very often!
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply