July 22, 2002 at 12:56 pm
Hello everyone,
I had posted this question before but did not get an answer. I want to know what indexes exist in my database, what are their names, and to which table they belong to. I want to know which table(s) hold this information. Thanks in advance for your help.
July 22, 2002 at 12:57 pm
Sorry, I have no idea how this topic was posted twice, please ignore the other thread and reply to this one only. Thank you.
July 22, 2002 at 1:07 pm
sysindexes holds this information. In Enterprise Manager, each table will also have this information. What verison of SQL are you using?
Whatver verion, right click a table and choose indexes or all tasks/indexes
Steve Jones
July 23, 2002 at 3:52 am
sp_helpindex is also useful (check the syntax in BOL)
July 23, 2002 at 10:53 am
We are running 2000.
I want to run a sql statement that will give me the name of index, which table it belongs to, and which filegroup it is stored in. Some of our users have not used the "Indexes" filegroup for their indexes and I just want to find the indexes that have been created on the wrong filegroup instead of going through all the tables one by one. Can someone help me with the sql statement that I need to run? Thanks a lot!
July 23, 2002 at 11:19 am
As a side-question, what command do I use to move an index to another filegroup?
July 24, 2002 at 11:21 am
I have written the following sql statement, but it doesn't return the user indexes. If I take out the "b.name NOT LIKE 'sys%' " part, I get all the indexes for the sys tables, but I am not interested in those indexes, I want the user indexes. How can I change this query? What table will give me the user indexes? Please, please help. Thank you.
select a.name as indexname, b.name as tablename, c.groupname
from sysindexes a, sysobjects b, sysfilegroups c
where a.indid = b.id and
c.groupid = a.groupid and
b.name NOT LIKE 'sys%'
July 24, 2002 at 3:42 pm
There is got to be someone out there who could help me with this...please...
July 25, 2002 at 11:10 am
Because no one has responded, I am beginning to think that SQL Server is unable to provide such information. Is that correct? Thanks in advance.
July 25, 2002 at 11:23 am
Try this:
SELECT USER_NAME(O.uid) as [owner],
O.name as [tablename],
I.name as [indexname],
FILEGROUP_NAME(I.groupid) as [filegroup]
FROM sysobjects O,sysindexes I
WHERE I.indid>0
AND I.indid<255
AND I.status&2048!=2048
AND I.status&4096!=4096
AND I.id=O.id
AND type in ('U','V')
AND (INDEXPROPERTY(I.id,I.name,'IsStatistics') <> 1)
AND (INDEXPROPERTY(I.id,I.name,'IsAutoStatistics') <> 1)
AND (INDEXPROPERTY(I.id,I.name,'IsHypothetical') <> 1)
AND O.type!='S'
ORDER BY USER_NAME(O.uid),O.name,I.name
HTH,
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
July 25, 2002 at 12:53 pm
Wow, that works, thanks a million! I will study your scripts and figure out why my query did not return the user indexes. Thanks again!
July 25, 2002 at 5:09 pm
Shahgols,
Please be patient. We do a fairly high volume of posts these days and sometimes we fall behind and/or miss a few. All of us have full time jobs too, so its a balancing act. If you post again in the future and don't get a reply within a couple days, please feel free to email one of us and we'll see what we can do!
Andy
July 29, 2002 at 3:27 am
quote:
I have written the following sql statement, but it doesn't return the user indexes<SNIP>select a.name as indexname, b.name as tablename, c.groupname
from sysindexes a, sysobjects b, sysfilegroups c
where a.indid = b.id and
c.groupid = a.groupid and
b.name NOT LIKE 'sys%'
the error is the join between sysindexes and sysobjects. It should be sysindexes.id = sysobjects.id NOT sysindexes.indid = ... The id in sysindexes refers not to the index itself but the id of the object to which it belongs (mostly - see BOL sysindexes entry for special cases of heap and text pointers - indid = 0 or indid = 255).
BTW using the obvious aliases (image the sys- prefix is "silent") for e.g. sysindexes i, sysobjects o, sysfilegroups f, makes queries so much more readable at no extra "cost".
July 29, 2002 at 10:12 am
If you need to move a few indexes, Enterprise Manager will let you change the location. Bring the table up in Design mode, show the "Table and Index Properties", under the "Indexes/Keys" tab you can change the Index Filegroup.
If you need to move a number of indexes, this link (http://www.ocsqlsug.org/) points to source code for a DMO based application (in VB6) that allows you to move indexes in bulk.
HTH
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
July 29, 2002 at 3:17 pm
Thanks everyone.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy