Sistem Nesneleri Dışında Veri Tabanından Her Şeyi Silme

Bazı veri tabanı çalışmalarında veri tabanında yer alan bütün nesnelerin silinmesi gerekebiliyor. Burada akla hemen şu gelebilir: madem her şeyi siliyoruz, neden veri tabanını tamamen silip yeniden oluşturmuyoruz, tabi ki yetkiden dolayı yapamıyor olabiliriz, bu durumda tek tek silme ile uğraşmak yerine bunu bir kod parçası ile yapabiliriz.

</p>
<p>/* Drop all non-system stored procs */<br />
DECLARE @name VARCHAR(128)<br />
DECLARE @SQL VARCHAR(254)</p>
<p>SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])</p>
<p>WHILE @name is not null<br />
BEGIN<br />
SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'<br />
EXEC (@SQL)<br />
PRINT 'Dropped Procedure: ' + @name<br />
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] &gt; @name ORDER BY [name])<br />
END<br />
GO</p>
<p>/* Drop all views */<br />
DECLARE @name VARCHAR(128)<br />
DECLARE @SQL VARCHAR(254)</p>
<p>SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])</p>
<p>WHILE @name IS NOT NULL<br />
BEGIN<br />
SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'<br />
EXEC (@SQL)<br />
PRINT 'Dropped View: ' + @name<br />
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] &gt; @name ORDER BY [name])<br />
END<br />
GO</p>
<p>/* Drop all functions */<br />
DECLARE @name VARCHAR(128)<br />
DECLARE @SQL VARCHAR(254)</p>
<p>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])</p>
<p>WHILE @name IS NOT NULL<br />
BEGIN<br />
SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'<br />
EXEC (@SQL)<br />
PRINT 'Dropped Function: ' + @name<br />
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] &gt; @name ORDER BY [name])<br />
END<br />
GO</p>
<p>/* Drop all Foreign Key constraints */<br />
DECLARE @name VARCHAR(128)<br />
DECLARE @constraint VARCHAR(254)<br />
DECLARE @SQL VARCHAR(254)</p>
<p>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)</p>
<p>WHILE @name is not null<br />
BEGIN<br />
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)<br />
WHILE @constraint IS NOT NULL<br />
BEGIN<br />
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'<br />
EXEC (@SQL)<br />
PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name<br />
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 &lt;&gt; @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)<br />
END<br />
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)<br />
END<br />
GO</p>
<p>/* Drop all Primary Key constraints */<br />
DECLARE @name VARCHAR(128)<br />
DECLARE @constraint VARCHAR(254)<br />
DECLARE @SQL VARCHAR(254)</p>
<p>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)</p>
<p>WHILE @name IS NOT NULL<br />
BEGIN<br />
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)<br />
WHILE @constraint is not null<br />
BEGIN<br />
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'<br />
EXEC (@SQL)<br />
PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name<br />
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 &lt;&gt; @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)<br />
END<br />
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)<br />
END<br />
GO</p>
<p>/* Drop all tables */<br />
DECLARE @name VARCHAR(128)<br />
DECLARE @SQL VARCHAR(254)</p>
<p>SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])</p>
<p>WHILE @name IS NOT NULL<br />
BEGIN<br />
SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'<br />
EXEC (@SQL)<br />
PRINT 'Dropped Table: ' + @name<br />
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] &gt; @name ORDER BY [name])<br />
END<br />
GO</p>
<p>

Yorum Yapın

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑

%d blogcu bunu beğendi: