Tuesday, February 2, 2010

List-all-indexes-statistics-sql-server

select
'Table name'
=
object_name(id),


'column_name'
=
index_col(object_name(id), indid, 1),


'index_description'
=
convert(varchar(210),
case
when (
status
& 16 )
<> 0


then
'clustered'


else
'nonclustered'


end


+
case
when (
status
& 1 )
<> 0 then
', '
+
'ignore duplicate keys'


else
''


end
+
case
when (
status
& 2 )
<> 0 then
', '
+
'unique'


else
''


end
+
case
when (
status
& 4 )
<> 0


then
', '
+
'ignore duplicate rows'


else
''


end
+
case
when (
status
& 64 )
<> 0


then
', '
+
'statistics'


else
case
when (
status
& 32 )
<> 0


then
', '
+
'hypothetical'


else
''


end


end
+
case
when (
status
& 2048 )
<> 0


then
', '
+
'primary key'


else
''


end


+
case
when (
status
& 4096 )
<> 0 then
', '
+
'unique key'


else
''


end
+
case
when (
status
& 8388608 )
<> 0 then
', '
+
'auto create'


else
''


end
+
case
when (
status
& 16777216 )
<> 0


then
', '
+
'stats no recompute'


else
''


end),
'index_name'
= name

from
sysindexes

where (
status
& 64 )
= 0

order
by id

List all indexes /statistics sql server 2000