Get all weekends in year 2010

DECLARE @FirstDateOfYear DATETIME
SET @FirstDateOfYear = ‘2010-01-01′
SELECT DISTINCT DATEADD(d, number, @FirstDateOfYear),
CASE DATEPART(dw, DATEADD(d, number, @FirstDateOfYear))
WHEN 7 THEN ‘Saturday’
WHEN 1 THEN ‘Sunday’
ELSE ‘Work Day’
END
FROM master..spt_values
WHERE number BETWEEN 0 AND 364
AND (DATEPART(dw, DATEADD(d, number, @FirstDateOfYear)) = 1 OR DATEPART(dw, DATEADD(d, number, @FirstDateOfYear)) = 7)
ORDER BY DATEADD(d, number, @FirstDateOfYear)

This is the script I replied the article Get Date of All Weekdays or Weekends of the Year on SQLAuthority.com

Add multiple columns to a table in MySQL

ALTER TABLE customer
ADD customer_description varchar(255),
ADD established_date datetime,
ADD salesforce_id varchar(18)

It's a bit different with SQL Server, we need to use ADD for each column.

Add a column with comment and default value

Add status column to tblCustomer table with comment and default value is 'I'

ALTER TABLE tblCustomer
ADD status CHAR(1) ASCII DEFAULT 'I' COMMENT 'A: Active, I: Inactive';

Generate token procedure

CREATE PROCEDURE GenerateToken(v_email varchar(255))
BEGIN
 DECLARE i_count INT;
 SELECT COUNT(email) INTO i_count
 FROM tbToken WHERE email = v_email;
 
 IF (i_count > 0) THEN
  UPDATE tblToken
  SET token = UUID(),
    created_date = NOW()
  WHERE email = v_email;
 ELSE
  INSERT INTO tblToken(email, token, created_date)
  VALUES(v_email, UUID(), NOW());
 END IF;
END;

Generate a token

Use UUID() function to generate a token or a GUID.

SELECT UUID();

Create linked server to MySQL

Follow these steps:
- Download MySQL Connector and install it in your server
- Use ODBC to create a System DSN name, such as: SystemDSNName to connect to your MySQL db by using MySQL ODBC Driver
- Configure MSDASQL Provider
- Run this script to create linked server to your MySQL db
EXEC master.dbo.sp_addlinkedserver @server = N'YourLinkedServerName', @srvproduct=N'SystemDSNName', @provider=N'MSDASQL', @datasrc=N'YourSystemDSNName'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'YourLinkedServerName', @useself=N'False', @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL

Get MySQL version

SELECT VERSION();

Create User on local host

CREATE USER 'UserName'@'localhost'
IDENTIFIED BY 'UserPassword'

Find SQL Server logins using their names as passwords

Applied to SQL Server 2005 or later

SELECT name, name As [Pwd]
FROM sys.sql_logins
WHERE PWDCOMPARE(name, password_hash) = 1
OR PWDCOMPARE(name,password_hash,1) = 1

Using SEQUENCE in Oracle

CREATE SEQUENCE CustomerSeq
INCREMENT BY 1
START WITH 1

INSERT INTO Customer(CustomerID, CustomerName)
VALUES(CustomerSeq.NEXTVAL, 'Google Inc.')

Import data from a text file to a table

File Test.txt contain 3 rows
1,2
3,4
5,6
We can import these rows into a table by using these scripts.
CREATE TABLE dbo.tblTest(a int, b int)
GO
BULK INSERT dbo.tblTest
FROM 'D:\Temp\Test.txt'
WITH(FIELDTERMINATOR =',',
ROWTERMINATOR ='\n')

Convert male into female and vice versa

tblEmployee(EmployeeID, EmployeeName, Gender, etc.)
Gender contains two values only, 'M' represents for 'Male' and 'F' represent for 'Female'.
For some reason, after importing employee data, you found that Gender field values have been reversed and you need to convert them.
UPDATE dbo.tblEmployee
SET Gender = t2.Gender
FROM dbo.tblEmployee t1
INNER JOIN (SELECT EmployeeID,
CASE Gender
WHEN 'M' THEN 'F'
WHEN 'F' THEN 'M'
END AS Gender
FROM dbo.tblEmployee) t2 ON t1.EmployeeID = t2.EmployeeID

Get 365 days in 2010 with one query

Use undocumented spt_values in master database and DATEADD() function

SELECT DATEADD(d, number, '2010-01-01')
FROM master..spt_values
WHERE type = 'P'
AND number < 365

Truncate database log file

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE YourDBName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (YourDBName_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE YourDBName
SET RECOVERY FULL;

Top 5 DBA tasks that are a waste of time (and might be hurting your SQL Server)

Reading: Top 5 DBA tasks that are a waste of time (and might be hurting your SQL Server)

Truncate table and keep blank space

Use this script to truncate your table but keep the blank space for INSERT or UPDATE new data.
TRUNCATE TABLE YourTable REUSE STORAGE;
Default of TRUNCATE TABLE is DROP STORAGE

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