# sqlcmd (Windows/Linux)
sqlcmd -S <server> -U <user> -P '<password>'
sqlcmd -S <server> -E # Windows auth
# From Linux
sqsh -S <server> -U <user> -P '<password>'
mssqlclient.py <user>@<server> -p 1433
-- List databases
SELECT name FROM sys.databases;
-- Use database
USE <database_name>;
-- Create database
CREATE DATABASE mydb;
-- Drop database
DROP DATABASE mydb;
-- Current database
SELECT DB_NAME();
-- List tables
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
SELECT name FROM sys.tables;
-- Describe table structure
EXEC sp_columns @table_name = 'TableName';
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName';
-- Create table
CREATE TABLE users (
id INT PRIMARY KEY IDENTITY(1,1),
username NVARCHAR(50) NOT NULL,
email NVARCHAR(100),
created_at DATETIME DEFAULT GETDATE()
);
-- Drop table
DROP TABLE users;
-- Truncate (delete all rows, reset identity)
TRUNCATE TABLE users;
-- Insert
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');
INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com');
-- Select
SELECT * FROM users;
SELECT TOP 10 * FROM users;
SELECT username, email FROM users WHERE id > 5;
SELECT DISTINCT username FROM users;
-- Update
UPDATE users SET email = 'new@example.com' WHERE username = 'john';
-- Delete
DELETE FROM users WHERE id = 5;
-- WHERE clauses
WHERE column = 'value'
WHERE column <> 'value'
WHERE column IN ('a', 'b', 'c')
WHERE column NOT IN ('a', 'b')
WHERE column BETWEEN 1 AND 100
WHERE column LIKE 'prefix%'
WHERE column LIKE '%suffix'
WHERE column LIKE '%contains%'
WHERE column IS NULL
WHERE column IS NOT NULL
-- Sorting
ORDER BY column ASC
ORDER BY column DESC
ORDER BY col1, col2 DESC
-- Pagination
SELECT * FROM users ORDER BY id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
-- Inner join
SELECT u.username, o.order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- Left join (all from left, matching from right)
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Right join
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- Full outer join
SELECT u.username, o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- Cross join (cartesian product)
SELECT * FROM table1 CROSS JOIN table2;
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT username) FROM users;
SELECT SUM(amount) FROM orders;
SELECT AVG(amount) FROM orders;
SELECT MIN(amount), MAX(amount) FROM orders;
-- Group by
SELECT status, COUNT(*) as count
FROM orders
GROUP BY status;
-- Having (filter after grouping)
SELECT status, COUNT(*) as count
FROM orders
GROUP BY status
HAVING COUNT(*) > 10;
LEN('string') -- Length
UPPER('string') -- Uppercase
LOWER('STRING') -- Lowercase
LTRIM(' string') -- Trim left
RTRIM('string ') -- Trim right
TRIM(' string ') -- Trim both (SQL Server 2017+)
SUBSTRING('string', 1, 3) -- Extract substring (1-indexed)
LEFT('string', 3) -- First n chars
RIGHT('string', 3) -- Last n chars
REPLACE('string', 'i', 'o') -- Replace
CHARINDEX('r', 'string') -- Find position
CONCAT('a', 'b', 'c') -- Concatenate
STUFF('string', 2, 3, 'X') -- Replace at position
GETDATE() -- Current datetime
GETUTCDATE() -- Current UTC datetime
SYSDATETIME() -- Higher precision datetime
DATEADD(day, 7, GETDATE()) -- Add interval
DATEDIFF(day, date1, date2) -- Difference between dates
DATEPART(year, GETDATE()) -- Extract part
YEAR(date), MONTH(date), DAY(date) -- Extract components
FORMAT(GETDATE(), 'yyyy-MM-dd') -- Format date
CONVERT(DATE, GETDATE()) -- Convert to date only
CAST('2024-01-01' AS DATE) -- Cast string to date
-- Variables
DECLARE @name NVARCHAR(50) = 'value';
DECLARE @count INT;
SET @count = 10;
SELECT @count = COUNT(*) FROM users;
-- If/Else
IF @count > 0
BEGIN
PRINT 'Has records';
END
ELSE
BEGIN
PRINT 'No records';
END
-- Case
SELECT username,
CASE
WHEN status = 1 THEN 'Active'
WHEN status = 0 THEN 'Inactive'
ELSE 'Unknown'
END AS status_text
FROM users;
-- While loop
DECLARE @i INT = 0;
WHILE @i < 10
BEGIN
PRINT @i;
SET @i = @i + 1;
END
-- Create procedure
CREATE PROCEDURE GetUserById
@UserId INT
AS
BEGIN
SELECT * FROM users WHERE id = @UserId;
END;
-- Execute
EXEC GetUserById @UserId = 5;
-- With output parameter
CREATE PROCEDURE GetUserCount
@Count INT OUTPUT
AS
BEGIN
SELECT @Count = COUNT(*) FROM users;
END;
DECLARE @result INT;
EXEC GetUserCount @Count = @result OUTPUT;
PRINT @result;
-- Drop procedure
DROP PROCEDURE GetUserById;
WITH ActiveUsers AS (
SELECT * FROM users WHERE status = 1
)
SELECT * FROM ActiveUsers WHERE created_at > '2024-01-01';
-- Recursive CTE
WITH Numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Numbers WHERE n < 10
)
SELECT * FROM Numbers;
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Check and commit or rollback
IF @@ERROR = 0
COMMIT;
ELSE
ROLLBACK;
-- Try/Catch
BEGIN TRY
BEGIN TRANSACTION;
-- statements
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK;
PRINT ERROR_MESSAGE();
END CATCH;
-- Create index
CREATE INDEX idx_username ON users(username);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_composite ON users(lastname, firstname);
-- Drop index
DROP INDEX idx_username ON users;
-- Show indexes
EXEC sp_helpindex 'users';
-- Create view
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE status = 1;
-- Query view
SELECT * FROM active_users;
-- Drop view
DROP VIEW active_users;
-- Create login (server level)
CREATE LOGIN mylogin WITH PASSWORD = 'SecurePass123!';
-- Create user (database level)
CREATE USER myuser FOR LOGIN mylogin;
-- Grant permissions
GRANT SELECT ON users TO myuser;
GRANT SELECT, INSERT, UPDATE ON users TO myuser;
GRANT EXECUTE ON GetUserById TO myuser;
-- Revoke permissions
REVOKE SELECT ON users FROM myuser;
-- Add to role
ALTER ROLE db_datareader ADD MEMBER myuser;
-- Check current user
SELECT SYSTEM_USER; -- Login name
SELECT USER_NAME(); -- Database user
-- Check role membership
SELECT IS_SRVROLEMEMBER('sysadmin');
SELECT IS_MEMBER('db_owner');
-- Server info
SELECT @@VERSION;
SELECT @@SERVERNAME;
SELECT SERVERPROPERTY('ProductVersion');
-- Current session
SELECT @@SPID; -- Session ID
SELECT SUSER_SNAME(); -- Login name
SELECT DB_NAME(); -- Current database
-- List all logins
SELECT name, type_desc FROM sys.server_principals;
-- List database users
SELECT name, type_desc FROM sys.database_principals;
-- Linked servers
SELECT * FROM sys.servers;
EXEC sp_databases; -- List databases
EXEC sp_tables; -- List tables
EXEC sp_columns 'TableName'; -- Table columns
EXEC sp_helptext 'ProcName'; -- View proc definition
EXEC sp_who2; -- Active connections
EXEC sp_configure; -- Server configuration
select @@ServerName [Server Name], DB_NAME() [DB Name], u.name [DB Role], u2.name [Member Name]
from sys.database_role_members m
join sys.database_principals u on m.role_principal_id = u.principal_id
join sys.database_principals u2 on m.member_principal_id = u2.principal_id
where u.name = 'db_owner'
order by [Member Name]
#in user database run the command
SELECT name, sid FROM sys.sysusers where name = 'dbo' .
#in master database run the command
SELECT name, sid FROM sys.sql_logins