Technical Article

Script to show user indexes tables(Sql 2000)

,

This script is supported only in SQL SERVER 2000, This shows only indexes created by users in tables.
Parameter @tabela--> You can put the table name and get the information about users indexes in this table.
Or
You can put nothing (NULL) and see all tables on the schema and how many indexes each table have.

CREATE procedure sp_dba_conta_indices @tabela varchar(30) = NULL
as
--create by Felipe Venancio Pedro
--showing users indexes
--@tabela is the name of any table
if( @tabela is null )
begin
select object_name(id) as tabela, count(*) qtd 
from sysindexes
where object_name(id) not like 'sys%'
AND indid > 0 and indid < 255
and name not like '_WA%'
and name not like 'hind%'
group by object_name(id)
order by count(*)

return(0)
end

if( @tabela is not null)
begin
select convert(char(30),name) as nome_dos_indices, 
Tipo_Indice = Case indid when 1 then 'CLUSTERED'
                  else 'NONCLUSTERED'
end
from sysindexes
where object_name(id) = @tabela
and object_name(id) not like 'sys%'
AND indid > 0 and indid < 255
and name not like '_WA%'
and name not like 'hind%'
order by Tipo_Indice
return(0)
end
go

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating