March 25, 2011 at 1:25 pm
Good afetrnoon
i know that sp sp_help table shows all table attributes, but i just wanna know if there is a sentence or sp that shows up just the index of the table?
Id appreciate your help
March 25, 2011 at 2:53 pm
While there are much easier ways of doing this through enterprise manager in 2000 you can do this in TSQL using a combination of sysobjects and sysindexes. you would need to get the table id from sysobjects and then you could look up any idexes for that id in sysindexes.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 25, 2011 at 5:23 pm
Try this for SQL 2000
SELECT 'Index Name' = i.name, OBJECT_NAME(i.id) AS 'Table Name',
Case Indid
WHEN 1 THEN 'Clustered'
ELSE 'NonClustered'
End 'Type',
'Last Updated' = STATS_DATE(i.id, i.indid),rowmodctr AS
'# Rows inserted deleted or updated', --, o.type
i.keys
FROM sysobjects o, sysindexes i
WHERE o.id = i.id AND (o.type <> 'S' AND indid <> 0 AND indid <> 255)
Sample results:
Index Name Table Name Type Last updatted Rows Inserted Deleted ...
PK_Orders Orders Clustered 2006-03-07 10:38:47.687 0
CustomerID Orders NonClustered 2006-03-18 12:36:51.950 0
CustomersOrdersOrders NonClustered 2006-03-18 12:36:51.967 0
EmployeesOrdersOrders NonClustered 2006-03-07 10:54:09.840 0
OrderDate Orders NonClustered 2008-07-05 11:17:37.090 0
March 25, 2011 at 7:53 pm
Would this suffice?
execute sp_msforeachtable 'sp_helpindex ''?''';
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply