맨 윗줄의 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 |
덧글을 달아 주세요
hanstar17 2012/01/10 11:02 고유주소 고치기 답하기
오랜만에 보는 업뎃~!^^
TTF 2012/01/11 20:59 고유주소 고치기
올릴게 없어서 이런거라도... ㅎㄷㄷ