Uncategorized

Drop all foreign keys in database (WHILE version)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON GO DECLARE @SchemaName NVARCHAR(128) DECLARE @TableName NVARCHAR(128) DECLARE @ConstraintName NVARCHAR(128) DECLARE @Stmt NVARCHAR(MAX) DECLARE @FKsToDel TABLE ([SchemaName] NVARCHAR(128) NOT NULL, [TableName] NVARCHAR(128), [ConstraintName] NVARCHAR(128), PRIMARY KEY ([SchemaName], [TableName], [ConstraintName])) INSERT INTO @FKsToDel ( [SchemaName], [TableName], [ConstraintName] ) SELECT [s].[name], [t].[name], [c].[name]…

Read more
Uncategorized

Drop all tables in database (WHILE version)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON GO DECLARE @SchemaName NVARCHAR(128) DECLARE @TableName NVARCHAR(128) DECLARE @Stmt NVARCHAR(MAX) DECLARE @TablesToDel TABLE ([SchemaName] NVARCHAR(128) NOT NULL, [TableName] NVARCHAR(128), PRIMARY KEY ([SchemaName], [TableName])) INSERT INTO @TablesToDel ( [SchemaName], [TableName]) SELECT [s].[name], [t].[name] FROM [sys].[tables] AS [t] INNER JOIN [sys].[schemas] AS [s]…
Uncategorized

Drop all schemas in database (WHILE version)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON GO DECLARE @SchemaName NVARCHAR(128) DECLARE @Stmt NVARCHAR(MAX) DECLARE @SchemasToDel TABLE ([SchemaName] NVARCHAR(128) NOT NULL PRIMARY KEY) INSERT INTO @SchemasToDel ( [SchemaName] ) SELECT [s].[name] FROM [sys].[schemas] [s] WHERE [s].[name] IN (/*Fill list*/) WHILE EXISTS (SELECT * FROM @SchemasToDel) BEGIN SELECT TOP(1)…
Uncategorized

Switch database to single user mode

EXEC sp_dboption ‘MyDatabase’, ‘single user’, ‘TRUE’ EXEC sp_dboption — display list of all avaiable options EXEC sp_dboption ‘MyDatabase’ — display list of options set to 1 EXEC sp_dboption ‘MyDatabase’, ‘single user’ — display actual option setting Running sp_dboption creates checkpoint in database and new setting are applied immediately.

Read more
Uncategorized

Drop all tables in database

DECLARE @Stmt NVARCHAR(MAX) = ” SELECT @Stmt = @Stmt + ‘DROP TABLE [‘ + SCHEMA_NAME(t.schema_id) + ‘].[‘ + t.name + ‘];’ FROM sys.[tables] t WHERE t.[type] = ‘U’ EXECUTE(@Stmt) GO
Uncategorized

Compress all tables and indexes in database

— tables EXEC sp_MSForEachTable ‘ALTER TABLE ? REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)’ GO — indexes EXEC sp_MSForEachTable ‘ALTER INDEX ALL ON ? REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE )’ GO
Uncategorized

Kill all sessions using database

DECLARE @Stmt VARCHAR(1000) SET @Stmt = ” SELECT @Stmt = @Stmt + ‘KILL ‘ + CONVERT(NVARCHAR(3), spid) + ‘; ‘ FROM master.dbo.sysprocesses WHERE db_name(dbid) = ‘TestDb’ AND dbid <> 0 and spid <> @@spid EXECUTE(@Stmt) GO

Read more