Friday, November 27, 2009

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

Thursday, November 26, 2009

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

Saturday, November 21, 2009

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

Tuesday, November 17, 2009

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>','','',''

Monday, November 16, 2009

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

Sunday, November 15, 2009

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

Saturday, November 14, 2009

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

Friday, November 13, 2009

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.

Monday, November 9, 2009

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()

Oracle Query