/***************************************************************************************************
# SYSTEM HELPERS:
# --------------------------------------------------------------------------------------------------
# SQLAUX scripting library | Author: Sergei Y.Kitáev
# -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
*/

/*==================================================================================================
# CHECKING, IF CONNECTED USER IS IN SERVER ADMINISTRATOR ROLE:
# === Usage: ===
# dbo.[AUX:IsInServerAdminRole]()
*/
function [AUX:IsInServerAdminRole]()
returns bit


/*==================================================================================================
# STORED PROCEDURE DELETION ROUTINE (IF PROCEDURE OBJECT EXISTS):
# === Usage: ===
# exec [AUX:DropProcedure] <Procedure name>
*/
procedure [AUX:DropProcedure] @ProcedureName $(dt_String_AUX)
/*==================================================================================================
# FUNCTION DELETION ROUTINE (IF FUNCTION OBJECT EXISTS):
# === Usage: ===
# exec [AUX:DropFunction] <Function name>
*/
procedure [AUX:DropFunction] @FunctionName $(dt_String_AUX)
/*==================================================================================================
# TABLE DELETION ROUTINE (IF TABLE OBJECT EXISTS):
# === Usage: ===
# exec [AUX:DropTable] <Table name>
*/
procedure [AUX:DropTable] @TableName $(dt_String_AUX)
/*==================================================================================================
# TABLE VIEW DELETION ROUTINE (IF VIEW OBJECT EXISTS):
# === Usage: ===
# exec [AUX:DropView] <View name>
*/
procedure [AUX:DropView] @ViewName $(dt_String_AUX)
/*==================================================================================================
# DATA TYPE DELETION ROUTINE (IF TYPE OBJECT EXISTS):
# === Usage: ===
# exec [AUX:DropType] <Type name>
*/
procedure [AUX:DropType] @TypeName $(dt_String_AUX)
/*==================================================================================================
# TRIGGER DELETION ROUTINE (IF TRIGGER OBJECT EXISTS):
# === Usage: ===
# exec [AUX:DropTrigger] <Trigger name>
*/
procedure [AUX:DropTrigger] @TriggerName $(dt_String_AUX)


/*==================================================================================================
# STORED PROCEDURE SET DELETION ROUTINE (BY NAME PATTERN, OF EXISTING PROCEDURE OBJECTS):
# === Usage: ===
# exec [AUX:DropProcedures] N'<Prefix token>:%'
# exec [AUX:DropProcedures] N'<Prefix token>::%'
# exec [AUX:DropProcedures] N'<Prefix token>:%', N'<Prefix token>::%'
# exec [AUX:DropProcedures] N'<Prefix token>\_%'
# ( Character “\” serves as “escape” for indication,
#   that “_” symbol is not used as placeholder. )
# *** CAUTION: ***
# USE THIS PROCEDURE WITH HIGH CAUTION
# TO AVOID UNINTENDED REMOVING OF PROCEDURE OBJECTS!
*/
procedure [AUX:DropProcedures] @NamePattern      $(dt_String_AUX),
                               @ExceptionPattern $(dt_String_AUX) = null
/*==================================================================================================
# FUNCTION SET DELETION ROUTINE (BY NAME PATTERN, OF EXISTING FUNCTION OBJECTS):
# === Usage: ===
# exec [AUX:DropFunctions] N'<Prefix token>:%'
# exec [AUX:DropFunctions] N'<Prefix token>::%'
# exec [AUX:DropFunctions] N'<Prefix token>:%', N'<Prefix token>::%'
# exec [AUX:DropFunctions] N'<Prefix token>\_%'
# ( Character “\” serves as “escape” for indication,
#   that “_” symbol is not used as placeholder. )
# *** CAUTION: ***
# USE THIS PROCEDURE WITH HIGH CAUTION
# TO AVOID UNINTENDED REMOVING OF FUNCTION OBJECTS!
*/
procedure [AUX:DropFunctions] @NamePattern      $(dt_String_AUX),
                              @ExceptionPattern $(dt_String_AUX) = null
/*==================================================================================================
# TABLE VIEW SET DELETION ROUTINE (BY NAME PATTERN, OF EXISTING VIEW OBJECTS):
# === Usage: ===
# exec [AUX:DropViews] N'<Prefix token>:%'
# exec [AUX:DropViews] N'<Prefix token>::%'
# exec [AUX:DropViews] N'<Prefix token>:%', N'<Prefix token>::%'
# exec [AUX:DropViews] N'<Prefix token>\_%'
# ( Character “\” serves as “escape” for indication,
#   that “_” symbol is not used as placeholder. )
# *** CAUTION: ***
# USE THIS PROCEDURE WITH HIGH CAUTION
# TO AVOID UNINTENDED REMOVING OF VIEW OBJECTS!
*/
procedure [AUX:DropViews] @NamePattern      $(dt_String_AUX),
                          @ExceptionPattern $(dt_String_AUX) = null
/*==================================================================================================
# DATA TYPE SET DELETION ROUTINE (BY NAME PATTERN, OF EXISTING TYPE OBJECTS):
# === Usage: ===
# exec [AUX:DropTypes] N'<Prefix token>:%'
# exec [AUX:DropTypes] N'<Prefix token>::%'
# exec [AUX:DropTypes] N'<Prefix token>:%', N'<Prefix token>::%'
# exec [AUX:DropTypes] N'<Prefix token>\_%'
# ( Character “\” serves as “escape” for indication,
#   that “_” symbol is not used as placeholder. )
# *** CAUTION: ***
# USE THIS PROCEDURE WITH HIGH CAUTION
# TO AVOID UNINTENDED REMOVING OF TYPE OBJECTS!
*/
procedure [AUX:DropTypes] @NamePattern $(dt_String_AUX),
                          @ExceptionPattern  $(dt_String_AUX) = null
/*==================================================================================================
# TRIGGER SET DELETION ROUTINE (BY NAME PATTERN, OF EXISTING TRIGGER OBJECTS):
# === Usage: ===
# exec [AUX:DropTriggers] N'<Prefix token>:%'
# exec [AUX:DropTriggers] N'<Prefix token>::%'
# exec [AUX:DropTriggers] N'<Prefix token>:%', N'<Prefix token>::%'
# exec [AUX:DropTriggers] N'<Prefix token>\_%'
#
# ( Character “\” serves as “escape” for indication
#   that “_” symbol is not used as placeholder. )
#
# *** CAUTION: ***
# USE THIS PROCEDURE WITH HIGH CAUTION
# TO AVOID UNINTENDED REMOVING OF TRIGGER OBJECTS!
*/
procedure [AUX:DropTriggers] @NamePattern       $(dt_String_AUX),
                             @ExceptionPattern  $(dt_String_AUX) = null


/*==================================================================================================
# EXECUTION RIGHT ASSIGNMENT — ON SET OF STORED PROCEDURES (BY NAME PATTERN, ON EXISTING OBJECTS):
# === Usage: ===
# exec [AUX:GrantExecOnProcedures] '<Prefix token>:%', <User name or user group name>
# exec [AUX:GrantExecOnProcedures] '<Prefix token>\_%', <User name or user group name>
# ( Character “\” serves as “escape” for indication,
#   that “_” symbol is not used as placeholder. )
# *** CAUTION: ***
# USE THIS PROCEDURE WITH HIGH CAUTION
# TO AVOID UNINTENDED EXECUTION RIGHT ASSIGNMENT!
*/
procedure [AUX:GrantExecOnProcedures]
   @ProcedureNamePattern   $(dt_String_AUX),
   @UserOrGroupName        $(dt_String_AUX)
/*==================================================================================================
# EXECUTION RIGHT REVOCATION — ON SET OF STORED PROCEDURES (BY NAME PATTERN, ON EXISTING OBJECTS):
# === Usage: ===
# exec [AUX:RevokeExecOnProcedures] '<Prefix token>:%', <User name or user group name>
# exec [AUX:RevokeExecOnProcedures] '<Prefix token>\_%', <User name or user group name>
#
# ( Character “\” serves as “escape” for indication,
#   that “_” symbol is not used as placeholder. )
# *** CAUTION: ***
# USE THIS PROCEDURE WITH HIGH CAUTION
# TO AVOID UNINTENDED EXECUTION RIGHT REVOCATION!
*/
procedure [AUX:RevokeExecOnProcedures]
   @ProcedureNamePattern   $(dt_String_AUX),
   @UserOrGroupName        $(dt_String_AUX)


/*==================================================================================================
# LISTING OF SQL PROCESSES (QUERIES):
# === Usage: ===
# exec [AUX:ListProcesses]                 -- all processes
# exec [AUX:ListProcesses] <Database name> -- processes in specified DB
# exec [AUX:ListProcesses] '.'             -- processes in current DB
# exec [AUX:ListProcesses] '*'             -- all processes
# exec [AUX:ListProcesses] null            -- processes without database context
*/
procedure [AUX:ListProcesses] @DatabaseName $(dt_String_AUX)='*'
/*
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# === Result-set: ===
# [Is current]         bit       not null
# [Database name]      sysname   not null
# [SPID]               int       not null
# [Status]             sysname   not null
# [Command]            sysname   not null
# [CPU]                int       not null
# [Login name]         sysname   not null
# [Application name]   sysname   not null
# [Host name]          sysname   not null
# [Login time]         datetime2 not null
# [Last batch time]    datetime2 not null
# ___ Order: ___
# : [Status] = 'runnable' — to list beginning;
# : current @@SPID — to list beginning;
# : current DB — to list beginning;
# [Database name] (heavy null-s);
# [SPID]
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
*/
