Table of Contents

Table of Contents

SQL

Drop Table if Exists

Database and Logfile Sizes

Log file sizes v2

sql table sizes

Rebuild all indexes on DB's or Rebuilt all indexes on a specific DB's

Current SQL Authentication Method

tsql to show connections and their auth type

SQL HTML email as a table

Alert for getting a more recent version of a Cert

Check for Duplicate - Example

Select rows that occur only once

Manipulating Error Logs

Export out to a file

Search Scheduled Jobs for String

Scheduled Job info

Last access on table

Grants and Denys for a Windows account on a table

Deny View on all databases to a specific user/group

Query users that *Can* View All Databases

Show Users and Roles - Shows Custom Groups too

Generic User and Rolename

Standard login users in roles

Process Monitor

Show blocking resources

Show Users (generic)

List columns on a table

Show OFFLINE (and other states) of a database

Show Recovery models of all DB’s

Set all User DBs to SIMPLE Recovery mode

Determine Cache Type

Linked Server Anonymous

https://support.microsoft.com/en-us/kb/925001

EXCEPT and INTERSECT Comparing Tables

https://msdn.microsoft.com/en-us/library/ms188055.aspx

Synonyms or creating a Database table links that point to other database tables

Connecting to localdb

Missing tables and columns

Count Number of Connections

CPU Utilization for last 30 minutes

SQL Job to record CPU Utilization and Number of Users per DB

PIVOT table to retrieve the info in a friendly format

Pivot with Weeknum

--Example 1 - Skeleton

--Example 2 - CCDA

Grant Server State attribute to user

Grant Execute for a Stored Procedure for a User

Trigger to alert when database server admin roles are added

Setup Alerts for AlwaysOn

Alert if a SQLAgent Job is Enabled or Disabled

Move Database locations with detach/attach

Move the TEMP Db's from one place to another

Move your databases without a Detach, but a backup/restore methodology

Create Delilmiated Strings with COALESCE

CreateTable from CSV

procdroptable - Proc drop table

Cycle through a Formatted List (ie ",")

Backup and Restore ETA

Last logins on server

Last time run for Stored Proc

Create Windows Login

List Tables Quickly

Link Server to Active Directory

Query AD and Populate a Table with Data

Convert EPOC time to DateTime

Convert time from DAY to DECIMAL

Dynamic Trace Log

dbo.procdroptable

Delete all data from all tables (even with triggers and constraints)

DDL_AUDIT_Logins (less info way)

DDL_AUDIT_Logins (More precise information)

TSQL Random String

LDAP - Check if a user is in a group

Get all users from an AD Group

EXEC results to a variable

Enable Server Wide Backup Compression

Disable Change Tracking

tsqls for custom CSV import

Custom Import that handles blank field and Quotes

BCP Export to CSV

Times of running queries

All of the Full and Trans log backup start and end times. Lots of data

CDC enabled or disabled

Log size monitor

Pivot to read Log size over days of week

Encryption at the Column Level

TRY/CATCH with ROLLBACK

Example with TRY/CATCH and CreateTable

Move Database files - no sql restart

Convert AD pwdLastSet to Datetime

AD pwdLastSet within 90 days

Get Local Time with GETDATE()

Mapping a Drive Drive Map

Export out Store Procs output to file from command line

Delete trigger to "waste" table example

Update/Insert Trigger example

Update joining back to original table

Monitor Recovery Mode/Status from Logs

MSSQL Show Last Restore timestamps of DB's

Get Cluster nodes and States of them

Dates from week number for a given number of weeks

Database Mail - Configuring

Command Line: Backdoor into SQL

Rebuild Index and Defrag - AWESOME

Finding Missing Indexes - Costs

Finding Missing Indexes - Costs (scripted)

mxfn_ParseDateTimeToCHAR

Show index fragmentation

ReIndex "Fancy"

Max Memory - Change - Works with oSQL too

"Restoring..." Stuck on it

Database in Suspect Mode

Take a Database OFFLINE immediately

Bring a database back ONLINE immediately

Transfer Logins between Servers

Fix Orphan User

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

Maintenance Plan Listing for DB's

Good casting of Date (swiped from HillRom)

Where Are My Saved Server List?

Log space used + Email Alert ability

Shrink Log Files [preserve CHECKPOINT]

xShrink Log files too

Password at Rest

uspLoginFun (as a function for queries)

ProperCase Function for Strings

Get Default Locations for Data, Log and Backup folders (all)

Get Default Locations for Data, Log and Backup folders (2012+)

Get most recent record

Identity INSERT ON OFF

SQL Export blob to Network Share

Easier example of import of CSV XLS

SQL Agent Jobs and Run Times

Cannot generate SSPI context

Bone Head Fixes

Force out of Single User Mode

Reattach a database that was previously in Standby

http://mikedefehr.com/2010/08/31/how-to-attach-a-database-in-standby/

Moving TempDB that goes bad/SQLCMD from Command Line

Database 'TestDb1' is in transition

Uninstall SSAS after broke install

Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)

Time

First Day of Week for a Date

Job failed alerts

epoch time to datetime

Datetime to epoch time

Convert UTC Time to Local Time

MSSQL to MySQL and back

Delete Previous Days Backups

Send Email from SQL job (i.e. if produce data)

SQL Agent Startup Alert Email

Cursor for blob to file system ole adodb.stream

Cursor between dates (format them nicely in output)

Email alert on disk space example

UNC output stored procedure

Export MySQL DB Schema

Parameter '@variable' must be defined

Export/Restore User passwords during migration

Create a full admin

Restore a DB from a mysqldump .sql file

mySQL output to flat file on the file system

Natural Sorting in MySQL

Convert SQLLite bigint to Date and Time