Create a database snapshot

Applied to SQL Server 2005 and later

CREATE DATABASE database_snapshot_name
(NAME = source_logical_filename,
FILENAME = operating_system_file)
AS SNAPSHOT OF source_database_name;

Get current connections

Sys.dm_exec_connections - Returns information about the connections made to the instance of the SQL Server and the details of each connection.

SELECT t1.session_id
, t2.login_name
FROM sys.dm_exec_connections t1
INNER JOIN sys.dm_exec_sessions t2 ON t1.session_id = t2.session_id

Get number of sessions established by every login

Applied to SQL Server 2005 or later

SELECT login_name [Login Name]
, COUNT(session_id) [Session Count]
FROM sys.dm_exec_sessions
GROUP BY login_name

Get table data as XML

Get Customer data as XML, return CustomerID as attribute and CustomerName as value

SELECT 1 AS Tag,
NULL AS Parent,
CustomerId AS [Customers!1!CustomerId],
CompanyName AS [Customers!1]
FROM Customers
FOR XML EXPLICIT

Get free disk space on server

EXEC xp_fixeddrives

Get space used in a table

EXEC sp_spaceused 'Your table name'

Get database file path and capacity

SQL Server 2000
SELECT * FROM sysfiles

SQL Server 2005 or later
SELECT * FROM sys.sysfiles

Get all triggers in a database

Applied to SQL Server 2005 or later

SELECT  t1.name [Trigger Name],
t3.name [Table Owner],
t2.name [Table Name],
CASE WHEN t1.is_disabled = 0 THEN 'Enabled'
  ELSE 'Disabled'END Status
FROM sys.triggers t1
INNER JOIN sys.tables t2 
  ON t1.parent_id = t2.object_id
INNER JOIN sys.schemas t3
  ON t2.schema_id = t3.schema_id

Find which tables contain a column name

Use the following query to get which tables contains EmployeeID as a column in a database.

SELECT t2.name [Table Name], t1.name [Column Name]
FROM syscolumns t1
INNER JOIN sysobjects t2 ON t1.id = t2.id
WHERE t1.name = 'EmployeeID'
AND t2.xtype = 'U'

Stored procedure tip

Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.

Get table data as XML

Get data in table as XML, return each column as element instead of attribute

SELECT CustomerID, CompanyName
FROM Customers
FOR XML AUTO, ELEMENTS

Copy a table

Copy CustomerName, CustomerID from Customer table

SELECT CustomerName, CustomerID INTO CustomerCopy
FROM Customer

Copy structure of Customer table
SELECT * INTO CustomerCopy
FROM Customer
WHERE 1 = 2

Export table to HTML format

Using system stored procedure sp_makewebtask to generate HTML file from a table.

EXEC sp_makewebtask @outputfile = 'C:\Temp\Customer.HTML',
@query='SELECT CompanyName, CustomerID FROM Customer ORDER BY
CompanyName'
, @lastupdated=0,@resultstitle=' '

Export table to HTML format

SELECT
'<TABLE BORDER="1">','<TH>Company Name</TH>','<TH>Customer ID</TH>',''
UNION ALL
SELECT '<TR>','<TD>'+CompanyName+'</TD>','<TD>'+CustomerId+'</TD>','</TR>'
FROM Customer
UNION ALL
SELECT
'</TABLE>','','',''

Get database endpoints

Applied to SQL Server 2005 or later

SELECT *
FROM sys.endpoints;

SELECT *
FROM sys.http_endpoints;

Get all tables & views in database

SELECT * FROM INFORMATION_SCHEMA.TABLES

Get objects, counters, and values installed

Shows the SQL Server objects, counters, and values installed on your server.

SELECT *
FROM sys.dm_os_performance_counters

Daily tasks should do

• Run backups (full, differential, filegroup, and log).
• Check services and instances to ensure they are running.
• Check SQL Server Logs (engine and SQL Agent).
• Check Windows event logs for SQL Server or SQL Server Agent entries.
• Verify backup and maintenance steps.

Backup database with password

BACKUP DATABASE YourDB
TO DISK = 'd:\temp\YourDB.bak'
WITH
   INIT
,   PASSWORD='YourPassword'
GO

Mirror a backup

BACKUP DATABASE YourDB
TO DISK = 'd:\temp\YourDB1.bak'
MIRROR TO DISK = 'd:\temp\YourDB22.bak'
WITH
FORMAT
GO

Create a backup device

EXEC master.dbo.sp_addumpdevice @devtype = N'disk'
, @logicalname = N'MyDailyBackup'
, @physicalname = N'D:\TEMP\SQL2K5Backup.bak'
GO

Check database exists or not before delete it

Be very careful before running this query!

IF EXISTS (
  SELECT name
        FROM sys.databases
        WHERE name = N'YourDBName'
)
DROP DATABASE YourDBName
GO

Split a string into a table

CREATE FUNCTION [fnSplit]
(@sInputList VARCHAR(MAX)
, @sDelimiter VARCHAR(10) = ',')
RETURNS @List TABLE (item NVARCHAR(4000))
BEGIN
DECLARE @sItem NVARCHAR(8000)
WHILE CHARINDEX(@sDelimiter, @sInputList, 0) <> 0
BEGIN
SELECT 

@sItem = RTRIM(LTRIM(SUBSTRING(@sInputList, 1, CHARINDEX(@sDelimiter, @sInputList, 0) - 1))),
@sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList, CHARINDEX(@sDelimiter, @sInputList,0) + LEN(@sDelimiter), LEN(@sInputList))))

  IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList
RETURN
END

Get all employees have birthday this week

SELECT EmployeeID, EmployeeName, Email, BirthDate
FROM Employee
WHERE DATEDIFF(w, BirthDate, GETDATE()) = 0

Get top 10 customers by revenue

Using TOP keyword.

SELECT TOP 10 CustomerID, CustomerName, Revenue
FROM dbo.Customer
ORDER BY Revenue DESC

Get all customers established this month

SELECT CustomerID, CustomerName, EstablishedDate, Email, Address
FROM dbo.Customer
WHERE DATEDIFF(m, EstablishedDate, GETDATE()) = 0

Try/Catch in T-SQL

Applied from SQL Server 2005

BEGIN TRY
 BEGIN TRAN
 --Your T-SQL code here
 COMMIT TRAN
END TRY
BEGIN CATCH
 DECLARE @ErrorMessage NVARCHAR(4000);
 SELECT @ErrorMessage = ERROR_MESSAGE();
 ROLLBACK TRAN;
 RAISERROR(@ErrorMessage, 16, 1);
END CATCH

Using cursor

DECLARE Cur CURSOR FOR
 SELECT AccountID, AccountName
 FROM Account

OPEN Cur
FETCH FROM Cur
INTO @AccountID, @AccountName

WHILE @@FETCH_STATUS = 0
BEGIN
 --Do your tasks here
 FETCH FROM Cur
 INTO @AccountID, @AcountName
END

CLOSE Cur
DEALLOCATE Cur

A six-part series on getting the most out of SharePoint Designer 2007

As a DBA, I think you also need some knowledge about SharePoint and SharePoint Designer so that you can use it to modify your SharePoint site when necessary. You can read this article for more detail A six-part series on getting the most out of SharePoint Designer 2007.

Delete all foreign keys in a database

DECLARE @Table VARCHAR(255)
DECLARE @ForeignKey VARCHAR(255)
DECLARE @STRSQL VARCHAR(8000)

DECLARE Cur CURSOR FOR   
 select t1.name as [Table], t2.name as [ForeignKey]
 from sysobjects t1 inner join
  sysobjects t2 on t1.id = t2.parent_obj
 where t1.xtype = 'U' and t2.xtype = 'F'
OPEN Cur
FETCH FROM Cur
INTO @Table, @ForeignKey
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @STRSQL = 'ALTER TABLE ' + @Table +
   ' DROP CONSTRAINT ' + @ForeignKey
 EXECUTE(@STRSQL)

 FETCH FROM Cur
 INTO @Table, @ForeignKey
END
CLOSE Cur
DEALLOCATE Cur

Get random record

SELECT TOP 1 *
FROM YourTable
ORDER BY NEWID()