Metadata Collection - SQL Server Integration Services Design Patterns - page 25

Figure 1-18 . The completed Data Flow task

You are now ready to create your second data flow. From the Control Flow tab,
copy and paste the existing data flow into the Foreach Loop container. Drag the green
arrow—the Success Precedence constraint—from the Database Size data flow to your
new data flow. Rename the new data flow Unused Indexes , and then double-click it to
return to the Data Flow designer.
Double-click the Dynamic SQL Source icon to edit its properties. You need to
change the SQL command to use the code in Listing 1-7 .

Listing 1-7 . Example of T-SQL Query to Retrieve Unused Indexes

/* Create a variable to hold a list of indexes */
DECLARE @Indexes TABLE
(

serverName NVARCHAR(128)

,schemaName SYSNAME

,schemaID INT

,databaseName SYSNAME

,databaseID INT

,tableName SYSNAME

,objectID INT

,indexName SYSNAME

,indexID INT

,indexType NVARCHAR(60)

,isPrimaryKey BIT

,isUnique BIT

,isFiltered BIT

,isPartitioned BIT

,numberOfRows BIGINT

,totalPages BIGINT

);

/* Iterate through all databases */
INSERT INTO @Indexes
(serverName,schemaName,schemaID,databaseName,databaseID,tableName,objectID,indexName,indexID,indexType,isUnique,isPrimaryKey,isFiltered,isPartitioned,numberOfRows,totalPages)
EXECUTE sys.sp_MSforeachdb
' USE ?;