Uncategorized

Drop all foreign keys in database (WHILE version)

Transact-SQL 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] FROM [sys].[objects] AS [c] INNER JOIN [sys].[tables] AS [t] ON [c].[parent_object_id] = [t].[object_id] INNER JOIN [sys].[schemas] AS [s] ON [t].[schema_id] = [s].[schema_id] WHERE [c].[type] IN ('F') AND [s].[name] IN (/*Fill list*/) WHILE EXISTS (SELECT * FROM @FKsToDel) BEGIN SELECT TOP(1) @SchemaName = [SchemaName], @TableName = [TableName], @ConstraintName = [ConstraintName] FROM @FKsToDel ORDER BY [SchemaName], [TableName], [ConstraintName] PRINT 'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT ' + QUOTENAME(@ConstraintName) + ';' + CHAR(13) + 'GO' DELETE FROM @FKsToDel WHERE [SchemaName] = @SchemaName AND [TableName] = @TableName AND [ConstraintName] = @ConstraintName END GO 1234567891011121314151617181920212223242526272829303132333435363738 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET NOCOUNT ONGO 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]FROM [sys].[objects] AS [c]INNER JOIN [sys].[tables] AS [t] ON [c].[parent_object_id] = [t].[object_id]INNER JOIN [sys].[schemas] AS [s] ON [t].[schema_id] = [s].[schema_id]WHERE [c].[type] IN ('F') AND [s].[name] IN (/*Fill list*/) WHILE EXISTS (SELECT * FROM @FKsToDel)BEGIN SELECT TOP(1)@SchemaName = [SchemaName],@TableName = [TableName],@ConstraintName = [ConstraintName]FROM @FKsToDelORDER BY [SchemaName], [TableName], [ConstraintName] PRINT 'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) +N' DROP CONSTRAINT ' + QUOTENAME(@ConstraintName) + ';' + CHAR(13) + 'GO' DELETE FROM @FKsToDel WHERE [SchemaName] = @SchemaName AND [TableName] = @TableName AND [ConstraintName] = @ConstraintName ENDGO  
Uncategorized

Drop all tables in database (WHILE version)

Transact-SQL 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] ON [t].[schema_id] = [s].[schema_id] WHERE [t].[type] IN ('U') AND [s].[name] IN (/*Fill list*/) WHILE EXISTS (SELECT * FROM @TablesToDel) BEGIN SELECT TOP(1) @SchemaName = [SchemaName], @TableName = [TableName] FROM @TablesToDel [td] ORDER BY [SchemaName], [td].[TableName] PRINT 'DROP TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + ';' + CHAR(13) + 'GO' DELETE FROM @TablesToDel WHERE [SchemaName] = @SchemaName AND [TableName] = @TableName END GO 12345678910111213141516171819202122232425262728293031323334 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET NOCOUNT ONGO 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]  ON [t].[schema_id] = [s].[schema_id]WHERE [t].[type] IN ('U') AND [s].[name] IN (/*Fill list*/) WHILE EXISTS (SELECT * FROM @TablesToDel)BEGIN     SELECT TOP(1) @SchemaName = [SchemaName], @TableName = [TableName] FROM @TablesToDel [td] ORDER BY [SchemaName], [td].[TableName]  PRINT 'DROP TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + ';' + CHAR(13) + 'GO'  DELETE FROM @TablesToDel WHERE [SchemaName] = @SchemaName AND [TableName] = @TableName ENDGO  
Uncategorized

Drop all schemas in database (WHILE version)

Transact-SQL 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) @SchemaName = [SchemaName] FROM @SchemasToDel ORDER BY [SchemaName] PRINT 'DROP SCHEMA ' + QUOTENAME(@SchemaName) + ';' + CHAR(13) + 'GO' DELETE FROM @SchemasToDel WHERE [SchemaName] = @SchemaName END GO 1234567891011121314151617181920212223242526272829 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET NOCOUNT ONGO 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) @SchemaName = [SchemaName] FROM @SchemasToDel ORDER BY [SchemaName]  PRINT 'DROP SCHEMA ' + QUOTENAME(@SchemaName) + ';' + CHAR(13) + 'GO'  DELETE FROM @SchemasToDel WHERE [SchemaName] = @SchemaName ENDGO  
Uncategorized

Switch database to single user mode

Transact-SQL 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. 123456 EXEC sp_dboption 'MyDatabase', 'single user', 'TRUE'EXEC sp_dboption -- display list of all avaiable optionsEXEC sp_dboption 'MyDatabase' -- display list of options set to 1EXEC sp_dboption 'MyDatabase', 'single user' -- display actual option setting Running sp_dboption creates checkpoint in database and new setting are applied immediately.
Uncategorized

Drop all tables in database

Transact-SQL 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 123456789 DECLARE @Stmt NVARCHAR(MAX) = '' SELECT @Stmt = @Stmt + 'DROP TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '];'FROM sys.[tables] tWHERE t.[type] = 'U' EXECUTE(@Stmt)GO
Uncategorized

Compress all tables and indexes in database

Transact-SQL -- 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 1234567 -- tablesEXEC sp_MSForEachTable 'ALTER TABLE ? REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'GO -- indexesEXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE )'GO
Uncategorized

Kill all sessions using database

Transact-SQL 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 123456789 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