SQL Cheat Sheet: Difference between revisions

From Coolscript
Jump to navigation Jump to search
(Created page with "this is about sql statements which we have collected by the time. <br><br><br><br> =Useful links= Glenn Berry's SQL Server Diagnostic Scripts *https://www.sqlskills.com/blog...")
 
 
Line 478: Line 478:
         AND object_name = 'SQLServer:Databases'
         AND object_name = 'SQLServer:Databases'
         AND instance_name = 'BADServiceStore'
         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 [https://SANAME.blob.core.windows.net/CONTAINER]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
, SECRET = 'sp=racwdl.......'
*Drop
drop CREDENTIAL [https://stcoldabn01.blob.core.windows.net/cold-abn-data]

Latest revision as of 17:21, 16 December 2021

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"