SQL Server'da Veritabanının İçeriğini Boşaltmak

Herkese merhaba,

Bu yazıda SQL Server’da veritabanının içeriğini boşaltmak hakkında bilgi vereceğim.

SQL Server’da bazı durumlarda veritabanının içeriğinin komple, yani her şeyiyle silinmesini isteyebiliriz.

Aşağıdaki kodu kullanarak bu işlemi rahatlıkla yapabilirsiniz.

Uyarı : Bu kodu canlı çalışan sistem üzerinde ya da herhangi bir deneme veritabanı üzerinde test etmeden kesinlikle denemeyin! Aksi durumda tüm sorumluluk size aittir.

--Tüm prosedürleri veritabanından kaldırmak
DECLARE @name VARCHAR(128);
DECLARE @SQL VARCHAR(254);

SELECT @name =
(
    SELECT TOP 1
           [name]
    FROM sysobjects
    WHERE [type] = 'P'
          AND category = 0
    ORDER BY [name]
);

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) + ']';
    EXEC (@SQL);
    PRINT 'Kaldırılan Prosedür: ' + @name;
    SELECT @name =
    (
        SELECT TOP 1
               [name]
        FROM sysobjects
        WHERE [type] = 'P'
              AND category = 0
              AND [name] > @name
        ORDER BY [name]
    );
END;
GO

--Tüm viewları veritabanından kaldırmak
DECLARE @name VARCHAR(128);
DECLARE @SQL VARCHAR(254);

SELECT @name =
(
    SELECT TOP 1
           [name]
    FROM sysobjects
    WHERE [type] = 'V'
          AND category = 0
    ORDER BY [name]
);

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) + ']';
    EXEC (@SQL);
    PRINT 'Kaldırılan View: ' + @name;
    SELECT @name =
    (
        SELECT TOP 1
               [name]
        FROM sysobjects
        WHERE [type] = 'V'
              AND category = 0
              AND [name] > @name
        ORDER BY [name]
    );
END;
GO

--Tüm fonksiyonları veritabanından kaldırmak
DECLARE @name VARCHAR(128);
DECLARE @SQL VARCHAR(254);

SELECT @name =
(
    SELECT TOP 1
           [name]
    FROM sysobjects
    WHERE [type] IN ( N'FN', N'IF', N'TF', N'FS', N'FT' )
          AND category = 0
    ORDER BY [name]
);

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) + ']';
    EXEC (@SQL);
    PRINT 'Kaldırılan Fonksiyon : ' + @name;
    SELECT @name =
    (
        SELECT TOP 1
               [name]
        FROM sysobjects
        WHERE [type] IN ( N'FN', N'IF', N'TF', N'FS', N'FT' )
              AND category = 0
              AND [name] > @name
        ORDER BY [name]
    );
END;
GO

--Tüm foreign keyleri veritabanından kaldırmak
DECLARE @name VARCHAR(128);
DECLARE @constraint VARCHAR(254);
DECLARE @SQL VARCHAR(254);

SELECT @name =
(
    SELECT TOP 1
           TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_CATALOG = DB_NAME()
          AND CONSTRAINT_TYPE = 'FOREIGN KEY'
    ORDER BY TABLE_NAME
);

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint =
    (
        SELECT TOP 1
               CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE CONSTRAINT_CATALOG = DB_NAME()
              AND CONSTRAINT_TYPE = 'FOREIGN KEY'
              AND TABLE_NAME = @name
        ORDER BY CONSTRAINT_NAME
    );
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) + '] DROP CONSTRAINT [' + RTRIM(@constraint) + ']';
        EXEC (@SQL);
        PRINT 'Kaldırılan FK Constraint: ' + @constraint + ' on ' + @name;
        SELECT @constraint =
        (
            SELECT TOP 1
                   CONSTRAINT_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            WHERE CONSTRAINT_CATALOG = DB_NAME()
                  AND CONSTRAINT_TYPE = 'FOREIGN KEY'
                  AND CONSTRAINT_NAME <> @constraint
                  AND TABLE_NAME = @name
            ORDER BY CONSTRAINT_NAME
        );
    END;
    SELECT @name =
    (
        SELECT TOP 1
               TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE CONSTRAINT_CATALOG = DB_NAME()
              AND CONSTRAINT_TYPE = 'FOREIGN KEY'
        ORDER BY TABLE_NAME
    );
END;
GO

--Tüm primary keyleri veritabanından kaldırmak
DECLARE @name VARCHAR(128);
DECLARE @constraint VARCHAR(254);
DECLARE @SQL VARCHAR(254);

SELECT @name =
(
    SELECT TOP 1
           TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_CATALOG = DB_NAME()
          AND CONSTRAINT_TYPE = 'PRIMARY KEY'
    ORDER BY TABLE_NAME
);

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint =
    (
        SELECT TOP 1
               CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE CONSTRAINT_CATALOG = DB_NAME()
              AND CONSTRAINT_TYPE = 'PRIMARY KEY'
              AND TABLE_NAME = @name
        ORDER BY CONSTRAINT_NAME
    );
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) + '] DROP CONSTRAINT [' + RTRIM(@constraint) + ']';
        EXEC (@SQL);
        PRINT 'Kaldırılan PK Constraint: ' + @constraint + ' on ' + @name;
        SELECT @constraint =
        (
            SELECT TOP 1
                   CONSTRAINT_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            WHERE CONSTRAINT_CATALOG = DB_NAME()
                  AND CONSTRAINT_TYPE = 'PRIMARY KEY'
                  AND CONSTRAINT_NAME <> @constraint
                  AND TABLE_NAME = @name
            ORDER BY CONSTRAINT_NAME
        );
    END;
    SELECT @name =
    (
        SELECT TOP 1
               TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE CONSTRAINT_CATALOG = DB_NAME()
              AND CONSTRAINT_TYPE = 'PRIMARY KEY'
        ORDER BY TABLE_NAME
    );
END;
GO

--Tüm tabloları veritabanından kaldırmak
DECLARE @name VARCHAR(128);
DECLARE @SQL VARCHAR(254);

SELECT @name =
(
    SELECT TOP 1
           [name]
    FROM sysobjects
    WHERE [type] = 'U'
          AND category = 0
    ORDER BY [name]
);

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) + ']';
    EXEC (@SQL);
    PRINT 'Kaldırılan Table: ' + @name;
    SELECT @name =
    (
        SELECT TOP 1
               [name]
        FROM sysobjects
        WHERE [type] = 'U'
              AND category = 0
              AND [name] > @name
        ORDER BY [name]
    );
END;
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

--Tüm prosedürleri veritabanından kaldırmak

DECLARE @name VARCHAR(128);

DECLARE @SQL VARCHAR(254);

SELECT @name =

(

SELECT TOP 1

[name]

FROM sysobjects

WHERE [type] = 'P'

AND category = 0

ORDER BY [name]

);

WHILE @name IS NOT NULL

BEGIN

SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) + ']';

EXEC (@SQL);

PRINT 'Kaldırılan Prosedür: ' + @name;

SELECT @name =

(

SELECT TOP 1

[name]

FROM sysobjects

WHERE [type] = 'P'

AND category = 0

AND [name] > @name

ORDER BY [name]

);

END;

--Tüm viewları veritabanından kaldırmak

DECLARE @name VARCHAR(128);

DECLARE @SQL VARCHAR(254);

SELECT @name =

(

SELECT TOP 1

[name]

FROM sysobjects

WHERE [type] = 'V'

AND category = 0

ORDER BY [name]

);

WHILE @name IS NOT NULL

BEGIN

SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) + ']';

EXEC (@SQL);

PRINT 'Kaldırılan View: ' + @name;

SELECT @name =

(

SELECT TOP 1

[name]

FROM sysobjects

WHERE [type] = 'V'

AND category = 0

AND [name] > @name

ORDER BY [name]

);

END;

--Tüm fonksiyonları veritabanından kaldırmak

DECLARE @name VARCHAR(128);

DECLARE @SQL VARCHAR(254);

SELECT @name =

(

SELECT TOP 1

[name]

FROM sysobjects

WHERE [type] IN ( N'FN', N'IF', N'TF', N'FS', N'FT' )

AND category = 0

ORDER BY [name]

);

WHILE @name IS NOT NULL

BEGIN

SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) + ']';

EXEC (@SQL);

PRINT 'Kaldırılan Fonksiyon : ' + @name;

SELECT @name =

(

SELECT TOP 1

[name]

FROM sysobjects

WHERE [type] IN ( N'FN', N'IF', N'TF', N'FS', N'FT' )

AND category = 0

AND [name] > @name

ORDER BY [name]

);

END;

--Tüm foreign keyleri veritabanından kaldırmak

DECLARE @name VARCHAR(128);

DECLARE @constraint VARCHAR(254);

DECLARE @SQL VARCHAR(254);

SELECT @name =

(

SELECT TOP 1

TABLE_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE CONSTRAINT_CATALOG = DB_NAME()

AND CONSTRAINT_TYPE = 'FOREIGN KEY'

ORDER BY TABLE_NAME

);

WHILE @name IS NOT NULL

BEGIN

SELECT @constraint =

(

SELECT TOP 1

CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE CONSTRAINT_CATALOG = DB_NAME()

AND CONSTRAINT_TYPE = 'FOREIGN KEY'

AND TABLE_NAME = @name

ORDER BY CONSTRAINT_NAME

);

WHILE @constraint IS NOT NULL

BEGIN

SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) + '] DROP CONSTRAINT [' + RTRIM(@constraint) + ']';

EXEC (@SQL);

PRINT 'Kaldırılan FK Constraint: ' + @constraint + ' on ' + @name;

SELECT @constraint =

(

SELECT TOP 1

CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE CONSTRAINT_CATALOG = DB_NAME()

AND CONSTRAINT_TYPE = 'FOREIGN KEY'

AND CONSTRAINT_NAME <> @constraint

AND TABLE_NAME = @name

ORDER BY CONSTRAINT_NAME

);

END;

SELECT @name =

(

SELECT TOP 1

TABLE_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE CONSTRAINT_CATALOG = DB_NAME()

AND CONSTRAINT_TYPE = 'FOREIGN KEY'

ORDER BY TABLE_NAME

);

END;

--Tüm primary keyleri veritabanından kaldırmak

DECLARE @name VARCHAR(128);

DECLARE @constraint VARCHAR(254);

DECLARE @SQL VARCHAR(254);

SELECT @name =

(

SELECT TOP 1

TABLE_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE CONSTRAINT_CATALOG = DB_NAME()

AND CONSTRAINT_TYPE = 'PRIMARY KEY'

ORDER BY TABLE_NAME

);

WHILE @name IS NOT NULL

BEGIN

SELECT @constraint =

(

SELECT TOP 1

CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE CONSTRAINT_CATALOG = DB_NAME()

AND CONSTRAINT_TYPE = 'PRIMARY KEY'

AND TABLE_NAME = @name

ORDER BY CONSTRAINT_NAME

);

WHILE @constraint IS NOT NULL

BEGIN

SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) + '] DROP CONSTRAINT [' + RTRIM(@constraint) + ']';

EXEC (@SQL);

PRINT 'Kaldırılan PK Constraint: ' + @constraint + ' on ' + @name;

SELECT @constraint =

(

SELECT TOP 1

CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE CONSTRAINT_CATALOG = DB_NAME()

AND CONSTRAINT_TYPE = 'PRIMARY KEY'

AND CONSTRAINT_NAME <> @constraint

AND TABLE_NAME = @name

ORDER BY CONSTRAINT_NAME

);

END;

SELECT @name =

(

SELECT TOP 1

TABLE_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE CONSTRAINT_CATALOG = DB_NAME()

AND CONSTRAINT_TYPE = 'PRIMARY KEY'

ORDER BY TABLE_NAME

);

END;

--Tüm tabloları veritabanından kaldırmak

DECLARE @name VARCHAR(128);

DECLARE @SQL VARCHAR(254);

SELECT @name =

(

SELECT TOP 1

[name]

FROM sysobjects

WHERE [type] = 'U'

AND category = 0

ORDER BY [name]

);

WHILE @name IS NOT NULL

BEGIN

SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) + ']';

EXEC (@SQL);

PRINT 'Kaldırılan Table: ' + @name;

SELECT @name =

(

SELECT TOP 1

[name]

FROM sysobjects

WHERE [type] = 'U'

AND category = 0

AND [name] > @name

ORDER BY [name]

);

END;

Yukarıdaki kod bloğunu Northwind veritabanı üzerinde test edeceğim. Northwind veritabanı seçili iken kodumu çalıştırıyorum.

İşlem başarı ile gerçekleşti ve kaldırılan objeler de listelenmiş oldu.

Herkese çalışma hayatında ve yaşamında başarılar kolaylıklar.

SQL Server Eğitimleri Kanalına Abone Olun

Görüntülenme Sayısı : 346

Konuyla İlişkili Yazılar

SQL Server’da Seçili Tablonun Scriptini Ayrıntılı Oluşturan Prosedür

SQL Server’da Veritabanındaki Tüm Tabloların Scriptini Almak

SQL Server’da MDF, LDF’lerin Dosya Boyut Bilgilerini Öğrenmek