SQL Scripts

To ease my work I have developed some SQL Scripts that may also be helpful to you. The scripts are hosted on CodePlex, here:

http://sys2dmvs.codeplex.com/

The scripts will work from SQL Server 2005 and after.

Please note that all my objects are created in the sys2 schema. I use this schema to reference all my "system" objects.

If you find bugs or have feeback and you want to share it with me so that I can improve the scripts, please use the Codeplex Issue Tracker feature to report them to me.

Here's the current available scripts:

sys2.indexes
Returns a row per index in table or view. It's a wrapper around sys.indexes.

sys2.indexes_operational_stats
Returns a row per index in table or view, displaying operational data. It's a wrapper around sys.dm_db_index_operational_stats().

sys2.indexes_physical_stats
Returns a row per index in table or view, displaying physical informations. It's a wrapper around sys.dm_db_index_physical_stats().

sys2.indexes_size
Returns a row per index in table or view, displaying its size in MB and KB.

sys2.indexes_usage_stats
Returns a row per index in table or view, displaying usage informations. It's a wrapper around sys.dm_db_index_usage_stats().

sys2.missing_indexes
Returns a row per detected missing index in the whole instance. It's a wrapper around sys.dm_db_missing_index_* DMVs.

sys2.objects_data_spaces
Returns a row per index/heap per table, showing in which Filegroup the object is located.

sys2.objects_dependencies
Returns a list of all the objects upon which a table or view is dependent.

sys2.objects_partition_ranges
Returns a row per partition per table, showing in which Filegroup the partition is located, how many rows are there, and the partition's range values.

sys2.tables_columns
Return tables and their columns and types. Also tell if a column is a LOB column or not and, if yes, in which filegroup is stored.