Beta Release - Please Report Issues
Who is online? 48 guests and 0 members
Member login | Become a member
home » blogs » MarkGStacey » Scripting Indexes with FIlters and schemas
posted 1/9/2010 5:49:01 AM by MarkGStacey
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 keysDECLARE
-- 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
SYSNAMEDECLARE
INTDECLARE
INTdeclare
declare
@IXFilter varchar(255)
-- Loop through all indexes
OPEN
FETCH
WHILE
(@@FETCH_STATUS = 0)
BEGIN
DECLARE @IXSQL NVARCHAR(4000) SET @IXSQL = ''
SET @IXSQL = 'goCREATE '
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)
IF (@IxFirstColumn = 1)
SET @IxFirstColumn = 0
ELSE
SET @IXSQL = @IXSQL + ', '
SET @IXSQL = @IXSQL + @IxColumn
END
CLOSE cIxColumn
DEALLOCATE cIxColumn
SET @IXSQL = @IXSQL + ')' + ISNULL('WHERE '
WHERE '
+ @IXfilter,'')
-- Print out the CREATE statement for the index
PRINT @IXSQL
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @IXFilter
ENDCLOSE
CLOSE
DEALLOCATE
GO
MarkGStacey (Member since: 12/10/2009 2:07:00 PM)
View MarkGStacey 's profile
Comment (No HTML)
It's fast, easy and free! Submit articles, get your own blog, ask questions & give answers in the forums, and become a better developer, faster.
enter your email address: