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

Tip The T-SQL in Listing 1-7 is just a starting point. You can easily modify this
query to return information such as which clustered indexes may warrant redesign,
which tables have the most updates, and which tables are the most frequently queried.

An example of the output follows.

captureDate serverName schemaName
databaseName tableName
----------------------- ---------- ----------
------------------ ------------------
2014-06-29 19:37:36.927 LOCALHOST Production
AdventureWorks2012 TransactionHistory
2014-06-29 19:37:36.927 LOCALHOST Production
AdventureWorks2012 TransactionHistory
2014-06-29 19:37:36.927
LOCALHOST Sales AdventureWorks2012 SalesOrderDetail

indexName indexType isFiltered

isPartitioned numberOfRows
-------------------------------------- ------------
---------- ------------- ------------
IX_TransactionHistory_ProductID NONCLUSTERED

0 0 1134431
IX_TransactionHistory_ReferenceOrderID NONCLUSTERED
0 0 1134431
IX_SalesOrderDetail_ProductID NONCLUSTERED

0 1 1213178

userSeeksSinceReboot userScansSinceReboot
userLookupsSinceReboot userUpdatesSinceReboot
-------------------- --------------------
---------------------- ----------------------
0 0 0

0 8 0

0 2 0

indexSizeInMB lastReboot