Skip to content

Instantly share code, notes, and snippets.

Avatar

Peter Schott paschott

View GitHub Profile
@paschott
paschott / Script_Permissions.sql
Last active Apr 13, 2017
TSQL Script DB Permissions
View Script_Permissions.sql
SELECT
state_desc + ' ' + permission_name + ' on ['+ ss.name + '].[' + so.name + ']
to [' + sdpr.name + ']' COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN sys.objects AS so
ON sdp.major_id = so.OBJECT_ID
JOIN SYS.SCHEMAS AS ss
ON so.SCHEMA_ID = ss.SCHEMA_ID
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
ON sdp.grantee_principal_id = sdpr.principal_id
@paschott
paschott / ExtendedEvent_LongRunningQuery.sql
Created Apr 13, 2017
Create an Extended Event Trace for Long-Running Queries
View ExtendedEvent_LongRunningQuery.sql
CREATE EVENT SESSION [Long-Running Queries] ON SERVER
ADD EVENT sqlserver.rpc_completed (
ACTION ( sqlserver.client_app_name, sqlserver.client_hostname,
sqlserver.database_id, sqlserver.database_name, sqlserver.nt_username,
sqlserver.query_hash, sqlserver.server_principal_name,
sqlserver.session_id, sqlserver.sql_text )
WHERE ( ( ( package0.greater_than_uint64(sqlserver.database_id, ( 4 )) )
AND ( package0.equal_boolean(sqlserver.is_system, ( 0 )) ) )
AND ( duration >= ( 500000 ) )
) ),
@paschott
paschott / WorkAroundConstraintsWithMerge.sql
Created Apr 13, 2017
Example of working around FK Constraint restrictions with a MERGE statement
View WorkAroundConstraintsWithMerge.sql
--1: Create Temp table
CREATE TABLE #MyFactTable(
ID INT NULL
, CustomerName VARCHAR(100) NULL
, SourceID INT NULL
, OutputAction VARCHAR(100) NULL
);
--2: INSERT into the temp table instead of your normal target table
-- Merge query will be the same otherwise
@paschott
paschott / SQL Server DDL Audit.sql
Created Apr 13, 2017
Create a DB and triggers for DDL Audits in SQL Server
View SQL Server DDL Audit.sql
-------------------------------------------------------------
-- Create Audit Database
-------------------------------------------------------------
USE master
GO
CREATE DATABASE Audit
GO
USE [Audit]
GO
@paschott
paschott / BCP Data.sql
Created Apr 17, 2017
Generate batch files to BCP Data in/out. Can use some work to make this more flexible. Requires target to have a copy of the DB with empty tables.
View BCP Data.sql
--Need details on creating a batch file and running it. Include a "PAUSE" and newline at the end.
--Note - need to create the appropriate folders. Default is "C:\bcp\databasename".
--Source
SELECT 'bcp ' + DB_NAME() + '.dbo.' + Table_name + ' OUT "C:\bcp\' + DB_NAME() + '\' + Table_name + '.bcp" -S' + @@servername + ' -N -T -E -b 100000'
FROM Information_Schema.Tables
WHERE Table_Schema = 'dbo'
ORDER BY TABLE_NAME
SELECT 'PAUSE
'
@paschott
paschott / SSISDB Cleanup.sql
Last active Jun 20, 2019
Cleanup script for the SSISDB Catalog to make up for MS' poor performing query and high default days for history retention.
View SSISDB Cleanup.sql
--With thanks to Bill Fellows for the script from here:
-- http://stackoverflow.com/questions/21781351/how-can-i-clean-up-the-ssisdb
USE SSISDB;
SET nocount ON;
IF Object_id('tempdb..#DELETE_CANDIDATES') IS NOT NULL
BEGIN
DROP TABLE #delete_candidates;
END;
View Disabling_Triggers.sql
CREATE TABLE TriggerStatus (
TriggerStatusID INT IDENTITY(1,1) NOT NULL,
TriggerName varchar(100) NOT NULL,
IsEnabled BIT NOT NULL CONSTRAINT DF_TriggerStatus_IsEnabled DEFAULT 1,
CONSTRAINT PK_TriggerStatus PRIMARY KEY CLUSTERED (TriggerStatusID)
)
GO
CREATE TABLE MyAuditTable (
MyAuditTableID INT NOT NULL IDENTITY(1,1),
@paschott
paschott / MERGE_and_OUTPUT.sql
Created Nov 24, 2018
Use MERGE and OUTPUT in TSQL to capture the old and new values when importing data.
View MERGE_and_OUTPUT.sql
--Create test table to represent data we want to import.
CREATE TABLE #ProductsToImport (
OriginalSystemID INT,
ProductName NVARCHAR(50),
ProductNumber NVARCHAR(25),
SafetyStockLevel SMALLINT,
ReorderPoint SMALLINT,
StandardCost MONEY,
ListPrice MONEY,
DaysToManufacture INT,
@paschott
paschott / pr_SendSSISErrors.sql
Created Jan 2, 2019
Reporting for SSIS Errors
View pr_SendSSISErrors.sql
--Note that this uses a DB Mail Profile named "Default".
CREATE PROC dbo.pr_SendSSISErrors
@FolderName sysname = NULL,
@ProjectName sysname = NULL,
@PackageName sysname = NULL,
@ToAddress NVARCHAR(500)
AS
BEGIN --Proc
SET NOCOUNT ON
@paschott
paschott / ImportSlack.ps1
Created Jan 19, 2019
Imports Slack files into a "Posts" table in SQL Server.
View ImportSlack.ps1
# lets just bucket all the comments from all users and then print it out
# for each file, read the file sequentially and add it to a hash table count
$database = "SQLSlack"
$server = "localhost"
$parent_folder = "C:\SlackExport"
Import-Module SqlServer
function Invoke-Sql {