SQL cheat sheet

From test.coolscript.org
Jump to navigation Jump to search

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



Grant a machine name to security[edit]

CREATE LOGIN [MyDomain\MyMachineName$] FROM WINDOWS

List user[edit]

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

List costs[edit]

  • 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[edit]

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[edit]

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 dependency within select statement[edit]

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