How to make Singleton sp in SQL server

http://www.sqlnotes.info/2012/10/10/update-with-updlock/

update MySignal with(updlock)  set Description  = 'Value 1' where ID = 72057594038910976

Finding all tables and columns used in a sp

http://www.sqlnotes.info/2015/11/19/get-all-referenced-tables-and-columns/#more-2586

create function GetReferencedColumns(@Plan xml)
returns table
as
return(
 with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as p),
 src as(
   select 
     c.value('@Server', 'varchar(128)') ServerName, 
     c.value('@Database', 'varchar(128)') DatabaseName, 
     c.value('@Schema', 'varchar(128)') SchemaName, 
     c.value('@Table', 'varchar(128)') TableName, 
     c.value('@Column', 'varchar(128)') ColumnName
   from @plan.nodes('//p:ColumnReference') n(c)
   )
 select distinct ServerName, DatabaseName, SchemaName, TableName, ColumnName
 from src
 where TableName is not null
)

Query Store

Find out user databases  where query store is not enabled.

select is_query_store_on,* from sys.databases
where is_query_store_on = 0 and database_id > 4

How to view dll contents with Powershell

-For viewing .dll classes and its methods in powershell follow this article as this will allow to display all details of the dll in sortable dialog box

https://blog.netspi.com/using-powershell-and-reflection-api-to-invoke-methods-from-net-assemblies/

$Results=@()
Get-ChildItem -recurse "D:\Documents\Visual Studio 2010\Projects\AesSample\AesSample\bin\Debug\"|
Where-Object { ($_.Extension -EQ ".dll") -or ($_.Extension -eq ".exe")} | 
ForEach-Object {
 $AssemblyName= $_.FullName; try {$Assembly = [Reflection.Assembly]::LoadFile($AssemblyName);} catch{ "***ERROR*** Error when loading assembly: " + $AssemblyName} $Assembly | Format-Table; $Assembly.GetTypes() |
 %{
 $Type=$_;$_.GetMembers() | Where-Object {$_.MemberType -eq "Constructor"-or $_.MemberType -EQ "Method" } | 
 %{
 $ObjectProperties = @{ 'Assembly' = $AssemblyName;
 'ClassName' = $Type.Name;
 'ClassPublic' = $Type.IsPublic;
 'ClassStatic' = $Type.IsAbstract -and $Type.IsSealed;
 'MemberType' = $_.MemberType;
 'Member' = $_.ToString();
 'Changed' = $Changed;
 'MemberPublic' = $_.IsPublic;
 'MemberStatic' =$_.IsStatic;
 }
 $ResultsObject = New-Object -TypeName PSObject -Property $ObjectProperties
 $Results+=$ResultsObject
 }
 }
}
$Results | Select-Object Assembly,ClassPublic,ClassStatic,ClassName,MemberType,Member,MemberPublic,MemberStatic | Sort-Object Assembly,ClassName,MemberType,Member| Out-GridView -Title "Reflection"