Davide Mauri - SQL Server & .NET Specialist

 

SQL Scripts

Here you can find some SQL Scripts that I find useful in my everyday work and that may also be helpful to you. Enjoy!
Please note that for SQL Server 2005 scripts, all my objects are created in the sys2 schema. I use this schema to reference all my "system" objects.
If you want to do the same, you have to create the sys2 schema before. You can do it simply using the CREATE SCHEMA sys2 statements.

sys2.indexes
A UDF that shows all indexes present on a table. For any index shows of which column it's made.
Usage: SELECT * FROM sys2.indexes('<schema>.<table>')
Note: If you pass a NULL value as parameter, you'll get all the indexes in ALL tables.

sys2.indexes_size
A UDF that shows how much big your indexes are. For any index shows the size in kb and mb.
Usage: SELECT * FROM sys2.indexes_size('<schema>.<table>')
Note: If you pass a NULL value as parameter, you'll get all the indexes in ALL tables.

sys2.indexes_stats
A simple wrapper around dm_db_index_physical_stats that beside index fragmentation statistics also shows index names. Usage: SELECT * FROM sys2.indexes_stats('<schema>.<table>')
Note: If you pass a NULL value as parameter, you'll get all the indexes in ALL tables.

sys2.indexes_usage
This UDF shows how (and if) indexes are used by SQL Server.
Usage: SELECT * FROM sys2.indexes_usage('<schema>.<table>')
Note: If you pass a NULL value as parameter, you'll get all the indexes in ALL tables.

Source: Davide Mauri - SQL Server & .NET Specialist