Help with query to find all BLOB columns that have an index.

• SQLRNNR

  SSC Guru

  Points: 281344

  More actions

  April 28, 2010 at 9:57 am

  #1159090

  toddrharrison (4/28/2010)

  ---

  Yes, that does work now. Thanks.

  I get duplicates now. See attached.

  Duplicates will be expected. Due to the different types of LOB data, one must query from both pools. The dups you are seeing would be due to an allocationUnit of LOB_Data that also has an allocation unit of ROW_OverFlow_Data. They are different but are both BLOBs.

  The results from your SQL have a lot of great details but not exactly what I'm looking for.

  All the indexes in this report are for the PK column of the tables that have a blob column somewhere else in the table. The blob column is of course not the PK column. I can rebuild these listed PK indexes online just fine.

  When I run this against any of my systems that is not the case. I don't get a list of just PKs. I do see some PKs but they are not exclusive.

  With SQL 2005 Enterprise Edition all indexes on blob columns must be rebuild offline.

  What I was looking for was a simple list of blob columns that are directly indexed. I need this list so I can schedule such offline index rebuilds over downtime.

  Simple enough. Add this to each side of the union And i.index_id <> 1, as a part of the where clause.

  I should note: I'm not familiar enough with this offline blob index rebuild requirement to know if the table's clustered index would also have to be included with the offline blob index rebuild.

  Clustered Indexes may need to be rebuilt offline from time to time. You will most likely be able to do that online most of the time though.

  wrote:

  Not a problem.

  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