Clean-up the Database
Olá Pessoal,
Alguém aqui já precisou realizar a limpeza de um banco de dados, não precisava exclui-lo e cria-lo novamente? Recentemente precisei realizar esse procedimento em alguns servidores e para não perder tempo criei um script que faz a limpeza dos objetos do banco de dados. Nesse primeiro esboço do script ele já realiza o drop das constraints primeiro para não dar erro e também tudo precisa estar no schema dbo. Futuramente pretendo melhorar o script para realizar o drop independente do schema a qual o objeto pertence. Espero que gostem.
USE DropObjects
–USE master
SET NOCOUNT ON
DECLARE @Statement NVARCHAR(MAX)
CREATE TABLE #QueryList
(
SQLStatement VARCHAR(MAX) ,
Executed BIT ,
ObjectPriority TINYINT
)
DECLARE @ObjectPriorities TABLE
(
ObjectName NVARCHAR(500) ,
ObjectType CHAR(2) ,
ObjectPriority TINYINT
)
INSERT INTO @ObjectPriorities
( ObjectName ,
ObjectType ,
ObjectPriority
)
SELECT name ,
type ,
ObjectPriority = CASE WHEN type = ‘F’ THEN 1
WHEN type = ‘IF’ THEN 2
WHEN type = ‘P’ THEN 3
WHEN type = ‘FN’ THEN 4
WHEN type = ‘V’ THEN 5
WHEN type = ‘U’ THEN 6
END
FROM sys.objects
WHERE type NOT IN ( ‘S’, ‘IT’, ‘SQ’, ‘PK’ )
INSERT INTO #QueryList
( SQLStatement ,Executed ,ObjectPriority)
SELECT SQLStatement = CASE WHEN ObjectPriority = 1 THEN ‘ALTER TABLE ‘ + TABLE_SCHEMA + ‘.’ + TABLE_NAME + ‘ DROP CONSTRAINT ‘ + QUOTENAME(ObjectName)
WHEN ObjectPriority = 2 THEN ‘DROP FUNCTION ‘ + QUOTENAME(ObjectName)
WHEN ObjectPriority = 3 THEN ‘DROP PROCEDURE ‘ + QUOTENAME(ObjectName)
WHEN ObjectPriority = 4 THEN ‘DROP FUNCTION ‘ + QUOTENAME(ObjectName)
WHEN ObjectPriority = 5 THEN ‘DROP VIEW ‘ + QUOTENAME(ObjectName)
WHEN ObjectPriority = 6 THEN ‘DROP TABLE ‘ + QUOTENAME(ObjectName)
END ,
0 AS Executed ,
ObjectPriority
FROM @ObjectPriorities AS obj
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS Const ON obj.ObjectName = Const.CONSTRAINT_NAME
ORDER BY ObjectPriority ASC
WHILE ( SELECT COUNT(*)
FROM #QueryList
WHERE Executed = 0) > 0
BEGIN
SELECT TOP 1 @statement = SQLStatement FROM #QueryList
WHERE Executed = 0
ORDER BY ObjectPriority ASC
EXECUTE sp_Executesql @statement
UPDATE #QueryList
SET Executed = 1
WHERE @statement = SQLStatement
END
SELECT SQLStatement, QueryExecuted =
CASE
WHEN Executed = 0 THEN ‘NO’
ELSE ‘YES’
END
FROM #QueryList
ORDER BY ObjectPriority
SET NOCOUNT OFF
DROP TABLE #QueryList
Bom pessoal, por hoje é isso.
Marcos Freccia
[MTA|MCTS|MCITP|MCT SQL Server 2008]
Posted on April 9, 2013, in Administração, Desenvolvimento, SQL Server, T-SQL and tagged clean-up database, drop objects, How to, SQL Server, T-SQL. Bookmark the permalink. 2 Comments.
Reblogged this on Alex Souza.
Muito útil seu script, Marcos. Facilita muito a nossa vida, pois ter que ficar deletando as tabelas por ordem de chave estrangeira dá um trabalho… E ficar criando um monte de versões do mesmo banco a cada vez que quiser testar o script completo de criação das tabelas é feio demais. Ficou bem bacana, já utilizei hoje mesmo. 🙂 Abraço.