How do i find indexes of the columns in SQL server 2000??

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000

Thanks a lot(001frien@.gmail.com) writes:

Quote:

Originally Posted by

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000

Here is a query. It lists only the five first index column, but you can
easily augment it if needed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

Here is a query. It lists only the five first index column, but you can
easily augment it if needed.

What was my INSERT-key up to last night? I missed to paste in the
query I was talking about. Here it is:

SELECT o.name, i.name,
col1 = MIN (CASE ik.keyno WHEN 1 THEN c.name END),
col2 = MIN (CASE ik.keyno WHEN 2 THEN c.name END),
col3 = MIN (CASE ik.keyno WHEN 3 THEN c.name END),
col4 = MIN (CASE ik.keyno WHEN 4 THEN c.name END),
col5 = MIN (CASE ik.keyno WHEN 5 THEN c.name END)
FROM sysobjects o
JOIN sysindexes i ON i.id = o.id
JOIN sysindexkeys ik ON ik.id = i.id
AND ik.indid = i.indid
JOIN syscolumns c ON c.id = ik.id
AND c.colid = ik.colid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(o.id, i.name, 'IsStatistics') = 0
AND indexproperty(o.id, i.name, 'IsHypothetical') = 0
GROUP BY o.name, i.name
ORDER BY o.name, i.name

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks a lot Erland

Erland Sommarskog wrote:

Quote:

Originally Posted by

Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

Here is a query. It lists only the five first index column, but you can
easily augment it if needed.

>
What was my INSERT-key up to last night? I missed to paste in the
query I was talking about. Here it is:
>
SELECT o.name, i.name,
col1 = MIN (CASE ik.keyno WHEN 1 THEN c.name END),
col2 = MIN (CASE ik.keyno WHEN 2 THEN c.name END),
col3 = MIN (CASE ik.keyno WHEN 3 THEN c.name END),
col4 = MIN (CASE ik.keyno WHEN 4 THEN c.name END),
col5 = MIN (CASE ik.keyno WHEN 5 THEN c.name END)
FROM sysobjects o
JOIN sysindexes i ON i.id = o.id
JOIN sysindexkeys ik ON ik.id = i.id
AND ik.indid = i.indid
JOIN syscolumns c ON c.id = ik.id
AND c.colid = ik.colid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(o.id, i.name, 'IsStatistics') = 0
AND indexproperty(o.id, i.name, 'IsHypothetical') = 0
GROUP BY o.name, i.name
ORDER BY o.name, i.name
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How do i find indexes of the columns in SQL server 2000??

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000
Thanks a lotselect object_name(si.id), si.name from Sysindexes SI inner join
Sysojbects SO ON
(si.id = so.id) where xtype = 'u'
001fr...@.gmail.com wrote:
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
> Thanks a lot|||This query will return a list of indexed columns that are NOT primary keys.
SELECT
TableName = cast( TABLE_NAME as varchar(50)),
ColumnName = cast( COLUMN_NAME as varchar(50))
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN dbo.sysindexes i
ON ( c.TABLE_NAME = object_name( i.id )
AND c.COLUMN_NAME = i.name
)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<001frien@.gmail.com> wrote in message news:1163193099.763199.289570@.k70g2000cwa.googlegroups
.com...
>
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
>
> Thanks a lot
>

How do i find indexes of the columns in SQL server 2000??

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000
Thanks a lot
select object_name(si.id), si.name from Sysindexes SI inner join
Sysojbects SO ON
(si.id = so.id) where xtype = 'u'
001fr...@.gmail.com wrote:
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
> Thanks a lot
|||This query will return a list of indexed columns that are NOT primary keys.
SELECT
TableName = cast( TABLE_NAME as varchar(50)),
ColumnName = cast( COLUMN_NAME as varchar(50))
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN dbo.sysindexes i
ON ( c.TABLE_NAME = object_name( i.id )
AND c.COLUMN_NAME = i.name
)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
<001frien@.gmail.com> wrote in message news:1163193099.763199.289570@.k70g2000cwa.googlegr oups.com...
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
> Thanks a lot
>

How do i find indexes of the columns in SQL server 2000??

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000
Thanks a lot
See the responses in microsoft.public.sqlserver.server
Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"frien" <001frien@.gmail.com> wrote in message
news:1163196068.628580.97620@.b28g2000cwb.googlegro ups.com...
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
> Thanks a lot
>
|||frien (001frien@.gmail.com) writes:
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
Answered in comp.databases.ms-sqlserver. Please do not post the same
question, independetly to different newsgroups.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx