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]…
Drop all tables in database (WHILE version)
18. 6. 2019
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]…
Drop all schemas in database (WHILE version)
18. 6. 2019
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)…
Switch database to single user mode
9. 3. 2018
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.
Drop all tables in database
9. 3. 2018
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
— 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
Kill all sessions using database
9. 3. 2018
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