MSSQL

MSSQL GUI Management Tool

Windows

• SQL Server Management Studio (SSMS)

Mac

• Azure Data Studio

---

Connection String

• http://www.devlist.com/ConnectionStringsPage.aspx

---

Disable all constraints

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

Enable all constraints

EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Drop database

USE master;
ALTER DATABASE [database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [database];

Create login

CREATE LOGIN [login_name] WITH PASSWORD = '[login_password]';

Create user

CREATE USER [user_name] for login [login_name]

Grant privilege

GRANT SELECT, INSERT, UPDATE, DELETE on [schema.object] to [user_name];

Get existing MSSQL connections

SELECT * FROM sys.dm_exec_sessions WHERE status = 'running';
OR
EXEC sp_who;

Take a table lock in transaction

BEGIN TRAN  
SELECT 1 FROM dbo.Members WITH (TABLOCKX)
WAITFOR DELAY '00:00:30' 
ROLLBACK TRAN   
GO 

Take a row lock in transaction

SELECT * FROM dbo.Members WITH (ROWLOCK, UPDLOCK)
WHERE ID = 2