how to select all user indexes? 
Author Message
 how to select all user indexes?

The _WA_* "indexes" are not really indexes at all. They are statistics that
SQL Server has created because you have the "Auto Create Statistics" option
set ON for the database.

I would recommend that users not have the rights to create indexes if you
are worried about extraneous indexes being created by users who should not
be doing so. Indexes are always owned by the owner of the table. As far as I
know, there is no way to query after the fact who created the index. A user
would have to be the owner of the database or the owner of the table to
create an index (or they might all be running with "sa" rights). You could
use Profiler to audit certain events, but I think that might be overkill.
Consider removing rights from those users who shouldn't have them.

--
David Gugick
Intrinsic Design, Inc.
Coefficient - Database Analysis for Microsoft SQL Server
http://www.***.com/



Tue, 09 Dec 2003 06:20:26 GMT
 how to select all user indexes?

Thanks for your assistance David.

I am not looking for indexes created by a particular
user.  The question I have is that I want to query a
database to get all indexes, I will delete all those, and
then create all new and improved indexes with a proper
naming convention included.

The problem I have is getting the index name from each
table.

Thanks again,

Chris B

Quote:
>-----Original Message-----
>The _WA_* "indexes" are not really indexes at all. They
are statistics that
>SQL Server has created because you have the "Auto Create
Statistics" option
>set ON for the database.

>I would recommend that users not have the rights to

create indexes if you
Quote:
>are worried about extraneous indexes being created by

users who should not
Quote:
>be doing so. Indexes are always owned by the owner of the
table. As far as I
>know, there is no way to query after the fact who created
the index. A user
>would have to be the owner of the database or the owner
of the table to
>create an index (or they might all be running with "sa"
rights). You could
>use Profiler to audit certain events, but I think that
might be overkill.
>Consider removing rights from those users who shouldn't
have them.

>--
>David Gugick
>Intrinsic Design, Inc.
>Coefficient - Database Analysis for Microsoft SQL Server
>http://www.idisoft.com

>.



Tue, 09 Dec 2003 06:31:40 GMT
 how to select all user indexes?
Well, you could create a stored procedure that uses a cursor to loop through
all user tables in the database (sysobjects where type = "U") and then run
sp_helpindex for each table in the cursor.

or

You can run straight SQL against the sysindexes table:

Select object_name(id) "Table Name", name "Index Name" from sysindexes where
status != 2 order by 1,2

Remember though that you will get back the _WA_SYS statistics in the second
method which are not indexes.

--
David Gugick
Intrinsic Design, Inc.
Coefficient - Database Analysis for Microsoft SQL Server
http://www.idisoft.com



Tue, 09 Dec 2003 07:04:21 GMT
 how to select all user indexes?
You can rename an index with sp_rename - there is no need to drop and
recreate.  The script below will generate an sp_rename script for all
indexes on user tables in the current database.

Note that this script includes indexes supporting primary key and unique
constraints.  You may want to use a different naming convention for those.
Also, accessing system tables directly is 'at your own risk' since this may
change in subsequent versions of SQL Server.

SELECT 'EXEC sp_rename ''' +
 QUOTENAME(USER_NAME(o.uid)) + '.' +
 QUOTENAME(o.name) + '.' +
 QUOTENAME(i.name) + ''', ''NewIndexName'',''INDEX'''
FROM sysobjects o
JOIN sysindexes i ON
 i.id = o.id
WHERE
 i.indid BETWEEN 1 AND 254 AND
 OBJECTPROPERTY(o.id, 'IsUserTable') = 1 AND
 INDEXPROPERTY(o.id, i.name, 'IsStatistics') = 0 AND
 INDEXPROPERTY(o.id, i.name, 'IsHypothetical') = 0
ORDER BY o.name, i.name

Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------


Quote:

> Thanks for your assistance David.

> I am not looking for indexes created by a particular
> user.  The question I have is that I want to query a
> database to get all indexes, I will delete all those, and
> then create all new and improved indexes with a proper
> naming convention included.

> The problem I have is getting the index name from each
> table.

> Thanks again,

> Chris B
> >-----Original Message-----
> >The _WA_* "indexes" are not really indexes at all. They
> are statistics that
> >SQL Server has created because you have the "Auto Create
> Statistics" option
> >set ON for the database.

> >I would recommend that users not have the rights to
> create indexes if you
> >are worried about extraneous indexes being created by
> users who should not
> >be doing so. Indexes are always owned by the owner of the
> table. As far as I
> >know, there is no way to query after the fact who created
> the index. A user
> >would have to be the owner of the database or the owner
> of the table to
> >create an index (or they might all be running with "sa"
> rights). You could
> >use Profiler to audit certain events, but I think that
> might be overkill.
> >Consider removing rights from those users who shouldn't
> have them.

> >--
> >David Gugick
> >Intrinsic Design, Inc.
> >Coefficient - Database Analysis for Microsoft SQL Server
> >http://www.idisoft.com

> >.



Tue, 09 Dec 2003 20:59:31 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. System Index vs. User Created Index

2. HELP: users granted SELECT ANY TABLE can't select any table

3. letting user see another user's index in user_indexes

4. Using index in SELECT

5. Problems on Select Statement not using Specific Index

6. Optimizer not selecting Index after table modification.

7. Selecting a column by it's index

8. does a select * require any indexes ?

9. Selecting fields that are indexed

10. Using a second index in a select

11. select index?

12. SQL optimize is selecting index base on actual value in the where clause


 
Powered by phpBB® Forum Software