View source for Mssql

==Connect to database.==
 # tsql -H 192.168.0.1 -U user -P password -p 1433
 locale is "en_US.utf-8"
 locale charset is "UTF-8"
 using default charset "UTF-8"
==connect to database from powershell==
List all databases and show size used. Remove --... and use tab as separator. Print in more readable format.
 sqlcmd -Q "EXEC sp_MSforeachdb 'USE [?]; EXEC sp_spaceused;';"  -h -1 -s "`t" -W | ConvertFrom-Csv -Delimiter "`t"

==${HOME}/.freetds.conf (config file. .my.cnf)==
 [server_name]
   host = server_hostname
   port = 1433
   tds version = auto

==whoami==
 select suser_name()
 go
Via sqlcmd
 sqlcmd -h -1 -s "`t" -W -Q "SELECT
  LEFT(SUSER_NAME(), 30) AS CurrentUser,
  LEFT(DB_NAME(), 30) AS CurrentDatabase,
  LEFT(HOST_NAME(), 30) AS HostName,
  LEFT(CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(128)), 30) AS MachineName;"
==Show tables.==
 SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 go
 
 TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
 master  dbo     spt_fallback_db BASE TABLE
 master  dbo     spt_fallback_dev        BASE TABLE
 master  dbo     spt_fallback_usg        BASE TABLE
 master  dbo     spt_monitor     BASE TABLE
 master  dbo     spt_values      BASE TABLE
 (5 rows affected)

==create database==
 CREATE DATABASE database_name;
 go
==show databases==
 SELECT name FROM master..sysdatabases;
 go

==which version of mssql==
 select @@microsoftversion/0x01000000 as version;
 go

==show time==
SQL Server 2000/2005
 SELECT
 CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
 CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
 GO
SQL Server 2008
 SELECT
 CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
 CONVERT(DATE,GETDATE(),101) AS DateOnly
 GO
SQL Server 2022
 SELECT CONVERT(VARCHAR(33), GETDATE(), 126) AS ISODateTime;
 go

==Uptime in minutes==
 select datediff(minute,create_date, getdate()) as minutes from master.sys.databases    where name='tempdb';
 go
 select datediff(minute,crdate,      getdate()) as minutes from master.dbo.sysdatabases where name='tempdb';
 go

==use database==
 USE DATABASE;

==show permissions==
 SELECT 
    class_desc 
  , CASE WHEN class = 0 THEN DB_NAME()
         WHEN class = 1 THEN OBJECT_NAME(major_id)
         WHEN class = 3 THEN SCHEMA_NAME(major_id) END [Securable]
  , USER_NAME(grantee_principal_id) [User]
  , permission_name
  , state_desc
 FROM sys.database_permissions
==command line with queuery==
 tsql -H 192.168.0.1 -U user -P password -p 1433 -o q < /tmp/tsql
==queries running==
 SELECT sqltext.TEXT,
 req.session_id,
 req.status,
 req.command,
 req.cpu_time,
 req.total_elapsed_time
 FROM sys.dm_exec_requests req
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext;
 go
==troubleshooting script==
mssql_troubleshooting.ps1
 # abjorklund script to troubleshoot mssql performance issues.
 # Name of script.
 $scriptName = "mssql_troubleshooting.ps1"
 # Hostname
 $hostname = $env:COMPUTERNAME
 # Define the log file path and name with the current date
 $logfile = "C:\temp\$scriptName.$hostname"
 # Infinite loop that runs continuously
 while ($true) {
   # Get the current date in the format yyyy-MM-dd
   $date = (Get-Date).ToString("yyyy-MM-dd")
   # Current time
   $DateWithSecond = (Get-Date).ToString("yyyy-MM-dd hh:mm:ss")
   # Define the full log file path with the current date
   $logfileWithDate = "$logfile.$date.log"
   # Write date to logfile
   (Get-Date).ToString("yyyy-MM-dd hh:mm:ss") | Out-File -Append -FilePath $logfileWithDate
   # cpu usage of machine
   $cpuUsage = Get-WmiObject Win32_Processor | Select-Object -ExpandProperty LoadPercentage ; $averageCpuUsage = ($cpuUsage | Measure-Object -Average).Average ; "cpu:{0:N0}%" -f $averageCpuUsage | Out-File -Append -FilePath $logfileWithDate
   # Memory usage of machine.
   $memory = Get-WmiObject Win32_OperatingSystem ; $totalMemory = $memory.TotalVisibleMemorySize ; $freeMemory = $memory.FreePhysicalMemory ; $usedMemory = $totalMemory - $freeMemory ; $memoryUsage = ($usedMemory / $totalMemory) * 100 ; "mem:{0:N0}%" -f $memoryUsage | Out-File -Append -FilePath $logfileWithDate
   # How many queres are running.
   sqlcmd -h -1 -s "`t" -W -r 0 -Q "SET NOCOUNT ON;SELECT 'RunningQueries=' + CAST(COUNT(*) AS VARCHAR) AS RunningQueries FROM sys.dm_exec_requests WHERE status = 'running' AND session_id <> @@SPID;" | Out-File -Append -FilePath $logfileWithDate
   # Queries over 1 second.
   sqlcmd -s "`t" -W -r 0 -Q "SET NOCOUNT ON;SELECT sqltext.TEXT AS QueryText, req.session_id, req.status, req.command, req.cpu_time, req.total_elapsed_time FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext WHERE req.total_elapsed_time > 1000 ORDER BY req.total_elapsed_time DESC;" | Out-File -Append -FilePath $logfileWithDate
   # Sleep for a certain time before running again (e.g., 1 second)
   Start-Sleep -Seconds 60
 }