[MS-SQL] DB Schema 모두 제거하기

DATABASE/MS-SQL 2012/01/10 02:21 글쓴이 : TTF
DB에서 Table, Procedure, View, User Defined Table을 모두 삭제하는 Query 입니다.
맨 윗줄의 Use [DBName]을 수정하시고 사용하세요.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
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 User Defined Table
BEGIN
     DECLARE @IndexForUDTable INT
     SET @IndexForUDTable = 0
     WHILE ( @IndexForUDTable < @SysObjectCount )
     BEGIN
        DECLARE @UDTABLE_NAME VARCHAR(MAX)
        DECLARE UDTABLE_LIST CURSOR FOR
            SELECT NAME FROM SYS.TYPES WHERE SYSTEM_TYPE_ID = 243 AND IS_TABLE_TYPE = 1
        OPEN UDTABLE_LIST
        FETCH NEXT FROM UDTABLE_LIST INTO @UDTABLE_NAME
        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'DROPPING User Defined Table Type '+@UDTABLE_NAME
            EXEC ('DROP TYPE '+@UDTABLE_NAME)
            FETCH NEXT FROM UDTABLE_LIST INTO @UDTABLE_NAME
        END
        CLOSE UDTABLE_LIST
        DEALLOCATE UDTABLE_LIST
        SET @IndexForUDTable = @IndexForUDTable + 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
2012/01/10 02:21 2012/01/10 02:21

걸린 글 0 / 덧글 2
글 걸기 주소 : 이 글에는 트랙백을 보낼 수 없습니다

덧글을 달아 주세요

  1. hanstar17 2012/01/10 11:02 고유주소 고치기 답하기

    오랜만에 보는 업뎃~!^^