맨 윗줄의 Use [DBName]을 수정하시고 사용하세요.
USE [DBName]
GO
DECLARE @SysObjectCount INT
SELECT @SysObjectCount = COUNT('') FROM SYSOBJECTS WITH(NOLOCK)
-- Drop Views
BEGIN
DECLARE @IndexForView INT
SET @IndexForView = 0
WHILE ( @IndexForView < @SysObjectCount )
BEGIN
DECLARE @VIEW_NAME VARCHAR(MAX)
DECLARE VIEW_LIST CURSOR FOR
SELECT NAME FROM SYSOBJECTS O2 WHERE XTYPE='V'
OPEN VIEW_LIST
FETCH NEXT FROM VIEW_LIST INTO @VIEW_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'DROPPING VIEW '+@VIEW_NAME
EXEC ('DROP VIEW '+@VIEW_NAME)
FETCH NEXT FROM VIEW_LIST INTO @VIEW_NAME
END
CLOSE VIEW_LIST
DEALLOCATE VIEW_LIST
SET @IndexForView = @IndexForView + 1
END
END
-- Drop Procedures
BEGIN
DECLARE @IndexForProcedure INT
SET @IndexForProcedure = 0
WHILE ( @IndexForProcedure < @SysObjectCount )
BEGIN
DECLARE @PROCEDURE_NAME VARCHAR(MAX)
DECLARE PROCEDURE_LIST CURSOR FOR
SELECT NAME FROM SYSOBJECTS O2 WHERE XTYPE='P'
OPEN PROCEDURE_LIST
FETCH NEXT FROM PROCEDURE_LIST INTO @PROCEDURE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'DROPPING PROCEDURE '+@PROCEDURE_NAME
EXEC ('DROP PROCEDURE '+@PROCEDURE_NAME)
FETCH NEXT FROM PROCEDURE_LIST INTO @PROCEDURE_NAME
END
CLOSE PROCEDURE_LIST
DEALLOCATE PROCEDURE_LIST
SET @IndexForProcedure = @IndexForProcedure + 1
END
END
-- Drop Tables
BEGIN
DECLARE @IndexForTable INT
SET @IndexForTable = 0
WHILE ( @IndexForTable < @SysObjectCount )
BEGIN
DECLARE @TABLE_NAME VARCHAR(MAX)
DECLARE TABLE_LIST CURSOR FOR
SELECT NAME FROM SYSOBJECTS O2 WHERE XTYPE='U' AND
NOT EXISTS (
SELECT * FROM SYSFOREIGNKEYS K
JOIN SYSCOLUMNS C1 ON (K.FKEYID = C1.ID AND C1.COLID=K.FKEY)
JOIN SYSCOLUMNS C2 ON (K.RKEYID = C2.ID AND C2.COLID=K.RKEY)
WHERE C2.ID = O2.ID AND C1.ID <> O2.ID
)
OPEN TABLE_LIST
FETCH NEXT FROM TABLE_LIST INTO @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'DROPPING TABLE '+@TABLE_NAME
EXEC ('DROP TABLE '+@TABLE_NAME)
FETCH NEXT FROM TABLE_LIST INTO @TABLE_NAME
END
CLOSE TABLE_LIST
DEALLOCATE TABLE_LIST
SET @IndexForTable = @IndexForTable + 1
END
END
_Attach_DB_From_Backup.sql
