sp_helpuser

SELECT * FROM sys.database_principals


SELECT loginname FROM syslogins where loginname like '%NAME%'


EXEC sp_helplogins

ctrl-F6

exec sp_msforeachdb
‘use [?];
select [DBName]=”?”, ds.* from sys.data_spaces ds where type=”FD”’

-- Locking
select cmd,* from sys.sysprocesses
where blocked > 0

SELECT command,
            s.text,
            start_time,
            percent_complete, 
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

SELECT
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id,
o1.name 'object name',
o1.type_desc 'object descr',
p1.partition_id 'partition id',
p1.rows 'partition/page rows',
a1.type_desc 'index descr',
a1.container_id 'index/page container_id'
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
	ON t1.lock_owner_address = t2.resource_address
LEFT OUTER JOIN sys.objects o1 on o1.object_id = t1.resource_associated_entity_id
LEFT OUTER JOIN sys.partitions p1 on p1.hobt_id = t1.resource_associated_entity_id
LEFT OUTER JOIN sys.allocation_units a1 on a1.allocation_unit_id = t1.resource_associated_entity_id

use [database name]
go
SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

How to add a subtotal row in sql
This comes from: http://stackoverflow.com/questions/19387650/how-to-add-a-subtotal-row-in-sql

Name Score
A     2
B     3
A     1
B     3

Name Score
A     2
A     1
Subtotal 3
B    3
B    3
Subtotal 6


create table scores (name varchar(10), Score integer)

insert into scores values ('A', 2)
go
insert into scores values ('B', 3)
go
insert into scores values ('A', 1)
go
insert into scores values ('B', 3)
go



with cte as (
    select *, row_number() over(order by newid()) as rn
    from scores
)
select
    case
       when grouping(c.rn) = 1 then 'Subtotal'
       else c.Name
    end as Name,
    sum(c.Score) as Score
from cte as c
group by grouping sets ((c.Name), (c.Name, c.rn))
order by c.Name;

-- OR

with cte as (
    select *, row_number() over(order by newid()) as rn
    from scores
)
select
    case
       when grouping(c.rn) = 1 then 'Subtotal'
       else c.Name
    end as Name,
    sum(c.Score) as Score
from cte as c
group by rollup(c.Name, c.rn)
having grouping(c.Name) = 0
order by c.Name;


oracle equivalent on the same scores table:

with cte as (
    select scores.*, row_number() over(order by 2) as rn
    from scores
)
select
    case
       when grouping(c.rn) = 1 then 'Subtotal'
       else c.Name
    end as Name,
    sum(c.Score) as Score
from cte c
group by grouping sets ((c.Name), (c.Name, c.rn))
order by c.Name

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
WHEN 'I' THEN 'Differential Database' 
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file' 
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential Partial'
ELSE msdb..backupset.type 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 2) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date desc

Links to articles:

Performance Troubleshooting Methodology