SQL Cheat Sheet

From Coolscript
Jump to navigation Jump to search

this is about sql statements which we have collected by the time.




Useful links

Glenn Berry's SQL Server Diagnostic Scripts

Or the local copy here: http://wiki.intern/index.php/Glenn_Berry%27s_SQL_Server_Diagnostic_Scripts

Top 10 Bookings

SELECT count(*) as exp1,bookingdate FROM tblIBE_LiveBookings_Basic where test=0 and bookingdate >= 20151216 and bookingdate <= 20170117 group by bookingdate order by exp1 desc

Add mySQL User

mysql> GRANT ALL PRIVILEGES ON mylvs.* TO mylvsuser@localhost IDENTIFIED BY 'demo';

mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'xxxxxxx';

HOT
GRANT ALL PRIVILEGES ON vfAutomation.* TO 'username'@'%' IDENTIFIED BY 'xxxxx';

Remove mySQL User

REVOKE ALL PRIVILEGES ON  vfAutomation.* FROM 'azoeller '@'%';
DROP USER 'azoeller'@'%';

List user

SELECT User FROM mysql.user;
SHOW GRANTS FOR 'user'@'host';


Change User pwd

SET PASSWORD FOR 'user'@'%' = PASSWORD('password');

Dump mySQL Table only

mysqldump -u root -p Statistic tblAirports > mysql_tblAirports.sql

Grant a machine name to security

CREATE LOGIN [MyDomain\MyMachineName$] FROM WINDOWS

List / kill user

SELECT SPID,STATUS, PROGRAM_NAME, LOGINAME=RTRIM(LOGINAME),HOSTNAME,CMD
FROM MASTER.DBO.SYSPROCESSES
WHERE DB_NAME(DBID) = 'MyDatabaseName' AND DBID != 0

Note You might need to set the permission for this too: 

GRANT VIEW ANY DEFINITION TO [fra01\misadmin];
GRANT VIEW SERVER STATE TO [fra01\misadmin];

To kill connections you would set: 

GRANT ALTER ANY CONNECTION TO [fra01\misadmin];

Tipp: 

kill spid

Close User Access

use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
--do you stuff here 
ALTER DATABASE YourDatabase SET MULTI_USER

List costs

  • This may work on MS SQL > 2005 only
ROUND(s.avg_total_user_cost *
  s.avg_user_impact
  * (s.user_seeks + s.user_scans),0)
  AS [Total Cost]
  ,d.[statement] AS [Table Name]
  ,equality_columns
  ,inequality_columns
  ,included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC

Move temp table

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'c:\MyDir\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'c:\MyDir\templog.ldf');
GO

Count data on a particiular hour per date

Select   
 sum(case when FlightType = 'WFE' then 1 else 0 end) WFECount,
 sum(case when FlightType = 'NET' then 1 else 0 end) NETCount,
 sum(case when FlightType = 'PUB' then 1 else 0 end) PubCount,
 BookingDate
 From tblIBE_LiveBookings_Basic
 group by BookingDate


Formular condition within select statement

use CountingService
go
DECLARE @sDate DATE='2013-03-16' DECLARE @eDate DATE='2013-04-16' 
SELECT        Count(*)  as exp1, case when pcc =  then user else pcc end, source 
FROM            Bookings
Where
convert(date, BookingDateTime) >= @sDate AND convert(date, BookingDateTime) <= @eDate
group by PCC,Source order by exp1 desc

Agent Job - delete by Date

DELETE FROM tblFlightAPIStoredFlight WHERE (FlightDateTime < GETDATE() - 1)


Bulk insert with T-SQL

  • Insert bulk.sql into table tblVia
BULK INSERT tblVia
  FROM 'u:\perl\webfares\Condor\bulk.sql'
  WITH 
     (
        FIELDTERMINATOR ='|',
        ROWTERMINATOR ='|\n'
     );


Bulk permissions

GRANT ADMINISTER BULK OPERATIONS TO [intern\misadmin]

Insert & Join

  • MS
UPDATE tblIBE_LiveBookings_Basic
SET Test = 0 
FROM            tblIBE_LiveBookings_Basic INNER JOIN
                        tblIBE_LiveBookings_Passenger ON tblIBE_LiveBookings_Basic.ID = tblIBE_LiveBookings_Passenger.UniqueID
WHERE        (tblIBE_LiveBookings_Passenger.LastName NOT LIKE 'hhiker%') AND (tblIBE_LiveBookings_Passenger.FirstName NOT LIKE 'hhiker%') AND 
                        (tblIBE_LiveBookings_Basic.Test = 1) AND (tblIBE_LiveBookings_Basic.BookingDate LIKE '201301%') AND 
                        (tblIBE_LiveBookings_Passenger.FirstName NOT LIKE 'hitch%') AND (tblIBE_LiveBookings_Passenger.FirstName NOT LIKE 'test%') AND 
                        (tblIBE_LiveBookings_Passenger.LastName NOT LIKE 'hitch%') AND (tblIBE_LiveBookings_Passenger.LastName NOT LIKE 'test%') AND 
                        (tblIBE_LiveBookings_Basic.Filekey <> 'TESTKEY') and (tblIBE_LiveBookings_Basic.Filekey <> 'TESTBOOKING')


  • MySQL
UPDATE tblIBE_LiveBookings_Basic
left JOIN tblIBE_LiveBookings_Passenger ON tblIBE_LiveBookings_Basic.ID = tblIBE_LiveBookings_Passenger.UniqueID
SET Test = 0
WHERE        (tblIBE_LiveBookings_Passenger.LastName NOT LIKE 'hhiker%') AND (tblIBE_LiveBookings_Passenger.FirstName NOT LIKE 'hhiker%') AND
                        (tblIBE_LiveBookings_Basic.Test = 1) AND (tblIBE_LiveBookings_Basic.BookingDate LIKE '201306%') AND
                        (tblIBE_LiveBookings_Passenger.FirstName NOT LIKE 'hitch%') AND (tblIBE_LiveBookings_Passenger.FirstName NOT LIKE 'test%') AND
                        (tblIBE_LiveBookings_Passenger.LastName NOT LIKE 'hitch%') AND (tblIBE_LiveBookings_Passenger.LastName NOT LIKE 'test%') AND
                        (tblIBE_LiveBookings_Basic.Filekey <> 'TESTKEY') and (tblIBE_LiveBookings_Basic.Filekey <> 'TESTBOOKING');


  • Check this for join, left join, right join and outer join:
http://joins.spathon.com/

MySQL Add and Del User

CREATE USER 'admin'@'%' IDENTIFIED BY 'xxxxx';	
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
DROP USER 'admin'@'%';

MySQL Create user by script

#!/bin/bash
DATABASE="bla"
USERNAME="bla"
PASSWORD="bla"
Q1="create database if not exists $DATABASE;"
Q2="grant usage on $DATABASE.* to $USERNAME@localhost identified by '$PASSWORD';"
Q3="grant all privileges on $DATABASE.* to $USERNAME@localhost;"
SQL="${Q1}${Q2}${Q3}"
mysql -u root -p -e "$SQL"

MS SQL restore Database to a new DataBase Name

RESTORE DATABASE FlightAPI_DER FROM DISK='d:\FlightAPI.bak'
WITH 
  MOVE 'FlightAPI' TO 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FlightAPI_DER.mdf',
  MOVE 'FlightAPI_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FlightAPI_DER_log.ldf'


Grant execute to stored procedures

use vfEntry_HLKDB1;
GRANT EXEC TO [production\ibeadmin]

Grant select to table only (MS)

1: Create the user and map public permission to the table, then: 

GRANT SELECT ON "dbo"."tblFlightAPIErrorDefinitions" TO "fapireader";

Set multi user

-- Start in master
USE MASTER;
ALTER DATABASE [vfEntry_HLKDB1] SET MULTI_USER

View user permission

SELECT * FROM fn_my_permissions(NULL, 'SERVER');

SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

Delete data older then n days

DELETE FROM tblBackOfficeFileTransferLog
WHERE        (Created < DATEADD(day, - 90, GETDATE()))

Restore database which is in use

use master;
ALTER DATABASE FlightAPIProd SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

use master;
RESTORE DATABASE FlightAPIProd FROM DISK = 'C:\fapi.prod.bak' WITH REPLACE

use master;
ALTER DATABASE FlightAPIProd SET MULTI_USER


Highest CPU Rate

-- Find queries that have the highest average CPU usage
SELECT TOP 50
   ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
   ,TextData           = qt.text   
   ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads
   ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads
   ,Executions         = qs.execution_count
   ,TotalCPUTime       = qs.total_worker_time
   ,AverageCPUTime     = qs.total_worker_time/qs.execution_count
   ,DiskWaitAndCPUTime = qs.total_elapsed_time
   ,MemoryWrites       = qs.max_logical_writes
   ,DateCached         = qs.creation_time
   ,DatabaseName       = DB_Name(qt.dbid)
   ,LastExecutionTime  = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time/qs.execution_count DESC


  • Another:
-- Find queries that take the most CPU overall
SELECT TOP 50
   ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
   ,TextData           = qt.text
   ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads
   ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads
   ,Executions         = qs.execution_count
   ,TotalCPUTime       = qs.total_worker_time
   ,AverageCPUTime     = qs.total_worker_time/qs.execution_count
   ,DiskWaitAndCPUTime = qs.total_elapsed_time
   ,MemoryWrites       = qs.max_logical_writes
   ,DateCached         = qs.creation_time
   ,DatabaseName       = DB_Name(qt.dbid)
   ,LastExecutionTime  = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time DESC


Show disk space by table

SELECT 
   t.NAME AS TableName,
   s.Name AS SchemaName,
   p.rows AS RowCounts,
   SUM(a.total_pages) * 8 AS TotalSpaceKB, 
   SUM(a.used_pages) * 8 AS UsedSpaceKB, 
   (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
   sys.tables t
INNER JOIN      
   sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
   sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
   sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
   sys.schemas s ON t.schema_id = s.schema_id
WHERE 
   t.NAME NOT LIKE 'dt%' 
   AND t.is_ms_shipped = 0
   AND i.OBJECT_ID > 255 
GROUP BY 
   t.Name, s.Name, p.Rows
ORDER BY 
   totalspacekb


Join for FAPI User and PCC

  • Use this query to show a pcc/user summary (without passwords) and save your time to answer annoying question
SELECT        tblFlightAPILogin.UserName, tblFlightAPIPCC.GUID, tblFlightAPIPCC.PCC, tblFlightAPIPCC.TerminalCountry, tblFlightAPIPCC.GDSName, tblFlightAPIPCC.PCCUser
FROM            tblFlightAPIPCC INNER JOIN
tblFlightAPILogin ON tblFlightAPIPCC.GUID = tblFlightAPILogin.UserGuid AND (tblFlightAPIPCC.GDSName = 'F1' OR tblFlightAPIPCC.GDSName = 'T1')
  • Use this query to show all pcc/user details
SELECT        tblFlightAPILogin.UserName, tblFlightAPIPCC.GUID, tblFlightAPIPCC.PCC, tblFlightAPIPCC.IsDefault, tblFlightAPIPCC.TerminalCountry, tblFlightAPIPCC.Password, tblFlightAPIPCC.GDSName, 
              tblFlightAPIPCC.PCCUser, tblFlightAPIPCC.ReferenceID, tblFlightAPIPCC.OrganisationID, tblFlightAPIPCC.PCCID, tblFlightAPIPCC.AccessPoint
FROM          tblFlightAPIPCC INNER JOIN
              tblFlightAPILogin ON tblFlightAPIPCC.GUID = tblFlightAPILogin.UserGuid AND (tblFlightAPIPCC.GDSName = 'F1' OR
              tblFlightAPIPCC.GDSName = 'T1')


Rebuild all indexes

DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT 

SET @fillfactor = 90 

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases   
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')   
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT [ + table_catalog + ].[ + table_schema + ].[ + 
  table_name + ] as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES 
  WHERE table_type = BASE TABLE'   

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor   

   FETCH NEXT FROM TableCursor INTO @Table   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command 
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
           EXEC (@cmd) 
       END
       ELSE
       BEGIN
          -- SQL 2000 command 
          DBCC DBREINDEX(@Table,' ',@fillfactor)  
       END

       FETCH NEXT FROM TableCursor INTO @Table   
   END   

   CLOSE TableCursor   
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor


Reduce Records in FAPI DB

Use the following statement to delete flight data following foreign keys 

DELETE FROM tblFlightAPIStoredFlight WHERE (FlightDateTime < GETDATE() - 7)

Transfer Database to a another SQL and create users from there

Problem: We need to transfer the PathFinder DB into the new Velia network and then create and assign local domain users

  • 1 Backup

Hint: Make sure you delete the provious backup to avoid FILE assignments 

backup database PathFinderDB2 to disk='e:\backup\PathFinderDB2.bak'
  • 2 Restore
use Master;
RESTORE DATABASE PathFinderDB2
FROM DISK = 'C:\PathFinderDB2.bak'
WITH MOVE 'PathFinderDB2' TO 'C:\Data\PathFinderDB2.mdf',
MOVE 'PathFinderDB2_Log' TO 'C:\Data\PathFinderDB2.ldf',
FILE=1,REPLACE;
  • 3: Assign local domain users to the new restored Database
use PathFinderDB2;
CREATE USER [FRA01\hhservice] FOR LOGIN [FRA01\hhservice]
EXEC sp_addrolemember 'db_datareader', 'FRA01\hhservice';
EXEC sp_addrolemember 'db_datawriter', 'FRA01\hhservice';
EXEC sp_addrolemember 'db_executestoredprocedures', 'FRA01\hhservice';

Create and assign user in ms sql

use PathFinderDB2;
CREATE USER [FRA01\hhservice] FOR LOGIN [FRA01\hhservice]
EXEC sp_addrolemember 'db_datareader', 'FRA01\hhservice';

Grant permission to a single table only

  • Assign Public permissions to the database

Optional: 

Use Statistic
exec sp_msforeachtable "DENY SELECT ON '?' TO [intern\informationservice];"

Then: 

GRANT SELECT ON tblAirports to [intern\informationservice]

Change Logical File Name (MSSQL)

  • Get the current name first, sample db = AlomaClient_AvorisNortravel
USE AlomaClient_AvorisNortravel
GO
SELECT file_id, name as [logical_file_name],physical_name
from sys.database_files

Return 

1	AlomaClient1_New_Data	C:\Data\AlomaClient_AvorisNortravel.mdf
2	AlomaClient1_New_Log	C:\Data\AlomaClient_AvorisNortravel_1.ldf
  • Change the name by:
USE [master];
GO
ALTER DATABASE [AlomaClient_AvorisNortravel] MODIFY FILE ( NAME = AlomaClient1_New_Data, NEWNAME = AlomaClient_AvorisNortravel_Data );
GO

And 

USE [master];
GO
ALTER DATABASE [AlomaClient_AvorisNortravel] MODIFY FILE ( NAME = AlomaClient1_New_Log, NEWNAME = AlomaClient_AvorisNortravel_Log );
GO


Most I/O intensive query the last 6 hours

  • Note: The query can take a long time to complete
SELECT TOP (20)last_execution_time, total_logical_reads/execution_count AS [avg_logical_reads],
   total_logical_writes/execution_count AS [avg_logical_writes],
   total_worker_time/execution_count AS [avg_cpu_cost], execution_count,
   total_worker_time, total_logical_reads, total_logical_writes, 
   (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), ) 
    FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
   (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
       (CASE WHEN statement_end_offset = -1
           THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
           ELSE statement_end_offset
           END - statement_start_offset
       ) / 2)
       FROM sys.dm_exec_sql_text(sql_handle) AS est) AS query_text,
   last_logical_reads, min_logical_reads, max_logical_reads,
   last_logical_writes, min_logical_writes, max_logical_writes,
   total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads,
   (total_logical_reads + (total_logical_writes * 5))/execution_count AS io_weighting,
   plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND (total_worker_time/execution_count) > 100 AND last_execution_time>DATEADD(hh,-6,GETDATE())
ORDER BY io_weighting DESC;

Transaction per second

TransactionCache

DECLARE @cntr_value bigint
SELECT @cntr_value = cntr_value
   FROM sys.dm_os_performance_counters
   WHERE counter_name = 'transactions/sec'
       AND object_name = 'SQLServer:Databases'
       AND instance_name = 'TransactionCache'
WAITFOR DELAY '00:00:01'
SELECT cntr_value - @cntr_value
   FROM sys.dm_os_performance_counters
   WHERE counter_name = 'transactions/sec'
       AND object_name = 'SQLServer:Databases'
       AND instance_name = 'TransactionCache'

BadServiceStore

DECLARE @cntr_value bigint
SELECT @cntr_value = cntr_value
   FROM sys.dm_os_performance_counters
   WHERE counter_name = 'transactions/sec'
       AND object_name = 'SQLServer:Databases'
       AND instance_name = 'BADServiceStore'
WAITFOR DELAY '00:00:01'
SELECT cntr_value - @cntr_value
   FROM sys.dm_os_performance_counters
   WHERE counter_name = 'transactions/sec'
       AND object_name = 'SQLServer:Databases'
       AND instance_name = 'BADServiceStore'

Create new user over an existing database user

use database;
alter user <user> with login=<user>

Create Identity for Azure Managed Instance

CREATE CREDENTIAL [1]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
, SECRET = 'sp=racwdl.......'
  • Drop
drop CREDENTIAL [2]
Retrieved from "https://coolscript.org/index.php?title=SQL_Cheat_Sheet&oldid=599"