List all index held on a SQL 2005 server

Question

Post reply

List all index held on a SQL 2005 server

RTendo

Grasshopper

Points: 17
More actions
September 29, 2010 at 2:03 am

#145411

Can anyone provide me with a script which will list all of the indexes held on a SERVER (and not just at the database level?)
One of my maint plans is failing due to permissions on an index, but it doesn't tell me which db it stems from.......and we have nearly 100 databases on the server!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

NickDBA Hall of Fame Points: 3792 More actions · Answer 1

You can get a lot out of the index DMV's

sys.dm_db_index_operational_stats

sys.dm_db_index_physical_stats

Although these are for showing locking information / fragmentation level etc, if you don't supply a databaseid (param 1) you'll get listings for all databases.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

RTendo (9/29/2010)
One of my maint plans is failing due to permissions on an index!

Permission issue with index ???:unsure:

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Can you provide the actual error message?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

RTendo Grasshopper Points: 17 More actions · Answer 4

Source: Reorganize Index 1 Execute SQL Task Description: Executing the query "ALTER INDEX [IX_NetFlowDetail_DestIPSort] ON [dbo].[NetFlowDetail_449_1428585] REORGANIZE WITH ( LOB_COMPACTION = ON ) " failed with the following error: "Cannot find the object "dbo.NetFlowDetail_449_1428585" because it does not exist or you do not have permissions.".

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

That 'no permission or not exists' refers to the table 'dbo.NetFlowDetail_449_1428585', not to an index. Hence you need to query sys.tables in each database (or write code that loops through all databases and queries it)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Feeg SSCrazy Points: 2766 More actions · Answer 6

this is in the solarwinds DB, NetPerfMon and I had a similar issue with a reorganize query i got ==> http://www.sqlservercentral.com/scripts/index/64469/ and I modified it to apply those ALTER INDEX commands it picked up ....

I then tossed it and tested and later implemented http://sqlfool.com/2010/04/index-defrag-script-v4-0/ instead and it is working fine. I'm not sure but I think Solarwinds has it's own DB maintenance built into the app and just requires setup ...

MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics

RTendo Grasshopper Points: 17 More actions · Answer 7

RTendo

Grasshopper

Points: 17

September 29, 2010 at 8:29 am

#1229079

Thanks Feeg, that was very helpful

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

I find that very interesting. Thanks for the explanation Feeg

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events