Solved

index building

Posted on 2008-10-06
5
196 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 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

623 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