Directory Listing in SSMS
Get a complete directory listing of the contents of folders and subfolders with the below script. This is useful if you don’t have direct access to the server operating system where SQL Server is installed,…
Search SSRS Reports for Keywords
Microsoft’s SQL Server Reporting Services, colloquially known as SSRS, provides a great way to expose data from SQL Server in human-consumable form, typically via a web site. Generally, it’s a good idea to use a…
Parse DTSX Package Connection Manager Properties
The following query returns connection manager properties from the SSIS .dtsx packages stored in a particular file system folder. The code relies upon the undocumented and unsupported sys.xp_dirtree extended stored procedure that ships with all…
Service Broker Queue Monitor
My previous post shows how to configure an Event Notification to fire whenever a login event occurs. The post uses Service Broker to receive those Event Notifications into a queue, which is then processed by…
Recompress Heaps
Heaps, or tables without a clustered index, suffer from a little-known problem when configured with page compression. Page compression only takes place once a page is 100% full, i.e. when no more rows can be…
Compress Big Tables
Storing data in SQL Server can be expensive. Not necessarily because disk is expensive; it isn’t and is generally getting cheaper all the time. Data costs money because it consumes RAM, requires CPU resources to…