SQL Utility Scripts

SQL Server utility scripts to manage administrative, performance and troubleshooting operations on your SQL Server.

Getting Started

The scripts were tested on SQL Server 2012 and above.

Prerequisites

• Install SSMS - SQL Server Management Studio from PowerShell choco install sql-server-management-studio or download from Microsoft
• Create schema utility CREATE SCHEMA Utility;

---

Cleanup Generator

An SQL view that helps you generate drop queries for the existing objects.

Get cleanup queries for specific table

Select * from [Utility].[GenerateDrop] where [Table] = 'Blogs'

Get cleanup queries for specific type

Select * from [Utility].[GenerateDrop] where [Type] = 'F' or [TypeDescription] = 'FOREIGN_KEY_CONSTRAINT'

Get cleanup queries for specific schema

Select * from [Utility].[GenerateDrop] where [Schema] <> 'dbo'

Select/Delete Dependent Data

When cascade option is restrict, delete operations of used entities fail. This inline table valued function helps you generate manual scripts to select/delete scripts for the related rows.

Select/Delete dependent data for single id

`SELECT * FROM [Utility].[GenerateDeleteDependent]('Users', '= 1')` `SELECT * FROM [Utility].[GenerateSelectDependent]('Users', '= 1')`

Select/Delete dependent data for multiple conditions

`SELECT * FROM [Utility].[GenerateDeleteDependent]('dbo.Users', 'IN (1, 2)')` `SELECT * FROM [Utility].[GenerateSelectDependent]('dbo.Users', 'IN (1, 2)')`

Rename Linked Server

Inline table valued function that generates alter queries to rename linked servers for views and stored procedures

Rename Linked Server

`SELECT * FROM [Utility].[GenerateRenameLinkedServer]('oldserver', 'newserver')`

Update Keyword

Stored procedure that generates update queries to replace keyword(s). Null is not considered a keyword.

Replace keyword in all tables

`EXEC [Utility].[sp_updatekeyword]('Value1', ',', 'NewValue')`

Replace multiple keywords in all tables

`EXEC [Utility].[sp_updatekeyword]('Value1,Value2', ',', 'NewValue')`

View Table Size

Stored procedure that generates information about tables, rows and reserved sizes.

View Table Size

`EXEC [Utility].[sp_tablesize]`