Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Scripting Indexes with FIlters and schemas

  • 9 January 2010
  • Author: MarkGStacey
  • Number of views: 2860
  • 0 Comments

Today I needed to script out portions of a database - to be more precise, only the objects in a single schema.

 

For tables and stored procedures, this is easy : simply press F7, Object Explorer Details, and select the objects you need.

 

For indexes, this is not quite as simple.

 

I started off by searching the net, and found a basic script to do this : http://www.sqlservercentral.com/scripts/Miscellaneous/31893/

This script does create all the indexes, but it does not filter by schema, and it also does not script out the filtered indexes that I'm using.

 

So a little bit of modification led me to the following: Adding a filter for the  schema, prefixing the table name, and the filtered index column :

 

CREATE

 

PROC [dbo].[GetScriptAllIndexes]

@Schema varchar(255)

as

-- Get all existing indexes, but NOT the primary keys

DECLARE

 

 

cIX CURSOR FOR

SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID,si.filter_definition

FROM Sys.Indexes SI

inner join sys.tables st

on si.object_id = st.object_id

inner join INFORMATION_SCHEMA.TABLES ist

on st.name = ist.table_name

LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME

WHERE TC.CONSTRAINT_NAME IS NULL

AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1

and ist.table_schema = @Schema

ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

 

 

 

 

 

 

 

 

DECLARE

 

 

@IxTable

SYSNAME

DECLARE

 

 

@IxTableID

INT

DECLARE

 

 

@IxName

SYSNAME

DECLARE

 

 

@IxID

INT

declare

 

 

@IXFilter varchar(255)

-- Loop through all indexes

OPEN

 

 

cIX

FETCH

 

 

NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID,@IXFilter

WHILE

 

 

(@@FETCH_STATUS = 0)

BEGIN

DECLARE @IXSQL NVARCHAR(4000) SET @IXSQL = ''

SET @IXSQL = '

go

CREATE '

-- Check if the index is unique

IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)

SET @IXSQL = @IXSQL + 'UNIQUE '

-- Check if the index is clustered

IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)

SET @IXSQL = @IXSQL + 'CLUSTERED '

SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @Schema +'.' + @IxTable + '('

-- Get all columns of the index

DECLARE cIxColumn CURSOR FOR

SELECT SC.Name

FROM Sys.Index_Columns IC

JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID

WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID

ORDER BY IC.Index_Column_ID

DECLARE @IxColumn SYSNAME

DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

-- Loop throug all columns of the index and append them to the CREATE statement

OPEN cIxColumn

FETCH NEXT FROM cIxColumn INTO @IxColumn

WHILE (@@FETCH_STATUS = 0)

BEGIN

IF (@IxFirstColumn = 1)

SET @IxFirstColumn = 0

ELSE

SET @IXSQL = @IXSQL + ', '

SET @IXSQL = @IXSQL + @IxColumn

FETCH NEXT FROM cIxColumn INTO @IxColumn

END

CLOSE cIxColumn

DEALLOCATE cIxColumn

SET @IXSQL = @IXSQL + ')' + ISNULL('

WHERE '

 

+ @IXfilter,'')

-- Print out the CREATE statement for the index

PRINT @IXSQL

FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @IXFilter

 

 

END

CLOSE

 

 

cIX

DEALLOCATE

 

 

cIX

GO

 

Print
Categories: Blogs
Tags:
Rate this article:
No rating

MarkGStaceyMarkGStacey

Other posts by MarkGStacey

Please login or register to post comments.