indexes

[EscapeUK]

How can i view a nice neat list of all the indexes used in my DB

 

[Bernadette]

Hi There

If you go to

http://www.swynk.com/sqlhome/index7.asp

. There are SQL Scripts there to list the indexes and the tables they refer to in your database.

Good Luck
Bernadette B-)

 

[Guest_imported]

Here is a script to list all indexes,sp's,triggers...etc.
for a given database. You should edit it for your own needs.
Have fun.
hth


-- SQL SERVER object counter
-- This procedure will list: Indexes,Stored Procedures,Triggers,Tables,Views
-- for a given database.
-- Version 1.00
-- Date: 2000/5/9


-- INDEXES
Print ''
Print '---List Indexes---'
Select right(name,3) as 'Type', name from sysindexes
where name like '%$pk'
or name like '%$ndx'
group by right(name,3),name
order by right(name,3),name

select count(*)'Count' ,right(name,4) as 'Type' from sysindexes
where name like '%$pk'
or name like '%$ndx%'
or name like '%$uk%'
group by right(name,4)


-- STORED PROCEDURES
Print ''
Print '---List SPs---'
Select 'SP' as 'Type', name from sysobjects
where name like '%$sp'
group by right(name,3),name
order by right(name,3),name

select count(*)'Count','SP' as 'Type' from sysobjects
where OBJECTPROPERTY(id, N'IsProcedure') = 1
group by right(name,4)

-- TRIGGERS
Print ''
Print '---List Triggers---'
Select 'TR' as 'Type', name from sysobjects
where name like '%$t%'
group by right(name,4),name
order by right(name,4),name

select count(*)'Count','TR' as 'Type' from sysobjects
where OBJECTPROPERTY(id, N'IsTrigger') = 1
group by right(name,4)


--TABLES
Print ''
Print '---List Tables---'
Select 'TB' as 'Type', name from sysobjects
Where OBJECTPROPERTY(id, N'IsTable') = 1
and type = 'U'
and name <> 'dtproperties'
order by name

Select Count(*) 'Count','TB' as 'Type' from sysobjects
Where OBJECTPROPERTY(id, N'IsTable') = 1
and type = 'U'
and name <> 'dtproperties'

--VIEWS
Print ''
Print '---List Views---'
Select 'VW' as 'Type', name from sysobjects
Where OBJECTPROPERTY(id, N'IsView') = 1
and type = 'V'
and name like '%_$vw%'
order by name

Select Count(*) 'Count','VW' as 'Type' from sysobjects
Where OBJECTPROPERTY(id, N'IsView') = 1
and type = 'V'
and name like '%_$vw%'