Last Backup of all the databases

select b.backup_finish_date,b.backup_start_date,type,b.backup_size,b.database_name,m.physical_device_name,
datediff (day, b.backup_start_date, getdate ()) datediff
from msdb..backupset b,msdb..backupmediafamily m
where b.media_set_id= m.media_set_id AND b.backup_start_date =
(SELECT MAX (backup_start_date) FROM msdb..backupset WHERE database_name = b.database_name AND type = 'D')
ORDER BY b.database_name

Find Last statistics updates on tables

SELECT OBJECT_NAME(A.object_id) AS Object_Name, A.name AS index_name, STATS_DATE(A.OBJECT_ID, index_id) AS StatsUpdated ,
DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DaysOld
FROM sys.indexes A
INNER JOIN sys.tables B ON A.object_id = B.object_id
WHERE A.name IS NOT NULL
ORDER BY DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DESC

USE AdventureWorksGO
SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('HumanResources.Department')

sp_change_users_login

sp_change_users_login [ @Action = ] 'action'
[ , [ @UserNamePattern = ] 'user' ]
[ , [ @LoginName = ] 'login' ]
[ , [ @Password = ] 'password' ]

This example produces a report of the users in the current database and their security identifiers.
EXEC sp_change_users_login 'Report'


--Change the user account to link with the 'Login1' login.
USE DB1
go
EXEC sp_change_users_login 'Update_One', 'OrphanUser', 'Login1'

This example shows how to use the Auto_Fix option to map an existing user to a login with the same name, or create the SQL Server login 'Login1' with the password '@a122ld' if the login Mary does not exist.
USE pubs
go
EXEC sp_change_users_login 'Auto_Fix', 'Login1' , NULL,a122ld'
go

Defaut Trace

SELECT * FROM sys.configurations WHERE configuration_id = 1568 --(check if trace option is set)
SELECT * FROM ::fn_trace_getinfo(0)

SELECT loginname, loginsid, spid, hostname, applicationname, servername, databasename, objectName,
e.category_id, cat.name AS [CategoryName], textdata, starttime, eventclass, eventsubclass,
--0=begin,1=commit e.name AS EventName FROM
::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)
INNER JOIN sys.trace_events e ON eventclass = trace_event_id INNER JOIN
sys.trace_categories AS cat ON e.category_id = cat.category_id WHERE
databasename = 'db' AND objectname IS NULL AND --filter by objectname
e.category_id = 5 AND
--category 5 is objects
e.trace_event_id = 46 --trace_event_id: 46=Create Obj,47=Drop Obj,164=Alter Obj


SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)

Search Object, Strings in database

CREATE FUNCTION [dbo].[FindStringTableDependency](@StrText varchar(500),@tableStrInd varchar(10))
RETURNS @tmpDepend TABLE (id varchar(100),Type varchar(10))
BEGIN
IF @tableStrInd <> 'T'
INSERT INTO @tmpDepend
SELECT DISTINCT name,xtype FROM sysobjects s INNER JOIN syscomments s2
ON s.id = s2.id WHERE s2.text LIKE '%'+ @strText + '%'
ELSE
INSERT INTO @tmpDepend
SELECT DISTINCT name,'T' FROM sysobjects WHERE id IN (
SELECT DISTINCT s.id FROM sysdepends s ,sysobjects o WHERE
s.depid = o.id AND o.name = '' + @StrText + '')

RETURN
END

Table size and heaviest table

-- sp_spaceused pay_detl

DECLARE @id INT
DECLARE @type character(2)
DECLARE @pages INT
DECLARE @dbname SYSNAME
DECLARE @dbsize dec(15, 0)
DECLARE @bytesperpage dec(15, 0)
DECLARE @pagesperMB dec(15, 0)

CREATE TABLE #spt_space
(
objid INT NULL,ROWS INT NULL,reserved dec(15) NULL,
DATA dec(15) NULL,indexp dec(15) NULL,unused dec(15) NULL
)

SET NOCOUNT ON

-- Create a cursor to loop through the user tables
DECLARE c_tables CURSOR
FOR
SELECT id FROM sysobjects WHERE xtype = 'U'
OPEN c_tables

FETCH NEXT FROM c_tables
INTO @id

WHILE @@fetch_status = 0
BEGIN
/* Code from sp_spaceused */
INSERT INTO #spt_space
(
objid, reserved
)
SELECT objid = @id, SUM(reserved) FROM sysindexes
WHERE indid IN (0, 1, 255) AND id = @id

SELECT @pages = SUM(dpages) FROM sysindexes
WHERE indid < 2 AND id = @id

SELECT @pages = @pages + ISNULL(SUM(USED), 0)
FROM sysindexes WHERE indid = 255 AND id = @id

UPDATE #spt_space SET DATA = @pages WHERE objid = @id


/* index: sum(used) where indid in (0, 1, 255) - data */
UPDATE #spt_space
SET indexp = (
SELECT SUM(USED) FROM sysindexes WHERE indid IN (0, 1, 255) AND id = @id
) - DATA
WHERE objid = @id

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
UPDATE #spt_space
SET unused = reserved
-(
SELECT SUM(USED) FROM sysindexes WHERE indid IN (0, 1, 255)
AND id = @id
)
WHERE objid = @id

UPDATE #spt_space SET ROWS = i.rows
FROM sysindexes i WHERE i.indid < 2
AND i.id = @id AND objid = @id

FETCH NEXT FROM c_tables
INTO @id
END

SELECT TOP 25 Table_Name = (SELECT LEFT(NAME, 25) FROM sysobjects WHERE id = objid ),
ROWS = CONVERT(CHAR(11), ROWS),reserved_KB = LTRIM(STR(reserved * d.low / 1024., 15, 0) + ' ' + 'KB'),
data_KB = LTRIM(STR(DATA * d.low / 1024., 15, 0) + ' ' + 'KB'),
index_size_KB = LTRIM(STR(indexp * d.low / 1024., 15, 0) + ' ' + 'KB'),
unused_KB = LTRIM(STR(unused * d.low / 1024., 15, 0) + ' ' + 'KB')
FROM #spt_space, MASTER.dbo.spt_values d
WHERE d.number = 1 AND d.type = 'E'
ORDER BY reserved DESC

DROP TABLE #spt_space
CLOSE c_tables
DEALLOCATE c_tables

Wait Search Query

SELECT
ws.wait_type,
ws.waiting_tasks_count,
CASE WHEN ws.waiting_tasks_count = 0 THEN 0 ELSE ws.wait_time_ms / ws.waiting_tasks_count END as average_wait_time_ms,
ws.wait_time_ms as total_wait_time_ms,
CONVERT(DECIMAL(12,2), ws.wait_time_ms * 100.0 / SUM(ws.wait_time_ms) OVER()) as wait_time_proportion,
ws.wait_time_ms - signal_wait_time_ms as total_wait_ex_signal_time_ms,
ws.max_wait_time_ms,
ws.signal_wait_time_ms as total_signal_wait_time_ms
FROM
sys.dm_os_wait_stats ws
WHERE
-- Restrict results to requests that have actually occured.
ws.waiting_tasks_count > 0
ORDER BY
ws.wait_type