28 November 2023 by mwd
SQL Server Stored Procedures
Stored procedures in my GitHub repository
The Stored Procs github repository currently contains the following scripts.
Each script, by default, creates a Stored Procedure in the Master database from where it can be run, each script will replace a like named Stored proc if one has already been created.
The RemoveStoredProcs.sql script will remove from the Master database any stored procs created with the same names as those in this repository. This is only needed if tidying up the master DB.
- WhatIsRunning.sql
- Display details of running sessions.
- Use optional
@ActiveOnly
parameter set to 1 to limit output to currently running sessions. - Use optional
@BackupsOnly
parameter with a value of 1 to only show backups and restores. - Usage examples :
EXEC sp_WhatIsRunning
orEXEC sp_WhatIsRunning @ActiveOnly = 0 , @BackupsOnly = 1
- WhatWasBackedUp.sql
- Display details of latest Database Backups for each DB on current instance.
- Use optional
@DBName
parameter to narrow down results to a specific DB or a subset using wildcards. - Use optional
@FullBackupsOnly
parameter with a value of 1 to restrict results to Full Backups. - Usage example :
EXEC sp_WhatWasBackedUp @DBName = 'MyDB' , @FullBackupsOnly = 1
- WhatWasRestored.sql
- Display the latest DB restores including details of the source backup file.
- Use optional
@DBName
parameter to narrow down results to specific DB or a subset using wildcards. - Use optional
@FullBackupsOnly
parameter with a value of 1 to restrict results to Full Backups. - Usage example :
EXEC sp_WhatWasRestored @DBName = 'MyDB' , @FullBackupsOnly = 1
- WhereAreTheBackups.sql
- Display the location and size of Database backup files for backups taken on the current instance.
- Use optional
@DBName
parameter to narrow down results to specific DB or a subset using wildcards. - Use optional
@FullBackupsOnly
parameter with a value of 1 to restrict results to Full Backups. - Usage example :
EXEC sp_WhereAreTheBackups @DBName = 'MyDB' , @FullBackupsOnly = 1
- WhereAreTheFiles.sql
- Display the location and size of Database Data and Log Files.
- Use optional
@DBName
parameter to narrow down results to specific DB or a subset using wildcards. - Usage example :
EXEC sp_WhereAreTheFiles @DBName = 'MyDB'
tags: sql