June 13, 2009 at 7:49 am
I need a script or select query which gives me
listing of
table name, column name, index name, index type
for a database
June 13, 2009 at 8:19 am
mjarsaniya (6/13/2009)
I need a script or select query which gives melisting of
table name, column name, index name, index type
for a database
I guess is for SQL2005, right? test this one
select 'table_name'=object_name(i.id) ,i.indid
,'index_name'=i.name ,i.groupid
,'filegroup'=f.name ,'file_name'=d.physical_name
,'dataspace'=s.name from sys.sysindexes i
,sys.filegroups f ,sys.database_files d
,sys.data_spaces s
where objectproperty(i.id,'IsUserTable') = 1
and f.data_space_id = i.groupid
and f.data_space_id = d.data_space_id
and f.data_space_id = s.data_space_id
order by f.name,object_name(i.id),groupid
go
also, check this out: http://www.sqlservercentral.com/scripts/61361/
June 13, 2009 at 8:25 am
Use sys.indexes instead of sysindexes and you can get the index type as well from the column type_desc. For the columns that comprise the index, join in sys.index_columns
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 13, 2009 at 10:07 am
Could you please provide me a script, which continuously checks the index fragmentation and if it finds the avg index fragmentation>30%, it should perform index rebuild and if it is<30%,it should perform index reorganize. I want create a job (which checks for index fragmentation for all databases and takes the required action Index rebuild or reorganize)using this script and automate the process.
I want to create a stored procedure in Master database using the script provided by you and use that stored procedure to index defragment for all databases.
please help me out..
June 13, 2009 at 10:17 am
Check the script library here, there are lots of index rebuild scripts there that satisfy your requirements.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply