Solved

index building

Posted on 2008-10-06
5
194 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
  • 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 300 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

821 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