Various useful small SQLs

Find the edition

select serverproperty('Edition')

Who are you connected as?

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

Show current time

select getdate()

Show current server

select @@servername

Show current version

select @@version

or even better:

xp_msver

Google the results to see what numbers mean

Find out current service pack

SELECT SERVERPROPERTY('ProductLevel')

Sizing

EXEC sp_msforeachdb @command1="use [?] exec sp_spaceused"

EXEC sp_spaceused

EXEC sp_spaceused table_name

List Backups in the last 30 days

SELECT s.database_name, S.backup_start_date, S.type,
M.physical_device_name
FROM msdb..backupset S
JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE DATEDIFF(DAY,S.backup_start_date,GETDATE()) <>

Auto close and auto shrink

SET NOCOUNT ON

SELECT [name] AS DatabaseName^?
, CONVERT(varchar(100),DATABASEPROPERTYEX([Name] , 'Recovery')) AS RecoveryType^?
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoClose')) AS AutoClose^?
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoShrink')) AS AutoShrink^?
FROM master.dbo.sysdatabases Order By DatabaseName^?

To create the shink log commands for ALL databases in FULL recovery mode:

SELECT ' use '+name +
char(10) + char(13)+ 'go ' + char(10) + char(13)+
'dbcc shrinkfile ('+name+',0,truncateonly)'+
char(10) + char(13)+ 'go ' + char(10) + char(13)+
FROM master.dbo.sysdatabases
where CONVERT(varchar(100),DATABASEPROPERTYEX([Name],'Recovery'))='FULL'

Check the last statistics update

SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(o.id,i.id)
FROM sysobjects AS o WITH (NOLOCK)
JOIN sysindexes AS i WITH (NOLOCK)
ON object_id(o.name) = i.id
ORDER BY STATS_DATE(o.id, i.id) desc

Check the instance parameters

select * from sys.configurations order by name

List database objects

SELECT * FROM sysobjects WHERE TYPE = 'P'

SELECT name, type FROM sysobjects WHERE id={number}

List all Tables

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE

FROM INFORMATION_SCHEMA.TABLES

ORDERBY TABLE_SCHEMA, TABLE_NAME

Execute SQL command for each table

EXEC sp_MSForEachTable 'ALTER TABLE ? ETC ETC'

To release the deallocated space

DBCC UPDATEUSAGE ('dbname')

Fix orphaned users

exec sp_change_users_login 'Report'

CREATE LOGIN [abclogin] WITH PASSWORD=N'passw0rd', DEFAULT_DATABASE=[DBname], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE DBName

GO

sp_change_users_login 'update_one', 'youruser', 'abclogin

Log Shipping

select * from log_shipping_monitor_primary

select * from log_shipping_monitor_secondary

select * from log_shipping_monitor_history_detail