June 1, 2010 at 7:45 pm
Hi,
In SQL 2000, we could have a list of all indexes on a table at a glance.
How can I provide this in Management Studio ? I don't like to have one by one.
Is there any query to list all indexes on a table ?
I expect a list like this :
<index-name> < columns>
e.g: <booking> <booking_id,booking_name,...>
Not:<booking><booking_id>
<booking><booking_name>
...
June 1, 2010 at 11:58 pm
Open object explorer, connect to the server
Expand out database->tables->the table you're interested in-> indexes
You can write a query, the views that you need are sys.indexes and sys.index_columns. It'll take some fun manipulation to get it into the format that you want, but it is possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2010 at 12:29 am
Thanks Gail,
As I said before, in database->tables-> indexes, I should check every index one by one
What I want, having all indexes and columns in a window
June 2, 2010 at 12:53 am
check if below code works..it definitely NOT faster as it uses cursor approach, but it should give the result you expected..
Declare @vchrFileName varchar(255)
Declare @vchrSQLStr varchar(2000)
set ansi_warnings off
create table #IndexScan1 (ObjName varchar(100) DEFAULT ('@update@'),Index_Name varchar(255),Index_Desc varchar(1000),Index_keys varchar(100))
DECLARE csrFileCursor CURSOR FOR
SELECT SCHEMA_NAME(schema_id)+'.'+name FROM sys.objects where type='u'
OPEN csrFileCursor
FETCH NEXT FROM csrFileCursor Into @vchrFileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @vchrSQLStr = 'insert into #IndexScan1 (index_name,index_desc,index_keys) EXEC sp_helpindex '''+@vchrFileName+''''
exec(@vchrSQLStr)
SET @vchrSQLStr ='update #IndexScan1 set ObjName ='+''''+@vchrFileName+''' where ObjName =''@update@'''
exec(@vchrSQLStr)
FETCH NEXT FROM csrFileCursor Into @vchrFileName
END
Close csrFileCursor
Deallocate csrFileCursor
SELECT
Objname,
Index_keys = REPLACE(
(
SELECT
Index_keys AS [data()]
FROM
#IndexScan1 ixs
WHERE
ixs.Objname = c.Objname
ORDER BY
Index_keys
FOR XML PATH ('')
), ' ', ' -- ')
FROM
#IndexScan1 c
group by Objname
ORDER BY Objname
Drop Table #IndexScan1
June 2, 2010 at 1:32 am
This query will give the result
select i.name as [Index],i.type_desc as [Index type],T.name as [Table name],C.name as [ColumnName]
from sys.tables T inner join sys.columns C on C.object_id=T.object_id
inner join sys.indexes i on i.object_id=T.object_id
Malleswarareddy
I.T.Analyst
MCITP(70-451)
June 2, 2010 at 1:43 am
malleswarareddy_m (6/2/2010)
This query will give the resultselect i.name as [Index],i.type_desc as [Index type],T.name as [Table name],C.name as [ColumnName]
from sys.tables T inner join sys.columns C on C.object_id=T.object_id
inner join sys.indexes i on i.object_id=T.object_id
No, that'll give you all the columns in the table, not all the columns in the index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2010 at 4:28 am
this may be worked.i checked for my database.Please suggest me if it is wrong.
select
case when index_id=1 then 'Clustered'
else 'Non-Clustered' end as [Index Column],
T.name as [Table name],C.name as [ColumnName]
from sys.tables T inner join sys.columns C on C.object_id=T.object_id
inner join sys.index_columns i on i.index_column_id=C.column_id
and i.object_id=C.object_id
Malleswarareddy
I.T.Analyst
MCITP(70-451)
June 2, 2010 at 12:22 pm
a.shahnazi (6/1/2010)
Hi,In SQL 2000, we could have a list of all indexes on a table at a glance.
How can I provide this in Management Studio ? I don't like to have one by one.
Is there any query to list all indexes on a table ?
I expect a list like this :
<index-name> < columns>
e.g: <booking> <booking_id,booking_name,...>
Not:<booking><booking_id>
<booking><booking_name>
...
To me it sounds like you just want to use
exec sp_helpindex 'tablename'
The downside is that included columns are not referenced in this list ... to get those as well you would need to do a more complex query with dmv's.
June 2, 2010 at 7:08 pm
Thanks All,
What I want is "sp_helpindex" idea.
NewBeeSQL's script is fantastic, just needs a small change in size of Index_keys column in temp table.
Regards
June 2, 2010 at 11:47 pm
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply