Solved

index building

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
format nvarchar field as mm/dd/yyyy 4 62
MS SQL order by with "over" statement and row_number() 11 40
Move SQL 2005 Express to Server 2012R2 19 105
Grid querry results 41 73
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now