— SQL server version
SELECT @@version
output:
Microsoft SQL Server 2005 – 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

— Current logged in username
SELECT suser_sname()
Output:
sa

— Find owner name of owner_id (It can used to map the owner of a job, database..)
When owner_sid is passed it returns the username (SQL Authenticated or AD) used to retrieve owner of the job
Example: select suser_sname(s.owner_sid), s.* from msdb..sysjobs s
Output:
sa 10D1618A-A661-4219-BC17-070E7E47EA21 0 Job1….
domain\user E1FD7D83-7503-4069-939B-4E378056C395 0 Job2….

It can also to used to return database owner
select suser_sname(sd.sid), sd.* from sysdatabases sd
sa master
Domain\user TestDB

Returns users connected to DB whose name is not sa
select suser_sname(sp.sid), sp.* from sysprocesses sp
where suser_sname(sp.sid) != ‘sa’

— Client hostname
SELECT host_name()
Output:
myhostname

— Get Current date time on the server
SELECT getdate()
Output: 2010-01-31 13:20:11.113

— Row count of rows selected/updated/deleted/inserted
@@ROWCOUNT – Row count of rows selected, updated, deleted, inserted

— Global variable reflects level of nested transaction, each BEGIN TRANSACTION increases @@trancount by 1 and COMMIT decreases by 1. ROLLBACK roll backs everything
DECLARE @transactioncount int
SELECT @transactioncount = @@trancount

— servername
SELECT @@servername
Output
servername
————–
CAL-AMIN

Or
SERVERPROPERTY(‘MACHINENAME’)
Output
CAL-AMIN
— To use procedure/package
DECLARE @l_machine VARCHAR(20)
SET @l_machine = CAST(SERVERPROPERTY(‘MACHINENAME’) AS VARCHAR(20))