tcartwright

/*
    TIM C: 
    Purpose: To check the check and foreign keys that are disabled or not trusted of all user databases or a single db for invalid data in tables by 
    using DBCC CHECKCONSTRAINTS, and generate the cleanup scripts, and the scripts to re-enable all constraints. 
    Will also try to re-enable untrusted constraints. 

    NOTE: Change the @current_db_only variable to run either just the current db, or all dbs
*/
DECLARE @current_db_only BIT = 1,
    @db_id VARCHAR(20) = NULL

tcartwright

-- https://www.techrepublic.com/blog/the-enterprise-cloud/find-blocking-processes-using-recursion-in-sql-server-2005/
-- TIM C: 
--    1) Modified the query so as not to return multiple sets for each block 
--    2) Switched to using dm_exec_requests    

IF OBJECT_ID('tempdb..#Processes') IS NOT NULL BEGIN 
    DROP TABLE #Processes 
END

CREATE TABLE #processes (

tcartwright

/* 
    -- TIM C 
    Purpose: To show any runnning transactions, and how much transaction activity is being generated. Will also show
    if the spid is blocking any other spids and what spids are being blocked. Can be helpful in determining the 
    rollback impact of killing a SPID. 
    
    NOTE: Does not show any queries that are NOT enlisted in transactions.

    Origin SQL sourced from these articles: 
        -- https://www.red-gate.com/simple-talk/sql/database-administration/investigating-transactions-using-dynamic-management-objects/

tcartwright

@rem bat file to ease use of the powershell

@%~d0
@cd "%~dp0"

@REM NOTE: Save the PostBuildEvent.ps1 to your project directory 
powershell.exe -ExecutionPolicy Bypass -NoLogo -NoProfile -file "$(ProjectDir)PostBuildEvent.ps1" -TargetPath "$(TargetPath)"

tcartwright

Clear-Host

# NOTE: IF YOU GET AN EXECUTION POLICY ERROR RUN THIS:
#     Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy Bypass

# NOTE: THIS MUST BE RUN FROM POWERSHELL ISE (X86) 
#     OTHERWISE YOU GET: "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine."


# ********************************************************************

tcartwright

SET NOCOUNT ON

DECLARE @sql NVARCHAR(MAX), 
    @nl CHAR(1) = CHAR(10),
    @tab CHAR(1) = CHAR(9)    

SELECT @sql = CAST((
    SELECT    'RAISERROR(''REFRESHING ' + o.type_desc + ' ' + fn.name + ''', 0, 1) WITH NOWAIT;' + @nl 
        + 'BEGIN TRY EXEC sys.sp_refreshsqlmodule  @name = N''' + fn.name 
            + '''; END TRY BEGIN CATCH PRINT CHAR(9) + ''ERROR: '' + ERROR_MESSAGE(); END CATCH;' + @nl + @nl

tcartwright

Converted into a module: https://github.com/tcartwright/tcdbtools

PS Gallery link: https://www.powershellgallery.com/packages/tcdbtools/

tcartwright

/*
TIM C: This is an example script showing two ways of archiving data from one table 
to another. I am not the originator of this technique. I am just documenting it here so as to be 
shareable with others who ask.

This is typically more efficient and produces shorter and less locks than the standard:

	INSERT INTO ARCHIVE_TABLE
	SELECT ... FROM MAIN_TABLE WHERE ...

tcartwright

/*
TIM C: I wrote this so I can dump server level security and easily glance over it, as well as: 
	- can be used to compare security from server to server
	- automatically exported every so often so changes can be monitored over time
	- used to clean up dead accounts
	- password hash could be added to the sql users for AG primary->secondary comparison, 
		- TO ENABLE PWD HASH EXPORT CHANGE @output_pwds BELOW
*/

BEGIN --principals table

tcartwright

-- my roles
SELECT [level] = 'SERVER', name, [is_member] = IS_SRVROLEMEMBER(name) 
FROM sys.server_principals WHERE type = 'R'
UNION 
SELECT [level] = 'DATABASE', name, [is_member] = IS_MEMBER(name) 
FROM sys.database_principals WHERE type = 'R'

-- my permissions