Some SQL Tricks for MySQL and SQL Server that help you

Hello, In this section you can find some important SQL queries that really help you as a developer.

For MS SQL Server

Use Comma-separated values with IN clause

CREATE PROCEDURE [dbo].[sp_retriveByIds](@ids nvarchar(500))
As
BEGIN         
    EXECUTE (N'SELECT * FROM table_name Where id in (' + @ids + ')';
END 

Add new column with default value

ALTER TABLE table_name 
ADD column_name data_type NOT NULL 
CONSTRAINT constraint_name DEFAULT {default_value}
[WITH VALUES]

Retrieve list of table

SELECT * 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE='BASE TABLE'

Retrieve list of column from table

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='table_name'

Obtain only Date part from current date & time

SELECT CAST(GETDATE() AS DATE)

For MySQL

Create a new table from existing table

CREATE TABLE new_tbl [AS] 
SELECT * FROM orig_tbl;

Obtain only 10 Rows randomly from table

SELECT column FROM table
ORDER BY RAND()
LIMIT 10

Remove Duplicate Records

ALTER IGNORE TABLE table_name
ADD PRIMARY KEY(field_name);

Remove Duplicate Records

GRANT ALL PRIVILEGES ON database_name.* To 'user_name'@'host_name' 
IDENTIFIED BY 'password';

Reset Auto increment value

ALTER TABLE table_name 
AUTO_INCREMENT = 1

Leave a Reply

Your email address will not be published. Required fields are marked *