DEV Community

Steve Mak
Steve Mak

Posted on • Edited on

1 1

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

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay