T-SQL: Ensure Check Constraints Are Trusted

Here’s a snippet of dynamic SQL that will ensure your check constraints are trusted (“with check” rather than “with no check”). It uses the FOR XML PATH(”) “trick” to concatenate the result set into a readable set of queries:

DECLARE @SQL NVarchar(MAX)
DECLARE @NewLine CHAR(2) = CHAR(10) + CHAR(13)
 
SELECT @SQL = REPLACE((
SELECT 
  'Print ''Fixing check constraint on ' + quotename(s.name) + '.' + quotename(t.name) + '.'';' + 
  'Alter Table ' + quotename(s.name) + '.' + quotename(t.name) + 
  ' With Check Check Constraint ' + fk.name +';'
FROM sys.foreign_keys AS fk
JOIN sys.tables AS t
  ON fk.parent_object_id = t.object_id
JOIN sys.schemas AS s
  ON t.schema_id = s.schema_id
WHERE 
  fk.is_not_trusted = 1
FOR Xml Path('')
), ';', (';' + @NewLine))
 
EXEC(@SQL)

Hope this helps!

Posted in T-SQL | Leave a comment

T-SQL: Find Out Who Has Locks In Your Databases

Here are a couple of ways to see who is locking up your SQL Server database.

First, the undocumented system stored procedure sp_who2 gives you a few more columns than the documented sp_who.

And second, this custom query gives a more tailored view of the user and the locks. It also gives you the option of tailoring the sort to your needs.

SELECT DISTINCT
  name AS database_name,
  session_id,
  host_name,
  login_time,
  login_name,
  reads,
  writes
FROM sys.dm_exec_sessions
LEFT OUTER JOIN sys.dm_tran_locks 
  ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
JOIN sys.databases 
  ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE 
  resource_type <> 'DATABASE'
  AND name ='MyDatabase'
ORDER BY 
  name

Hope this helps!

Posted in T-SQL | Leave a comment

Azure PowerShell: Copy All Files in Blob Storage to Local Directory

Here’s a Azure PowerShell script that downloads all blobs in a storage container to your local disk:

 
$storageAccountName = "mysuperstorage"
$containerName = 'my-blobs'
$destinationFolder = 'C:\temp\my-blobs'
 
New-Item -ItemType Directory -Force -Path $destinationFolder
 
Login-AzureRmAccount
 
$storageAccountContext = (Get-AzureRmStorageAccount | Where-Object{$_.StorageAccountName -eq $storageAccountName}).Context
 
$blobs = Get-AzureStorageBlob -Container $containerName -Context $storageAccountContext
foreach ($blob in $blobs)
{  
  Get-AzureStorageBlobContent -Container $containerName -Blob $blob.Name -Destination $destinationFolder -Context $storageAccountContext
}

Hope this helps!

Posted in Azure, PowerShell | Leave a comment

PowerShell: Read Secure String and Convert Back to Plain Text

Here’s a quick way to read in a secure string and then convert it back to plain text:

$SecureString = Read-Host -Prompt "What is the secret?" -AsSecureString
$ClearTextString = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecureString))

Also, take a look at the ConvertTo-SecureString and ConvertFrom-SecureString functions of PowerShell.

Hope this helps!

Posted in PowerShell | Leave a comment

PowerShell: Generate a Pseudo-Random String

Here’s a quick way to generate a pseudo-random string with PowerShell:

$Length = 20
$AllowedCharacters = "abcdefghijklmnopqrstuvwxyz0123456789".ToCharArray()
$MyRandomString = -join (Get-Random -Count $Length -InputObject $AllowedCharacters)

Hope this helps!

Posted in PowerShell | Leave a comment

PowerShell: Parse all Text Files in a Directory for a String Pattern

Here’s a snippet of PowerShell that goes through all files in a directory looking for a string. Those files that match the string pattern are then moved to another directory.

Set-Location C:\somewhere
Get-ChildItem -Recurse | Select-String -pattern "Delivery Status Notification" | Group Path | Select -ExpandProperty Name | Move-Item -Destination C:\temp\emails_moved\

Hope this helps!

Posted in PowerShell | Leave a comment