Useful common sql commands for daily dba grind

--get the name of database files

select * from sysfiles

--C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\nwind_report.mdf
--C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\nwind_report_log.ldf

--get version and name of SQL Server you are running

select @@version
select @@servername
select serverproperty('INSTANCENAME')
select '\\' + @@servername
select '\\' + cast(serverproperty('servername')as varchar)

--Find active processes and related commands

sp_who2 active
kill 60
dbcc inputbuffer(61)

--one of favorites commands. used when log file runs out of space

backup log database with no_log

--some useful extended stored procedures

master..xp_fixeddrives
master..xp_readerrorlog

--old school system objects

select * from sysobjects
where type='u'
select * from sysdatabases
where name not in ('northwind', 'pubs', 'tempdb')

--text for a stored procedure

sp_helptext cs_Process_t_IPContact

--xp_cmdshell at work

master..xp_cmdshell 'dir c:\' --12GB
master..xp_cmdshell 'dir d:\' --12GB
master..xp_cmdshell 'dir data\dba'