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 GO
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 SELECT ‘ALTER TABLE ‘ + QUOTENAME([s].[name]) + ‘.’ + QUOTENAME([t].[name]) + ‘ REBUILD PARTITION =…
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