T-SQL Tuesday #112 – Dipping into your Cookie Jar


 This post is part of the T-SQL Tuesday, which is a monthly blog party on the second Tuesday of each month. Everyone is welcome and have the chance to write about SQL Server.
This month’s host is my good friend Shane O’Neill (B|T) and he wants us to share some cookies with all you guys.


Some time ago, when I started this blog, my idea was to learn more about SQL Server by experimenting and writing about it.
But obviously I don’t pay my bacon just playing around with SQL, I have to do actual work to do get money at the end of each month 🙂

To save me some time on my day-to-day tasks, I filled little by little a good jar of cookies, which was good fun and tought me plenty of things that now I know.

And because the SQL Server community is like that, I’m not going to give you one, but the whole jar.

Here is my jar

Last summer I decided to start with my own repository on Github

It’s not very well documented as a whole, but each script is, and the names are very descriptive.

If you are afraid or just cannot create a whole database, you still can benefit from the it, because in the Scripts folder, there is a variety of them which are stand-alone and can run independently on SQL Servers from 2008 onwards.

The list is as follows

  • BackupSizeInfo.sql, to display backup infomation, can be filtered by database, backup type and provides a basic RESTORE DATABASE command which can be really useful for this long restore sequences as it can return the N last backups in ascending or descending order
  • DatabaseFilesInfo.sql, returns information about database files, including size, used space, disk space and more. Can be filtered by database or file type, for example LOG files.
  • DatabaseSizeInformation.sql, database size split in DATA, LOG, FILESTREAM, USED and TOTAL size
  • DatabaseSnapshotCreate.sql, will script out or create a database snapshot, specially useful when there are multiple database files.
  • IndexDescription.sql, comprehensive list of indexes with sizes and usage stats. Also will return DROP and CREATE statements.
  • IndexMissing.sql, based on missing index recommendations would return stats and CREATE index statement
  • JobsHistory.sqlto list jobs along requested historical executions, can be filtered by the name and steps command
  • ListAllProgrammability.sql, to return stored procedures, functions, views and trigges, along create statements
  • RunningQueries.sql, lightwight tool to see queries currently running on the server, can be filtered by SPID tweaked to show only blocked/blocking sessions
  • SecurityAuditDatabaseUsers.sql, database users and permissions
  • SecurityAuditServerLogins.sql, servers principals and permissions, see this post
  • StrSQLSearch.sql, to find any pattern anywhere, tables, columns, sql modules, jobs, really love this one
  • TableDescription.sql, great to identify HEAPs, big tables, and more. See previous post


Hopefully you found these scripts useful and just to say thanks again to Shane for hosting this month’s party.

Thanks for reading!


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.