markwdavies

companion site to my github repo

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 or EXEC 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

Back to all posts