DEV Community

Steve Mak
Steve Mak

Posted on • Edited on

MSSQL

#mssql #cheatsheet #rdbms

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

Top comments (0)