Home Microsoft SQL Server DigiMailing Contact
    Keyword



Create all your missing indexes

 

SQL Server 2008 has a new system view named sys.dm_db_missing_index_details that enables you to create all your missing indexes. The script below helps you to assembly the create syntax of your missing indexes. Just try the code and use the output!
 

declare @index_handle int, 
@database_id smallint,
@object_id int,
@equality_columns nvarchar(4000),
@inequality_columns nvarchar(4000),
@included_columns nvarchar(4000)

 
declare cIdx cursor for
select index_handle, database_id, object_id, equality_columns, inequality_columns,
included_columns from sys.dm_db_missing_index_details where database_id=db_id()

open cIdx
 
fetch cIdx into @index_handle, @database_id, @object_id, @equality_columns,
@inequality_columns, @included_columns
while @@fetch_status=0
begin
print 'USE [' + db_name(@database_id)+ ']'
print 'go'
print 'CREATE NONCLUSTERED INDEX ' + OBJECT_NAME(@object_id) + '_IDX_' +
convert(nvarchar,@index_handle) + ' ON ' + OBJECT_NAME(@object_id)
print '(' + @equality_columns
IF @inequality_columns IS NOT NULL
BEGIN
PRINT ',' + @inequality_columns
END
PRINT ')'
IF @included_columns IS NOT NULL
BEGIN
PRINT 'INCLUDE (' + @included_columns + ')'
END
PRINT 'go'
PRINT ''
fetch cIdx into @index_handle, @database_id, @object_id, @equality_columns,
@inequality_columns, @included_columns
end
close cIdx
deallocate cIdx

 




Syntax
Last revision
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash