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.

Direct download can be done using the "Source Code" page:

http://sys2dmvs.codeplex.com/SourceControl/list/changesets

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:


VIEWS/FUNCTIONS

sys2.database_backup_info
Returns information of last backup of each database

sys2.database_files
Shows the space available and the spaces used for eah file in a database.

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_per_table
Display a list of all table along with information regarding presence of clustered and nonclustered indexes.

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.logs_usage
Display Transaction Log usage data for all databases.

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. Also shows in which filegroup LOB data are stored and the space used in any Filegroup.

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.plan_cache_size
Shows how much memory is being used by different object types in plan cache, reporting also how much memory assigned to reused plan and to plans used only one time

sys2.query_memory_grants
Wrapper around sys.dm_exec_query_memory_grants.

sys2.query_stats
Wrapper around sys.query_stats.

sys2.stats
Wrapper around sys.stats.

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.

STORED PROCEDURES

stp_get_databases_space_used_info
Returns a list of all databases along with the relativa space used, space available, max space and growth.