Identify tables with identity columns but clustered index on other columns
I sometimes need to do this to work on re-indexing databases. WHY identity columns are perfect for clustered indexes is a big topic, but here's a query to help identify columns where clustered indexes are placed on OTHER columns...
/********************
* Identify where clustered indexes are create
* on columns other than identity columns.
*
* Greg Linwood
* September 2005
*
* Designed for SQL 2000 only
*
********************/
set nocount on
/* holds a list of the tables & indexes in the curreent database */
declare @ssvnts_table_indexes table (
tableid int not null
, indid smallint not null
, indname sysname not null
, colname sysname not null
, isprimarykey varchar (1) not null
, isunique varchar (1) not null)
/* populates with a list of the tables & indexes in the current database */
insert into @ssvnts_table_indexes
select so.id as tableid, si.indid, si.name as indname, sc.name as colname
, case when si.status & 2048 <> 0 then 'y' else 'n' end as 'IsPrimaryKey'
, case when (si.status & 2 <> 0) or (si.status & 4096 <> 0) then 'y' else 'n' end as 'IsUnique'
from sysindexes si
join sysobjects so on si.id = so.id
join sysindexkeys sik on si.id = sik.id and si.indid = sik.indid
join syscolumns sc on sik.id = sc.id and sik.colid = sc.colid
where si.status & 64 = 0
order by so.name, si.indid
/* holds a list of tables in the current database - used as a temp workspace */
declare @ssvnts_table_info table (
tableid int not null
, tablename varchar (255) null
, identity_column varchar (255) null
, Has_A_Clustered_Index int null
, Identity_Column_Is_In_CIX char (1) null
, No_Of_Columns_In_CIX int null)
/* populates a list of tables in the current database */
insert into @ssvnts_table_info
select tableid
, convert(varchar(255), name) as tablename
, convert(varchar(255), '') as 'Identity_Column'
, convert(integer, min(indid)) as 'HasClusteredIndex'
, convert(char(1), 'u') as 'Identity_Column_Is_In_CIX'
, convert(integer, -1) as 'No_Of_Columns_In_CIX'
from @ssvnts_table_indexes ti
join sysobjects so on ti.tableid = so.id
where so.xtype = 'U'
group by tableid, name
/* if min(indid) is 0, table is a heap */
update @ssvnts_table_info
set Has_A_Clustered_Index = 0
where Has_A_Clustered_Index > 1
/* syscolumns.colstat & 1 signifies column is an indentity */
update @ssvnts_table_info
set Identity_Column = tic.Identity_Column
from @ssvnts_table_info ti
join (select so.id as tableid
, sc.name as 'Identity_Column'
from sysobjects so
join syscolumns sc on so.id = sc.id
where (sc.colstat & 1 = 1)) tic on ti.tableid = tic.tableid
/* count the columns in the clustered index (indid = 1) */
update @ssvnts_table_info
set No_Of_Columns_In_CIX = colsincix
from @ssvnts_table_info ti
join (select tableid, count(indid) as colsincix from @ssvnts_table_indexes where indid = 1 group by tableid) dv on ti.tableid = dv.tableid
/* idntify where the identity column is in the clustered index */
update @ssvnts_table_info
set Identity_Column_Is_In_CIX = 'y'
from @ssvnts_table_info ti
join (select tableid, colname, count(indid) as colsincix from @ssvnts_table_indexes where indid = 1 group by tableid, colname having count(indid) = 1) dv on ti.tableid = dv.tableid and ti.Identity_Column = colname
update @ssvnts_table_info
set Identity_Column_Is_In_CIX = ''
where Identity_Column_Is_In_CIX != 'y'
/* return output */
select *
from (select case when Has_A_Clustered_Index != 1
or Identity_Column_Is_In_CIX != 'y'
or No_Of_Columns_In_CIX != 1 then 'no' else 'yes' end as Clustered_Index_Choice
, ti.*
, ts.sizedec
from @ssvnts_table_info ti
join (select dv.id, upper(name) as [table_name]
, convert(decimal(15, 1), left(sizemb, len(sizemb)-2)) as sizedec
from (select so.id, convert(varchar(255), so.name) as name
, convert(decimal(15, 1), round((sum(convert(decimal(15, 1), si.dpages) * convert(decimal(15, 1), 8192)) / 1048000), 1)) as sizemb
from sysindexes si join sysobjects so on si.id = so.id join sysfilegroups sf on si.groupid = sf.groupid
where objectproperty(so.id, 'IsUserTable') = 1
group by so.id, so.name) as dv ) ts on ti.tableid = ts.id) c
where Clustered_Index_Choice != 'good'
order by sizedec desc