Solved

index building

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

752 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