Delete and return deleted rows

Applied to SQL Server 2005 or later.

CREATE TABLE dbo.tblTest(a int, b int)
INSERT INTO dbo.tblTest(a,b)
SELECT 1,2
UNION SELECT 3,4
UNION SELECT 4,5

DELETE dbo.tblTest OUTPUT DELETED.*
WHERE a > 1

Checking table before dropping

Be careful when using this script in your production server!

IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'YourTable')
DROP TABLE YourTable;

Import Google map KML file into a table

Applied to SQL Server 2005 or later
Define MapKML as XML data type in table tblMap

DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'D:\GoogleMaps\YourMap.kml'
EXEC('
INSERT INTO tblMap(MapName, MapKML)
SELECT ''Your map'', xmlData
FROM
(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')

Get Oracle db version

SELECT * FROM v$version

Change User Name/Password

Change a user name to new name
ALTER LOGIN OldUserName
WITH NAME = NewUserName;

Change user password
ALTER LOGIN YourUserName
WITH PASSWORD = 'YourNewPassword';

CREATE LOGIN

The following script creates a login for a particular user and assigns a password. You use MUST_CHANGE option to require this user to change their password the first time they connect to the server.

CREATE LOGIN YourLoginName
WITH PASSWORD = 'YourLoginPassword' MUST_CHANGE

Search with CONTAINS

The following script returns all products which their name contains strings with prefixes of either "chain" or "full".
You must set up full text search for Product table.

SELECT ProductID, ProductName, Price
FROM Product
WHERE CONTAINS(ProductName, '"chain*" OR "full*"');

Search with FreeText

Search employees with their description contains 'hard' or 'working'
You must set up full text search for Employee table

SELECT EmployeeID, EmployeeName, BirthDate
FROM Employee
WHERE FREETEXT(EmployeeDescription, 'hard working');

Check database back up set capacity

Sometimes you need to check your back up files to do some maintenance actions for it, here's the script you can use.

SELECT CONVERT(VARCHAR(10), backup_start_date, 111) [Backup Date]
, backup_size/1024000 [Size In MB]
FROM msdb..backupset
WHERE database_name = 'Your DB Name'
and type = 'd'
ORDER BY backup_start_date DESC
COMPUTE SUM(backup_size/1024000)

Get database columns by using CTE

The following script uses Common Table Expression to get all columns in the database.
Applied to SQL Server 2005 or later

WITH DBColumns
AS
(
   SELECT table_name, column_name
   FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name
FROM DBColumns