DEV Community

Steve Mak
Steve Mak

Posted on • Updated on

Cheatsheet for MSSQL

MSSQL GUI Management Tool

Windows

  • SQL Server Management Studio (SSMS)

Mac

  • Azure Data Studio

Connection String


Disable all constraints

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
Enter fullscreen mode Exit fullscreen mode

Enable all constraints

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

Drop database

USE master;
ALTER DATABASE [database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [database];
Enter fullscreen mode Exit fullscreen mode

Create login

CREATE LOGIN [login_name] WITH PASSWORD = '[login_password]';
Enter fullscreen mode Exit fullscreen mode

Create user

CREATE USER [user_name] for login [login_name]
Enter fullscreen mode Exit fullscreen mode

Grant privilege

GRANT SELECT, INSERT, UPDATE, DELETE on [schema.object] to [user_name];
Enter fullscreen mode Exit fullscreen mode

Get existing MSSQL connections

SELECT * FROM sys.dm_exec_sessions WHERE status = 'running';
OR
EXEC sp_who;
Enter fullscreen mode Exit fullscreen mode

Take a table lock in transaction

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

Take a row lock in transaction

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

Discussion (0)