SQL Server Sistem Nesneleri Dışındaki Nesneleri Toplu Silme

Veri tabanı geliştirme aşamalarında kullanıcı tarafından oluşturulan bütün nesnelerin (stored prosedür, view, tablo vb.) silinmesi istenebilir. Böyle bir durumda silinecek nesneler sysobjects katalog içeriğinden faydalanılarak bulunabilir.

Kullanıcı Tanımlı Stored Procedürlerin Toplu Silinmesi

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 'Dropped Procedure: ' + @name 
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name]) 
END 
GO

Kod içerisinde kullanılan yapıları inceleyecek olursak;

• sysobjects: Veri tabanında bulunan bütün nesnelerin tutulduğu katalogtur.
• type: sysobjects kataloğuna ait bir kolondur ve nesnenin türünü belirtir. Burada verilen “P” karakteri ile stored prosedürler alınıyor.
• EXEC(@sql): Parametre olarak aldığı string olarak tutulan bir SQL komutunu çalıştırır. Bu yapı sayesinde dinamik olarak oluşturulan SQL kodları çalıştırılabilir.
• WHILE: Belli bir şarta göre belirli görevleri tekrar eden yapıdır. Burada kullanılan şart: “sysobjects kataloğundan @name parametresine sonuç döndüğü sürece”dir.
• “SELECT @SQL = ‘DROP PROCEDURE [dbo].[‘ + RTRIM(@name) +’]'” sorgusu ile dönen @name parametresi ile dinamik olarak DROP komutu oluşturulur ve sonraki satırda yer alan EXEC fonksiyonu ile bu komut çalıştırılır.

Kullanıcı Tanımlı View’lerin Silinmesi

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 'Dropped View: ' + @name 
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name]) 
END 
GO

Kullanıcı Tanımlı Fonksiyonların Silinmesi

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 'Dropped Function: ' + @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

Kullanıcı tanımlı fonksiyonlar sorgulanırken type şartına verilen değerler aşağıdaki gibidir.

• FN: Skaler değer dönen fonksiyon
• In-Line Tablo değer dönen fonksiyon
• TF: Tablo değer dönen fonksiyon
• FS: CLR ile tanımlanmış skaler değer dönen fonksiyon
• FT: CLR ile tanımlanmış tablo dönen fonksiyon

Yabancıl Anahtar (Foreign Key) Toplu Silme

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 'Dropped 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

Tablolar üzerinde yer alan kısıtlamaları sorgulamak için INFORMATION_SCHEMA.TABLE_CONSTRAINTS kataloğundan faydalanılır. Bizim kullandığımız sorguda kullanılan constraint_catalog ve CONSTRAINT_TYPE kolonları sırasıyla zorlamanın bulunduğu veri tabanını ve zorlama tipini belirtmektedir. DB_NAME() fonksiyonu üzerinde çalışılan veri tabanı adını verir.

Foreign Key silinirken tablo düzenlendiğinden dolayı oluşturulan komut “ALTER TABLE ” komutudur.

Kullanıcı Tanımlı Tabloların Silinmesi

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 'Dropped Table: ' + @name 
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name]) 
END 
GO 

Tabloların hata alınmadan silinebilmesi için öncelikle veri tabanı üzerinde tanımlı yabancıl anahtarların (foreign key) silinmesi gerekmektedir. Bu sebeple iki kodun beraber kullanılması tablo silme işleminde hatayı engelleyecektir. Aslında alınan bu hata işlemi durduracak bir hatadan öte işlem adımlarını arttıran bir hata olacaktır. Komut referans alınan bir tablo silmeye çalıştığında hata alacaktır ve sonraki tabloya geçecektir bu şekilde işlem bütün tabloları silene kadar devam edecektir.