Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

index building

Posted on 2008-10-06
5
Medium Priority
?
198 Views
Last Modified: 2012-05-05
Hi Experts i have to build index for that iam doing script manually script .
is there any way i can get the index script for all the Indexes in the database.

iam attaching the file how iam doing right now.
Please tell me is there a way out generating the script for indexes in database.
index.JPG
0
Comment
Question by:Sandeepiii
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 22648869
Hi,

In Management Studio, right click the database -> Tasks -> Generate Scripts...

In the wizard that pops up, select the database and on the next dialog ("Choose script options"), make sure you set "Script Indexes" to True. Continue on, choose to script Tables and select all tables.

You then just need to remove the stuff you don't want from the resulting script (i.e. the table definitions) - I don't think you can just script the indexes themselves this way, you have to script the tables too - but it's still quicker/easier than what you're doing now
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22649090
One second..I can probably write something to script these out...
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22649172
OK, so by eye-balling it, this should work for 2005 (although it does not take into account included columns).

select 'CREATE ' + type_desc + ' INDEX  ON ' + object_name(i.object_id) + '(' +
reverse(substring(reverse((
select name + case when is_descending_key = 1 then ' DESC' ELSE ' ASC' END + ',' from sys.columns cc
join sys.index_columns c on c.object_id = cc.object_id and c.column_id = cc.column_id
where c.index_id = i.index_id and c.object_id = i.object_id
order by key_ordinal
for xml path('')

)), 2, 8000)) + ')'
from
sys.indexes i  
where name is not null
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 1200 total points
ID: 22649207
takes care of me forgetting the index name the first time and includes "included columns"


select IndexDef + ISNULL(' INCLUDE (' + IncludedColumns + ')' , '') from
(
SELECT IndexDef = 'CREATE ' + type_desc + ' INDEX ' + i.name COLLATE SQL_Latin1_General_CP1_CI_AS + ' ON ' + OBJECT_NAME(i.object_id) + '(' +
REVERSE(SUBSTRING(REVERSE((
SELECT name + CASE WHEN is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ',' FROM sys.columns cc
JOIN sys.index_columns c ON c.object_id = cc.object_id AND c.column_id = cc.column_id
WHERE c.index_id = i.index_id AND c.object_id = i.object_id
ORDER BY key_ordinal
FOR XML PATH('')

)), 2, 8000)) + ')',

IncludedColumns = REVERSE(SUBSTRING(REVERSE((
SELECT name + ',' FROM sys.columns cc
JOIN sys.index_columns c ON c.object_id = cc.object_id AND c.column_id = cc.column_id
WHERE c.index_id = i.index_id AND c.object_id = i.object_id and is_included_column = 1
ORDER BY key_ordinal
FOR XML PATH('')

)), 2, 8000))
FROM
sys.indexes i  
WHERE name IS NOT NULL
) a

0
 

Author Closing Comment

by:Sandeepiii
ID: 31503351
the answer is accurate and very detailed and in depth topic was covered / answered.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question