Recent comments

None


İçerik Ara











Yasal Uyarı
Bu sitede sunulan tüm bilgi ve dökümanlar Turgay Sahtiyan tarafından yazılmaktadır. Yazıların kaynak göstermek şartıyla kullanılması serbesttir.

© Copyright 2009-2013
Takvim
<<  Ağustos 2017  >>
PaSaÇaPeCuCuPa
31123456
78910111213
14151617181920
21222324252627
28293031123
45678910
Keywords

Uzun süredir vakit bulamadığımdan dolayı yapamadığım bir şeyi sonunda bugün vakit bulup yapabildim.

Başlıktanda anladığınız gibi bloğumu Blog Engine 1.6.1 e update ettim.

Özellikle reCaptcha nın çok önemli olduğu bu update diğer güzel özellikler şu şekilde;

  • reCaptcha ile spam kontrolü
  • Yorumları topluca onaylama yada silme
  • Disqus comment özelliği
  • Yorumlar için White List ve Black List seçenekleri

Blogengine sayfasındaki konuyla alakalı post a erişmek için http://www.dotnetblogengine.net/post/BlogEngine-161-Update.aspx

Hayırlı olması dileğiyle :)

 

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bildiğiniz gibi Management Studio üzerinden  bir DB nin en son ne zaman backup ının alındığına bakabiliyoruz. Bunun için bakmak istediğimiz database e sağ tıklayıp properties ekranını açıyoruz.

 ldb1

Bu ekrandaki Last Database Backup yazısının yanındaki tarih database in en son ne zaman backuplandığını gösteriyor.

 

Ayrıca database reportlarındaki Backup and Restore Events ten de bir DB nin backup tarihçesine bakabiliriz.

Bunun için Database>>Reports>>Standart Reports>>Backup and Restore Events tıklıyoruz. Daha sonra açılan pencerede Successful Backup Operations yazısını tıklarsak bu ekran bize bu DB için gerçekleşen başarılı backup operasyonlarını verecektir.

 ldb2

ldb3

Sistemimizde çok sayıda database var ise ve biz bu database lerin son backup alınma tarihine bakmak istiyorsak yukarıda anlattığım 2 yöntemde oldukça zaman alıcı yöntemler.

Peki bütün database ler için son backup alma tarihini tek bir script ile öğrenemezmiyiz. Tabiki öğrenebiliriz. Bugünkü makalemizin nedenide bu :)

Aşağıdaki script ile sistemde bulunan tüm database lerin son backup alınma tarihini öğrenebilirsiniz.

SELECT sd.Name as DatabaseName,
COALESCE(Convert(varchar(12), MAX(bs.backup_finish_date), 101),'Daha Backup Alınmamış') as LastBackUpDate,
COALESCE(Convert(varchar(12), MAX(bs.user_name), 101),'NA') as UserName
FROM sys.sysdatabases sd
LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = sd.name
GROUP BY sd.Name
ORDER BY sd.Name

 

Benim sistemimde çıkan sonuç

ldb4

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bu yazımda sıkça sorulan bir konudan bahsetmek istiyorum. “Log dosyası olmadan bir Database Attach edilebilir mi?”

Bu sorunun cevabına geçmeden önce Attach ve Detach hakkında biraz laflayalım.

Backup ile bir DB nin backup ını alıyor ve başka bir ortama restore edebiliyoruz. Attach ile ise Bir DB nin SQL Server a olan bağlantısını kesip, Detach ile tekrar bu bağlantıyı kuruyoruz.

Detach işlemi yapıldığında DB nin SQL Server la bağlantısı kalkıyor dedik. Peki ya bu DB ye ait Data ve Log file lar? Bu dosyalar olduğu gibi disk te kalmakta. Daha sonra bu dosyaları kullanarak istediğimiz server a Attach işlemini gerçekleştirebiliriz.

Burada ufak bir bilgi vermek istiyorum. SQL Server açısından 32 bit ve 64 bit dosya yapısı aynı. Yani attach işlemi 32 bit – 64 bit server lar arasında çalışabilmekte.

Peki nasıl bir case de Attach-Detach kullanırız? Örneğin bir DB yi bir serverdan alıp başka bir server a taşıyacağız. Backup-Restore çözümü ilk akla gelen yöntem olmakla beraber, bazen detach edip, file ları diğer server a taşıyıp, attach etmek daha hızlı bir çözüm olabilmekte.

Şimdi Attach ve Detach in nasıl yapıldığına bakalım.

Detach


Detach işlemi eğer aşağıdaki durumlardan biri geçerli ise yapılamaz.

  • Eğer DB replica ediliyor yada publich ediliyor ise detach edilemez.
  • Eğer attach yapılmak istenen DB nin snapshot ları mevcut ise attach işlemi yapılamaz.
  • Eğer DB mirror lanan bir DB ise attach edilemez.
  • Eğer DB suspect mod da ise detach edilemez.
  • System DB leri detach edilemez. (master,msdb,tempdb,model)

SQL Server Management Studio ile Detach İşlemi

  1. Detach edilmek istenen DB ye sağ tıklanıp Detach yazısı tıklanır.
    image 
  2. Ok e basıyoruz ve Detach i bitiriyoruz J Eğer bağlı olan connection ları drop etmek isterseniz drop connection check box ını tıklayabilirsiniz.
    image


T-SQL ile Detach İşlemi

EXEC sp_detach_db 'AdventureWorks', 'true';
  • 1. parametre DB seçimi için kullanılır.
  • 2.parametre ise detach yapılmadan önce table larda UPDATE STATISTICS yapılıp yapılmayacağını belirleyen parametredir. Eğer bu parametre true ise Update Statistics yapılmaz, false ise yapılır.



Attach


Daha önce detach edilmiş yada kopyalanmış bir DB yi attach yapabilirsiniz.

SQL Server Management Studio ile Attach İşlemi

  1. Databases yazısına sağ tıklanıp attach yazısı tıklanır.
    image
  2. Açılan ekranda Data File ı seçmek amaçlı Add butonuna basılır.
    image
  3. Disk ten data file ın bulunduğu yer seçilir ve OK e basılır.
    image
  4. Current File Path kısmından Dosya uzantılarını kontrol ediyoruz. Ayrıca eğer başka bir isimle restore etmek istiyorsak Attach As kısmında istediğimiz DB adını verebiliriz.
    image 
  5. OK e basarak işlemi bitiriyoruz.


T-SQL ile Attach İşlemi

CREATE DATABASE [AdventureWorks] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks_Data.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks_Log.ldf' )
 FOR ATTACH

 

Evet. Şimdi makalemizin ana konusu olan “Log dosyası olmadan Database Attach etme” konumuza geri dönelim.


Log Dosyası Olmadan Database Attach Etme


Management Studio ile

Data file larının olduğu ekranda log dosyasını seçip remove butonuna basıyoruz.

image

Bu işlemden sonra OK e basarsak yeni bir log dosyası oluşarak attach işlemi sonuçlanacaktır.


T-SQL ile

CREATE DATABASE [AdventureWorks] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks_Data.mdf' )
 FOR ATTACH

 

İşlem bu kadar basit. Log dosyasını parametreden kaldırmak yeterli.

Ayrıca bu işlem için sp_attach_single_file_db system prosedürü kullanılabilmekte ama daha sonraki versiyonlarda bu sp kaldırılması gündemde olduğu için kullanmamanızı öneririm.


Açıklayıcı bir yazı olduğunu umuyorum.

İyi çalışmalar,

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bugün size iş arkadaşım Kadir Evciler’in yazmış olduğu bir SP yi anlatıyor olacağım.

Sayısal Loto oynarken numara sallamaktan sıkılan Kadir arkadaşım oturmuş ve SQL Server de bir SP yazmış.:) Bu SP ye parametre olarak başlangıç ve bitiş numarasını veriyorsunuz, kaç rakam istediğinizi söylüyorsunuz. Oda size random olarak sayıları generate edip veriyor.

Örneğin sayısal loto için 1,49,6 parametrelerini verdiğimizde bize 1 ile 49 arasında 6 tane sayı geri döndürüyor.

Kullanımına bakacak olursak;

EXEC RandomNumber 1,49,6


Sonuç:
17,18,24,32,35,43


Ve işte para kazandırma garantili o muhteşem SP :)

/*
RANDOM NUMBER GENERATOR
RASGELE SAYI ÜRETİCİ
*/
CREATE PROC RandomNumber @startIndex int,@endIndex int,@randomNumberCount int
AS
-- SP Name: RandomNumber
-- Author: Kadir Evciler
SET @endIndex=@endIndex+1
DECLARE @DIFF INT,@I INT,@RANNUM INT
SET @I=0
SET @DIFF=@endIndex-@startIndex
IF @startIndex>=@endIndex
BEGIN
RAISERROR('END INDEX NUMBER MUST BE GREATER THAN START INDEX NUMBER',16,1)
RETURN
END
ELSE IF @DIFF < @randomNumberCount BEGIN RAISERROR('DIFFERENCES OF START AND END INDEX NUMBERS MUST BE GREATER THAN RANDOM NUMBER COUNT, IN THIS EXAMPLE MAX RANDOM NUMBER COUNT MIGHT BE %d',16,1,@DIFF) RETURN END CREATE TABLE #TEMP(NUMBERS INT); WHILE @I<@randomNumberCount BEGIN SELECT @RANNUM=RAND()*@endIndex IF @RANNUM >=@startIndex AND NOT EXISTS(SELECT TOP 1 1 FROM #TEMP WHERE NUMBERS=@RANNUM)
BEGIN
INSERT INTO #TEMP VALUES(@RANNUM)
SET @I=@I+1
END
END
DECLARE @STR VARCHAR(MAX)=''
SELECT @STR=@STR+CAST(NUMBERS AS VARCHAR(2))+',' FROM #TEMP ORDER BY NUMBERS ASC
SELECT @STR


Bol kazançlar

Turgay Sahtiyan
Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bir önceki yazımda SQL Server daki system table larının ne olduklarını ve ne amaçla kullandıklarını anlatmıştım. Bu yazıma aşağıdaki linkten erişebilirsiniz.
http://www.turgaysahtiyan.com/post/SQL-Server-System-Databases-(Sistem-Veritabanlarc4b1).aspx

TempDB Database File larını Taşımak makalemde de bahsettiğim gibi system DB lerinin taşınması normal user DB lerinin taşınmasından farklıdır.

Bu yazımda da master DB nin database file larının nasıl taşınacağını adım adım anlatıyor olacağım.

  1. Start >> Programs >> Microsoft SQL Server 2008 >> Configuration Tool >> SQL Server Configuration Manager ı açalım.
    image
  2. Master DB sinde değişiklik yapmak istediğimiz SQL Server instance ı sağ tıklayıp properties e basalım. (örneğin SQLServer(MSSQLServer)
    image
  3. Advanced tab ında Startup Parameters te oynama yapacağız.
    image
  4. Startup parameters deki yazı herhangi bir değişiklik yapılmadıysa aşağıdaki gibidir.

    -dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;
    -eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;
    -lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

    Master DB nin Data ve Log file larını C:\DATA folder ına taşıdığımızı düşünürsek startup parameters de ki yazı aşağıdaki gibi olmalıdır.

    -dC:\DATA\master.mdf;
    -eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;
    -lC:\DATA\mastlog.ldf

    Bu yazıyı alıp startup parameters e paste edip apply diyerek ekranı kapatalım.
  5. Configuration Manager üzerinden SQL Server Service i stop edelim.
  6. Master DB nin data ve log file larını (master.mdf ve mastlog.ldf) bulunduğu yerden alıp C:\Data folder ına taşıyalım.
  7. Configuration Manager dan SQL Server service ini tekrar start edelim.
  8. Kontrol amaçlı Management Studio üzerinden aşağıdaki sorguyu çalıştıralım.
    SELECT name, physical_name AS CurrentLocation, state_desc
    FROM sys.master_files
    WHERE database_id = DB_ID('master');
    GO
  9. Aşağıdaki gibi bir sonuç almamız beklenmektedir.
    image


Bir sonraki makalemde görüşmek üzere.

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server da bulunan Sistem Database leri ve kısa açıklamaları aşağıdaki gibidir.

Sistem Database

Açıklama

master

SQL Server ile alakalı server – level bilgileri tutar.

msdb

SQL Server Agent tarafından kullanılan bu db; alert ve job lar ile alakalı bilgileri tutar

model

Model DB si SQL Server da yeni oluşturulacak DB lerin template idir. Msdb üzerinde database size,collation,recovery model gibi bilgiler değiştirilerek yeni oluşturulan her DB nin bu bilgilerle oluşturulması sağlanabilir.

resource

SQL Server sistem obje leri tutan Resource DB si bir read-only DB dir. Sistem obje leri fiziksel olarak resource db de tutulurken mantıksal olarak her DB nin içerisinde gösterilir.

tempdb

TempDB temporary obje leri tutar. Her SQL Service in restart ında tekrar create edilir.


Sistem DB lerine Management Studio üzerinden aşağıdaki şekilde erişebilirsiniz.

image

Gördüğünüz gibi bu listede resource db bulunmamakta. Daha öncede bahsettiğimiz gibi obje ler fiziksel olarak resource db de tutulmaktadır. Bu objeler mantıksal olarak her db nin içerisinde gösterilmektedir.

Sistem DB lerine script ile ulaşmak için aşağıdaki kodu kullanabilirsiniz.

select * from sys.databases where database_id<=4

 

image

Sistem database leri SQL Server kurulumu ile beraber gelmektedir. Ve her SQL server instance ında sistem database leri query sonucunda görülen database_id leri almaktadır.

Sistem DB lerine daha detaylı incelemeye devam ediyoruz.


master Database

master Database SQL Server ile alakalı server-level bilgileri tutar. Örneğin loginler, backup device lar, linked server lar ve Endpoint gibi bilgiler master DB de tutulmaktadır. Ayrıca sistem de bulunan user database bilgileri ve bu database lerin file bilgileride master DB de tutulmaktadır. Dolayısıyla eğer master DB erişilemez durumdaysa SQL Server açılamaz durumda olur. Bundan dolayı master DB nin sıklıkla backuplanması önerilmektedir. Bu arada ufak bir bilgi vermek istiyorum. Master DB nin sadece full backup ı alınabilir, diff yada tlog backup ı alınamaz.

Master db ile alakalı MSDN de bulunan öneriler aşağıdaki gibidir.

  • Master DB periyodik olarak backup lanmalıdır.
  • Aşağıdaki işlemlerden herhangi biri yapıldığında en kısa sürede master db nin back up ı alınmalıdır.
    • Her hangi bir DB oluşturulduğunda, değiştirildiğinde yada silindiğinde.
    • Server yada DB configuration ları değiştirildiğinde.
    • Logon account larında değişiklik yapıldığında yada yeni bir login eklendiğinde.
  • Master DB içerisinde user object create edilmesi önerilmez.
  • Master DB nin TRUSTWORTHY özelliğinin ON yapılması önerilmez.


msdb Database


SQL Server agent tarafından kullanılan bu db; alert ve job lar ile alakalı bilgileri tutar.

Örneğin job history için aşağıdaki query i msdb db sinde çalıştırabilirsiniz.

select job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime

 

model Database


Model DB si SQL Server da yeni oluşturulacak DB lerin template idir. Msdb üzerinde database size,collation,recovery model gibi bilgiler değiştirilerek yeni oluşturulan her DB nin bu bilgilerle oluşturulması sağlanabilir. TempDB her SQL Server restart ında create edildiği için, model DB SQL Server instance ında her zaman vardır.


tempDB Database


tempDB, local temporary tables, temporary stored procedures, table variables ve cursor gibi temp objeleri tutan database dir. Ayrıca result set te yapılan sorting(sıralama) işlemi tempDB de yapılmaktadır.

tempDB, SQL Server ın performans lı çalışması için en önemli etkenlerden biridir. En base olarak tempDB nin data ve log file larının diğer database lerden ayrı bir diskte, hatta mümkünse data ve log file larının farklı disklerde tutulması best practise dir. Ayrıca tempDB data file ının core işlemli sayısı kadar file dan oluşması ve her birinin eşit boyutta verilmesi diğer bir best practise adımıdır.

tempDB data file larının küçük değerlerde olmasının şöyle bir dezavantajı vardır. Örneğin çok kayıtlı bir result set execute edilidğinde tempDB data file ı bu sonucu allocate edecek boyutta değil ise auto growth olması gerekmektedir. Bu da performans a negatif etki yapmaktadır.

TempDB Data ve Log file larının taşınması ile ilgili yazıma aşağıdaki url den erişebilirsiniz.
http://www.turgaysahtiyan.com/post/TempDB-Database-File-larc4b1nc4b1-Tasc4b1mak.aspx



Makaleme burada nokta koyarken sistem DB leri hakkında yeteri kadar bilgiye ulaştığınızı umuyorum.

Bir sonraki makalemizde görüşmek üzere.

İyi çalışmalar,

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Login leri database lere yetkilendirirken Database Role lar kullanarak gruplarız.

Örneğin Developer lar için bir database i role ü oluşturur ve bu role e yetkilendirmeler yaparız. Developer User larıda bu role e ekleyerek otomatik yetkilendirmiş oluruz. Daha sonra developer yetkilerinde bir değişiklik yapmak istediğimizde tek tek developer user larda oynama yapmak yerine developer role ünü değiştiririz ve bütün developer user lar yansımasını sağlarız.

Böyle bir yapı kurgulamamız user ları daha iyi ve hızlı yönetmemizi sağlar.

Bugünkü konumuz ise bir DB için tanımlanmış Database Role de hangi user lar olduğunu listelemek.

Ortamlar arası DB taşıması yaparken, role lerde ki userlarıda bu makaledeki SP i sayesinde rahatça taşıyor olacağız.

İlk olarak listelemeyi yapacak olan SP ye bakalım.

USE [master]
GO
CREATE PROCEDURE [GetRoleMembers] @DBName sysname, @RoleName sysname
As

CREATE TABLE ##tmp_role_member_ids (id int not null, role_id int null, sub_role_id int null, generation int null)
CREATE TABLE ##tmp_names (name sysname)

declare @str varchar(max)=''
set @str='
declare @generation int
set @generation = 0
INSERT INTO ##tmp_role_member_ids (id) SELECT
rl.principal_id AS [ID]
FROM 
'+@DBName+'.sys.database_principals AS rl
WHERE
(rl.type = ''R'')and(rl.name='''+@RoleName+''')
UPDATE ##tmp_role_member_ids SET role_id = id, sub_role_id = id, generation=@generation
WHILE ( 1=1 )
BEGIN
	INSERT INTO ##tmp_role_member_ids (id, role_id, sub_role_id, generation)
		SELECT a.member_principal_id, b.role_id, a.role_principal_id, @generation + 1
            FROM '+@DBName+'.sys.database_role_members AS a INNER JOIN ##tmp_role_member_ids AS b
			ON a.role_principal_id = b.id
            WHERE b.generation = @generation
	if @@ROWCOUNT <= 0
		break
	set @generation = @generation + 1
END
DELETE ##tmp_role_member_ids WHERE id in (SELECT
rl.principal_id AS [ID]
FROM
'+@DBName+'.sys.database_principals AS rl
WHERE
(rl.type = ''R'')and(rl.name='''+@RoleName+''') )
UPDATE ##tmp_role_member_ids SET generation = 0;
INSERT INTO ##tmp_role_member_ids (id, role_id, generation) 
    SELECT distinct id, role_id, 1 FROM ##tmp_role_member_ids
DELETE ##tmp_role_member_ids WHERE generation = 0

insert ##tmp_names
SELECT
u.name AS [Name]
FROM
'+@DBName+'.sys.database_principals AS rl
INNER JOIN ##tmp_role_member_ids AS m ON m.role_id=rl.principal_id
INNER JOIN '+@DBName+'.sys.database_principals AS u ON u.principal_id = m.id
WHERE
(rl.type = ''R'')and(rl.name='''+@RoleName+''')
ORDER BY
[Name] ASC
'
exec (@str)
--print @str

select Name from ##tmp_names

drop table ##tmp_role_member_ids
drop table ##tmp_names

 

Gördüğünüz gibi SP yi Master DB sinde create ediyoruz. Her DB için ayrı SP kullanmaktansa DB yi parametre olarak göndereceğiz.

2. parametre ise role parametresi. Hangi DB içindeki hangi Role de ki user lar?

Kullanımına bakacak olursak;

Use Master
GO
exec GetRoleMembers 'AdventureWorks','My_Role'
GO

 

Şimdi örneğimize geçelim.

İlk önce tanımlamaları yapıyoruz.

User ları create edelim.

use Master
GO
CREATE LOGIN User1 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
CREATE LOGIN User2 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
CREATE LOGIN User3 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
CREATE LOGIN User4 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
CREATE LOGIN User5 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
GO

 

User ları AdventureWorks DB sinde açalım.

Use AdventureWorks
GO
CREATE User [User1] FOR LOGIN [User1];
CREATE User [User2] FOR LOGIN [User2];
CREATE User [User3] FOR LOGIN [User3];
CREATE User [User4] FOR LOGIN [User4];
CREATE User [User5] FOR LOGIN [User5];
GO

 

Yeni bir DB Role tanımlayalım.

Use AdventureWorks
GO
CREATE ROLE [My_Role] AUTHORIZATION [dbo]
GO

 

User ları bu Role e ekleyelim.

Use AdventureWorks
GO
EXEC sp_addrolemember N'My_Role', N'User1';
EXEC sp_addrolemember N'My_Role', N'User2';
EXEC sp_addrolemember N'My_Role', N'User3';
EXEC sp_addrolemember N'My_Role', N'User4';
EXEC sp_addrolemember N'My_Role', N'User5';
GO

 

Tanımlamalar bitti. Şimdi SP yi kullanarak AdventureWorks DB sindeki My_Role DB Role üne eklenmiş user ları listeleyelim.

Use Master
GO
exec GetRoleMembers 'AdventureWorks','My_Role'
GO

 

Name
============
User1
User2
User3
User4
User5

Bu SP yi kullanarak user lar için Role e ekleme script i de oluşturabilirsiniz. Bunuda size bırakıyorum J

İyi çalışmalar,

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bir kullanıcıya DB bazında yetkilendirmelerin nasıl verildiğini SQL Server da Kullanıcı Yaratma ve Yetkilendirme yazımda anlatmıştım. Eğer bu yazımı okumadıysanız bundan sonrasını anlamak için lütfen okuyunuz.

Bir kullanıcıyı sysAdmin yada bunun gibi server bazlı yetkilendirmeler değilde db_owner yada db_datareader tarzı DB bazlı yetkilendirmeler her DB için ayrı ayrı verilir.

Yani eğer 10 tane DB niz varsa ve create ettiğinzi yeni bir user bu 10 DB de de db_owner olsun istiyorsanız User Mapping kısmında her DB için db_owner seçeneğini seçmeniz gerekir.

Peki bu işin kolay bir yolu yok mu? Tabiki var ve bu yazımızın konusuda bu.

Önce yetkilendirme işlemini yapacağımız bir user create edelim.

CREATE LOGIN MyUser 
WITH 
	PASSWORD=N'm',
	
	DEFAULT_DATABASE=[master],
	
	CHECK_EXPIRATION=OFF,
	
	CHECK_POLICY=OFF

Şimdide bu user ı server da bulunan bütün DB lerde db_owner yapalım.

select @str += 'USE ['+ name +']; ' + CHAR(10) +
	   'CREATE USER [MyUser] FOR LOGIN [MyUser] ' + char(10) +
	   'EXEC sp_addrolemember N''db_owner'', N''MyUser'' ' + char(10) + char(10)
from sys.databases where database_id>4
--print (@str)
exec @str


İşlem bu kadar basit.

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bu makalemde DDL trigger ile SQL server login işlemi esnasında gelen login isteğini kontrol edeceğiz ve kullanıcı istediğimiz makinadan yada uygulamadan bağlanmadıysa login i engelleyeceğiz.

Canlı bir örnek vermek gerekirse; production sql server ortamınızda bir user create ettiniz ve uygulamada bu user ı kullanıyorsunuz. User ın bu uygulama harici kullanılmasını örneğin SSMS kullanılarak login olunmasını engellemek istiyorsunuz. İşte bu engellemeyi DDL trigger ile yapacağız.

Ama bu işleme geçmeden önce SQL Server DDL Trigger lar hakkında biraz bilgi vermek istiyorum.

DDL – DML Nedir?


SQL Server da DML(Data Manipulation Language) ve DDL(Data Definition Language) olmak üzere 2 tip script tipi vardır.

DML, database üzerinde yapılan insert,update,delete gibi data manipulation işlemlerini ifade eder. DML trigger ların oluşturulması ile alakalı daha önce yazdığım yazıyı okumanızı tavsiye ederim. Buradan erişebilirsiniz.

DDL ise database yada server üzerinde yapılan obje create,drop,alter gibi obje değişikliklerini ifade eder. Database creatinde yada table drop unda DDL trigger kullanarak işlemi yakalayabilir ve buna göre aksiyon alabiliriz.


İlk DDL Trigger ımız


Örneğin aşağıdaki DDL trigger ile kullanıcıların database üzerinde table drop etmesini engelleyebiliriz.

CREATE TRIGGER trg_Table_Drop
ON DATABASE 
FOR DROP_TABLE
AS 
   PRINT 'Table drop işlemi engellenmiştir!. "trg_Table_Drop trigger ını disable etmeniz gerekmektedir.' 
   ROLLBACK

 

DDL Trigger ı geçici olarak disable etmeniz gerekebilir. Örneğin yukarıdaki gibi table drop ddl trigger ınız var. Drop etmeye çalıştınız ve hata mesajını aldınız. Ama genede drop etmeniz gerekiyor. Bu durumda trigger ı disable edip drop işlemini yaptıktan sonra tekrar enable edebilirsiniz.

DISABLE TRIGGER [logon_ddl_trg] ON ALL SERVER
ENABLE TRIGGER [logon_ddl_trg] ON ALL SERVER

 

Ayrıca daha önce yazdığım tüm trigger ları disable-enable etme script ine aşağıdaki url den erişebilirsiniz.
http://www.turgaysahtiyan.com/post/Sql-Server-da-Triger-larc4b1-Sql-Kod-ile-Aktif-ve-Deaktif-Etmek.aspx

Server da create edilmiş DDL Trigger lara sys.server_triggers table ından bakabilirsiniz.

select * from sys.server_triggers

 

sys.server_trigger_events tableını kullanarakta bu trigger ların hangi event ler için create edilmiş olduğuna bakabilirsiniz.

select * from sys.server_trigger_events

 

Daha öncede söylediğim gibi DDL trigger lar sadece bazı event ler ile tetiklenmektedir. Bu event lerin tamamına aşağıdaki url den erişebilirsiniz.
http://msdn.microsoft.com/en-us/library/bb522542.aspx

DDL Trigger İle Login Kontrolü


Şimdi asıl konumuz olan DDL trigger ile Login kontrolüne geçelim.

Konuyu anlamanın daha anlaşılır olacağını düşündüğüm için direk örneklere geçiyorum.

Örneklere geçmeden önce “t” name inde bir SQL server user create ediyoruz.

USE [master]
GO
if exists (Select * from sql_logins where type_desc='SQL_LOGIN' and name='t')
  DROP LOGIN [t]
GO
CREATE LOGIN [t] WITH PASSWORD=N't', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

 

Şimdi örnekler;

  1. “t “ SQL Server login i SQL Server a bağlanamasın. 
     
    if exists(select * from sys.server_triggers
        where name='logon_ddl_trg')
    DROP TRIGGER [logon_ddl_trg] ON ALL SERVER;
    GO
    
    ALTER TRIGGER [logon_ddl_trg]
    ON ALL SERVER WITH EXECUTE AS 'sa'
    FOR LOGON
    AS
    BEGIN 
      IF ORIGINAL_LOGIN()= 't' BEGIN
    		  rollback;
      END
    END
        

    Yukarıdaki trigger ı Execute ettikten sonra SSMS üzerinden bağlantı kurmayı denediğinizde aşağıdaki gibi bir hata mesajıyla karşılasacak ve bağlantı kuramayacaksınız.

    image 

  2. “t” user ı sadece management studio kullanarak login olamasın, diğer uygulamalarla login olabilsin.

    if exists(select * from sys.server_triggers
        where name='logon_ddl_trg')
    DROP TRIGGER [logon_ddl_trg] ON ALL SERVER;
    GO
    
    ALTER TRIGGER [logon_ddl_trg]
    ON ALL SERVER WITH EXECUTE AS 'sa'
    FOR LOGON
    AS
    BEGIN 
       IF ORIGINAL_LOGIN()= 't' 
         and APP_NAME()= 'Microsoft SQL Server Management Studio' BEGIN 
    		  rollback;
      END
    END


    Trigger ı execute ettikten sonra SSMS üzerinden bağlantı kurmayı denerseniz bağlanamadığınızı göreceksiniz.

    Birde osql ile bağlantı kurmayı deneyelim. Bunun için command promp ta osql –S sahtiyan_tu –U t –P t yazıp enter a basalım. Bingo. Bağlantı kurabildik. :)

    Bu tarz bir trigger la application için tanımladığınız user ın sadece o application ile kullanılmasını sağlayabilir ve güvenliğinizi arttırabilirsiniz.

  3. Son olarak LoginName ve ApplicationName gibi bilgileri gelen eventdata dan almak mümkün. Bununla alakalı örneğide aşağıda inceleyebilirsiniz.
    if exists(select * from sys.server_triggers 
    	where name='logon_ddl_trg')
      DROP TRIGGER [logon_ddl_trg] ON ALL SERVER;
    GO
    
    CREATE TRIGGER [logon_ddl_trg]
    ON ALL SERVER WITH EXECUTE AS 'sa'
    FOR LOGON
    AS
    BEGIN
       declare @EventData xml,
               @PostTime datetime,
               @LoginName varchar(50),
               @ClientHost varchar(50),
               @LoginType varchar(50)
       set @EventData = eventdata()
    
    
       set @PostTime = @EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
       set @LoginName = @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
       set @ClientHost = @EventData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
       set @LoginType = @EventData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)')
    
       if @LoginType = 'SQL Login' and @LoginName = 't'
          rollback;          
    end
    GO

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Güvenlik nedeniyle SQL Server login lerden Builtin/Administrator ı genelde kaldırırız biliyorsunuz. Bu şekilde sadece sa kullanıcısının ve bizim belirlediğimiz yetkili kullanıcıların sysAdmin kalmasını sağlarız.

Peki ya sa şifresini unutursak yada kaybedersek ne yapacağız?

Sıkça karşılaşılan bu problemin elbette bir çözümü var. Bu makalede bunu görüyor olacağız.

Unutulan sa şifresinin tekrar set edilmesi

  1. SQL Server Configuration Manager >> SQL Server >> Log On account tan SQL Server ın üzerinde kurulu olduğu makinada admin haklarına sahip bir windows account u seçiyoruz. Apply >> OK diyerek kapatıyoruz.
    image
  2. Local Computer Policy>>Windows Settings>>Security Settings>>Local Policies>>User Rights Assignment>>Log on as a service e bu account u ekliyoruz.
  3. Yaptığımız policy değişikliği update etmek için command prompt u açarak gpupdate /force yazıp enter a basıyoruz.
    image 
  4. SQL Server Configuration Manager dan SQL Server ı stop ediyoruz.
  5. SQL Server ı –m parametresi ile tekrar start ediyoruz. Bunun için command prompt ta C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe –m çalıştırıyoruz.
  6. Command prompt ta OSQL ile bağlanarak SA şifresini set ediyoruz.
    osql –E
    ALTER LOGIN [sa] WITH PASSWORD=N'1' WITH CHECK_POLICY = OFF
    GO
  7. -m parametresini kullandığımız command prompt ta exit yazıp enter a basıyoruz. Diğer açtığınız command prompt larıda exit yazarak kapatınız.
  8. SSMS kullanarak sa ile server a bağlantı kurabildiğimizi görüyoruz.
  9. 1 nolu adımda log on da windows account u kullanmıştık. Bunu tekrar eski haline getiriyoruz.
  10. SQL Server Service ini restart ediyoruz.

Bu adımlardan sonra SQL Server sa şifresini değiştirmiş oluyoruz.

İşinize yaraması umuduyla.

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server da sistemde yapılan işlemleri trace etmek için kullanılan SQL Server Profiler ı kullanabilmek için kullanan user ın sysAdmin role üne yada Grant Trace yetkisine ihtiyacı vardır. Firmanızda bulunan developer ların sistemi trace etmek ihtiyacı sürekli gelecektir. Bu durumlarda developer lara sysAdmin role ü vermek DBA lerin istemediği bir durumdur. Verilecek ortam production ortamı olmasa bile.

SQL Server 2000 de profiler açmak için tek yetki imkanı sysAdmin iken SQL Server 2005 ile beraber gelen Grant Trace yetkisi ile sysAdmin vermeden bu isteği karşılayabiliyoruz.

GRANT ALTER TRACE TO [domain\username]
GO

 

Bu yazımda ise bir user a sysAdmin ve Grant Trace hakkı vermeden SQL Server Profiler çalıştırma hakkının nasıl verileceğini anlatacağım.

[more]

İlk olarak trace açmak isteyen userda gerekli yetkiler yoksa aldığı hataya bakalım.

image

In order to run a trace against SQL Server you must be a member of sysadmin fixed server role or have the ALTER TRACE permission.

Şimdi ise sysAdmin role ü veya Grant Trace yetkisi vermeden nasıl profiler açtırabileceğimize bakalım.

Aslında mantık çok basit. sysAdmin role üne sahip yeni bir user açacağız. Bu user ı kullanarak SQL Server Profiler ı başlatacak bir bat dosyası hazırlayacağız. Daha sonrada bu bat dosya okunamasın diye exe ye dönüştüreceğiz.

  1. Yeni bir user create ediyoruz. Profiler ı bu user ile çalıştıracağız.
    CREATE LOGIN [profiler_user] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    
    EXEC sys.sp_addsrvrolemember @loginame = N'profiler_user', @rolename = N'sysadmin'
    GO
  2. Exe oluşturmak için http://www.abyssmedia.com/downloads/quickbfc.exe adresindeki Quick Batch File Compiler uygulamasını download edip kuruyoruz. Bu uygulama paralı olmasına rağmen ücretsiz de kullanılabilmekte.
  3. Quick Batch File Compiler uygulamasını açıyoruz.
  4. Source kısmına SQL Server profiler ı çalıştıracak olan aşağıdaki kodu yazıp Build tuşuna basarak exe yi oluşturuyoruz. Server adını kendi sisteminize göre değiştirmelisiniz.

    profiler /S sahtiyan_tu /U profiler_user /P password /T "standard(default)"

    Profiler ın command prompt tan çalıştırılması ile alakalı aşağıdaki yazıyı okumanızı tavsiye ederim. http://msdn.microsoft.com/en-us/library/ms162808.aspx

    image

Bu oluşturduğumuz exe yi kullanarak SQL Server Profiler ı açabiliriz.

image

Download ettiğimiz exe yi ücretsiz kullandığımız için bir uyarı ekranı gelecektir. Bu ekranda herhangi bir tuşa basıp geçiyoruz.

image

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bu yazımda bir database in collation ın nasıl değiştirileceğini anlatıyor olacağım. Bu işleme başlamadan önce ilk olarak genel hatlarıyla collation nedir diye bakalım.

Collation, SQL Server da character set anlamına gelmektedir. Eşitliklerde, order işleminde, büyük küçük harf ayrımında karakterlerin hangi mantıkta kullanılacağını belirler.

Örneğin genelde Türkçe database lerde kullanılan TURKISH_CI_AS collation ında ki CI ifadesi Case Insensitive(Büyük küçük harf ayrımı olmasın) anlamına gelmektedir.

Collation ın ne şekilde bir fark oluşturduğunu görmek için aşağıdaki örneklere bakalım.

if upper('filiz') = 'FİLİZ' collate Turkish_CI_AS select 'eşit' else select 'eşit deðil'
if upper('filiz') = 'FILIZ' collate Turkish_CI_AS select 'eşit' else select 'eşit deðil'
if upper('filiz') = 'FİLİZ' collate SQL_Latin1_General_CP1_CI_AS select 'eşit' else select 'eşit değil'
if upper('filiz') = 'FILIZ' collate SQL_Latin1_General_CP1_CI_AS select 'eşit' else select 'eşit değil'

 

Gördüğünüz gibi Türkçe de I ve i birbirinden farklı 2 karakter iken ingilizce de bu 2 karakter birbirinin aynısıdır.

SQL Server da 3 level da collation verilebilmektedir.

  • Server Collation
  • Database Collation
  • Column Collation
  • Expression Collation

Server ın collation ın ne olduğuna bakmak için aşağıdaki script i kullanabilirsiniz.

SELECT SERVERPROPERTY('collation')

 

Yada SSMS üzerinden Server>>Properties ekranından bakabilirsiniz.

image

Database collation ınlarına sys.databases system table ından bakabilirsiniz.

select collation_name,* from sys.databases

 

Yada SSMS üzerinden Database>>Properties ekranından bakabilirsiniz.

image

Bu ön bilgilerden sonra şimdi DB collation nasıl değiştirelir konumuza geri dönelim.

İlk olarak şunu sorgulamak gerekiyor. Bir DB nin collation ınını niye değiştirmek zorunda olalım.

Örneğin Server collation ını Turkish_CI_AS olsun, bununla beraber TempDB collation ınada Turkish_CI_AS olduğunu düşünelim. İşlem yapmak istediğimiz DB de SQL_Latin1_General_CP1254_CI_AS olsun.

Bu DB üzerinde TempDB kullanacak herhangi bir sorgu çalıştırdığımızda (Where clause, order vb) script aşağıdaki hatayı verecektir.

Select *
from dbo.Table1 t1, dbo.Table2 t2
where t1.Column1 = t2.Column2

 

Msg 468, Level 16, State 9, Procedure ET_Update_Corporate_Customer_Limits, Line 41
Cannot resolve the collation conflict between "Turkish_CI_AS" and "SQL_Latin1_General_CP1254_CI_AS" in the equal to operation.

Where clause collation key i kullanarak bu hatayı aşmak mümkün.

Select *
from dbo.Table1 t1, dbo.Table2 t2
where t1.Column1 COLLATE TURKISH_CI_AS = t2.Column2 COLLATE TURKISH_CI_AS

 

Ama böyle bir kullanım bütün query lerde değişiklik yapmak gerektiğinden pek kullanışlıu değildir. Ayrıca bu kullanımda table scan yapıldığından dolayı performans olarakta tavsiye edilmez.

Dolayısıyla bizim ihtiyacımız olan DB nin collation ını değiştirmektir.

Eğer column bazında collation kullanılmadıysa aşağıdaki script i uygulamanız collation ı değiştirmek için yeterlidir.

ALTER DATABASE SampleDB COLLATE TURKISH_CI_AS

 

Lakin eğer column collation kullanıldıysa iş biraz daha karmaşık hale gelmektedir. Yapılması gereken adımlar sırayla aşağıdaki gibidir.

  1. Constraint leri drop et
  2. Index leri drop et
  3. DB nin collation ını değiştir.
  4. Column collation ları değiştir. Alter column
  5. Index leri create et
  6. Constraint leri create et.

Şimdi bu işlemleri hızlıca nasıl yaparız bunu görüyor olacağız.

1.Constraint Drop

Constraint leri drop etmeden önce daha sonra create edebilmek için sistemde ki constraint lerin create scriptleri generate etmemiz gerekiyor.

Bunun için daha önce yazmış olduğum Constraint ler için DROP ve CREATE DDL Scriptleri Generate Etme makalesinde ki SP yi kullanacağız.

Bu SP yi kullanarak aşağıdaki script aracılığıyla constrate create scriptlerini generate ediyoruz.

Use SampleDB
GO
create table #CreateScripts (ID INT , Constraint_Type VARCHAR(100), SQL VARCHAR(8000), Column_Name SYSNAME DEFAULT '')

declare @TableName sysname
declare curs cursor for
select name from sys.objects where type='U'
open curs
fetch next from curs into @TableName
while (@@fetch_status = 0)
begin
  insert #CreateScripts 
     EXEC usp_Create_Table_DDLs @sTable_Name = @TableName, @Create_Table_Ind = 0, @PK_Ind = 1, @FK_Ind = 1, @Check_Ind = 1, @Default_Ind = 1 
fetch next from curs into @TableName
end
close curs
deallocate curs

declare @sql varchar(max)=''
select @sql+=sql+char(10) from #CreateScripts
print @sql

drop table #CreateScripts

 

Messages kısmındaki sonlara doğru Alter ile başlayan satırların tamamını alıp kaydedin. Daha sonra bu scriptler ile constraint leri tekrar create edeceğiz.

Evet şimdi constraint leri drop edebiliriz. Bunun için aşağıdaki script i execute ediniz.

use SampleDB
declare @sql varchar(max)=''
select @sql+='ALTER TABLE ['+tab.name+'] DROP CONSTRAINT ['+cons.name+']; '+char(10)
from sys.objects cons,sys.objects tab
where cons.type in ('C', 'F', 'PK', 'UQ', 'D')
and cons.parent_object_id=tab.object_id and tab.type='U'
order by cons.type
exec(@sql)

 

2.Index Drop

Constraint te yaptığımız gibi Index te de drop etmeden önce generate scriptleri hazırlamamız gerekiyor ki daha sonra create edebilelim. Create script generate için aşağıdaki script i kullanabilirsiniz.

DECLARE @TabName varchar(100)=NULL
DECLARE @tableName varchar(100)
DECLARE TCur CURSOR FOR
SELECT '['+SCHEMA_NAME(t.schema_id)+'].['+t.name+']' FROM sys.tables t WHERE exists(SELECT TOP 1 1 FROM sys.indexes WHERE object_id=t.object_id and index_id>0)
AND (t.object_id=OBJECT_ID(@TabName) OR @TabName is null)
OPEN TCur
FETCH FROM TCur INTO @tableName
WHILE @@FETCH_STATUS=0
BEGIN
      DECLARE  ICur CURSOR FOR
      SELECT name,is_primary_key from sys.indexes i WHERE  exists(SELECT TOP 1 1 FROM sys.index_columns ic
      WHERE i.object_id=ic.object_id and i.index_id=ic.index_id) and
      i.object_id=OBJECT_ID(@tableName)
      OPEN ICur
      DECLARE @IName VARCHAR(100),@IsPK BIT,@SQL VARCHAR(MAX),@CName varchar(100),@is_descending_key bit
      FETCH FROM ICur INTO @IName,@IsPK
      WHILE @@FETCH_STATUS=0
      BEGIN
            IF(@IsPK=1)
            BEGIN
                  SET @SQL='ALTER TABLE '+@tableName+' ADD PRIMARY KEY'+CHAR(10)+'('+CHAR(10)
                  DECLARE CCur CURSOR FOR
                  SELECT COL_NAME(i.object_id,ic.column_id),ic.is_descending_key FROM sys.indexes i inner join sys.index_columns ic
                  ON i.object_id=ic.object_id and i.index_id=ic.index_id
                  WHERE i.object_id=OBJECT_ID(@tableName) and i.name=@IName
                  OPEN CCur
                  FETCH FROM CCur INTO @CName,@is_descending_key
                  SET @SQL+='['+@CName+'] '+CASE WHEN @is_descending_key=0 THEN 'ASC' ELSE 'DESC' END+CHAR(10) 
                  FETCH NEXT FROM CCur INTO @CName,@is_descending_key
                  WHILE @@FETCH_STATUS=0
                  BEGIN
                        SET @SQL+=',['+@CName+'] '+CASE WHEN @is_descending_key=0 THEN 'ASC' ELSE 'DESC' END+CHAR(10)     
                        FETCH NEXT FROM CCur INTO @CName,@is_descending_key
                  END
                  CLOSE CCur
                  DEALLOCATE CCur
                  SET @SQL+=');'
                  PRINT @SQL
            END
            ELSE
            BEGIN 
                  SET @SQL='CREATE '+(SELECT  type_desc FROM sys.indexes WHERE object_id=OBJECT_ID(@tableName) and name=@IName)+' INDEX ['+@IName+'] ON '+@tableName+CHAR(10)+'('+CHAR(10)
                  DECLARE CCur CURSOR FOR
                  SELECT COL_NAME(i.object_id,ic.column_id),ic.is_descending_key FROM sys.indexes i inner join sys.index_columns ic
                  ON i.object_id=ic.object_id and i.index_id=ic.index_id
                  WHERE i.object_id=OBJECT_ID(@tableName) and i.name=@IName
                  OPEN CCur
                  FETCH FROM CCur INTO @CName,@is_descending_key
                  SET @SQL+='['+@CName+'] '+CASE WHEN @is_descending_key=0 THEN 'ASC' ELSE 'DESC' END+CHAR(10) 
                  FETCH NEXT FROM CCur INTO @CName,@is_descending_key
                  WHILE @@FETCH_STATUS=0
                  BEGIN
                        SET @SQL+=',['+@CName+'] '+CASE WHEN @is_descending_key=0 THEN 'ASC' ELSE 'DESC' END+CHAR(10)     
                        FETCH NEXT FROM CCur INTO @CName,@is_descending_key
                  END
                  CLOSE CCur
                  DEALLOCATE CCur
                  SET @SQL+=');'
                  PRINT @SQL
            END
            FETCH NEXT FROM ICur INTO @IName,@IsPK
      END
      CLOSE ICur
      DEALLOCATE ICur
      FETCH NEXT FROM TCur INTO @tableName
END
CLOSE TCur
DEALLOCATE TCur

 

Messages kısmındaki yazıları kopyalayıp kaydedelim. Daha sonra create index adımında bu create script ini kullanacağız.

Create script i hazır olduğuna göre artık index leri drop edebiliriz.

Bunun için aşağıdaki script i kullanabilirsiniz.

declare @str varchar(max)=''
select @str += 'DROP INDEX ['+i.name +'] ON ['+schema_name(t.schema_id)+'].['+t.name+']; '+CHAR(10)
from sys.indexes i
left join sys.objects t on t.object_id=i.object_id
where t.type='u' and i.index_id>0
exec(@str)

 

3.Change DB Collation

DB nin collation ını değiştirmek için aşağıdaki script i kullanabilirsiniz.

ALTER DATABASE SampleDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE SampleDB COLLATE TURKISH_CI_AS
ALTER DATABASE SampleDB SET MULTI_USER

 

4.Change Columns Collation

Bu adımda DB de default collation kullanmayan column ların collation ını değiştireceğiz. Hangi column ları değiştireceğimizi bulmak için aşağıdaki script i kullanabilirsiniz.

-------------------------------------------------------------
--- LM_ChangeCollation - Change collation in all tables
--- made by Luis Monteiro - ljmonteiro@eurociber.pt 
--- modified by wilfred van dijk - wvand@wilfredvandijk.nl
-------------------------------------------------------------
DECLARE @new_collation varchar(100)
DECLARE @debug bit
DECLARE
	@table sysname,
	@previous sysname,
	@column varchar(60),
	@type varchar(20),
	@legth varchar(4),
	@nullable varchar(8),
	@sql varchar(8000),
	@msg varchar(8000),
	@servercollation varchar(120)
/*
uncomment one of the following lines:
*/
set @new_collation = convert(sysname, databasepropertyex(DB_NAME(), 'collation'))
--- set @new_collation = convert(sysname, serverproperty('collation'))
/*
@debug = 0 to execute
*/
set @debug = 1
if @new_collation is null
begin
	print 'which collation?'
	goto einde
end

DECLARE C1 CURSOR FOR
select 'Table' = b.name,
	'Column' = a.name,
	'Type' = type_name(a.system_type_id),
	'Length' = a.max_length,
	'Nullable' = case when a.is_nullable = 0 then 'NOT NULL' else ' ' end
from sys.columns a
join sysobjects b on a.object_id = b.id
where b.xtype = 'U'
	and b.name not like 'dt%'
	and type_name(a.system_type_id) in ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
	and a.[collation_name] <> @new_collation
order by b.name,a.column_id

OPEN C1
FETCH NEXT
FROM C1
INTO @table,@column,@type,@legth,@nullable
set @previous = @table
WHILE @@FETCH_STATUS = 0
BEGIN
	if @table <> @previous print ''
	set @sql = 'ALTER TABLE ' + QUOTENAME(@table) + ' ALTER COLUMN ' + QUOTENAME(@column) + ' '
	set @sql = @sql + @type + '(' + @legth + ')' + ' COLLATE ' + @new_collation + ' ' + @nullable
	print @SQL
	if @debug = 0 
	begin
		begin try
			EXEC (@sql)
		end try
		begin catch
			print 'ERROR:' + ERROR_MESSAGE()
			print '' 
		end catch
	end
	set @previous = @table
FETCH NEXT
FROM C1
INTO @table,@column,@type,@legth,@nullable
END
CLOSE C1
DEALLOCATE C1
einde:

 

Bu script sonucununda çıkan alter script lerini kullanarak kolonları alter ederek collation larını değiştiriyoruz.


5.Constraint ReCreate

1 numaralı adımda oluşturduğumuz constraint create script ini kullanarak constraint leri tekrar create ediyoruz.


6. Index ReCreate

2 numaralı adımda oluşturduğumuz index create scriptini kullanarak index leri tekrar create ediyoruz.

Bu adımlardan sonra eğer hiç bir hata almadıysanız DB nin script i değişmiş olması gerekiyor. Kontrol etmek için 4 nolu adımdaki select işlemini tekrarlayabilirsiniz. Bu sorgu sonucunda kayıt dönmemesi gerekiyor.


İyi çalışmalar.

Turgay Sahtiyan


Kaynaklar:

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bugün size SQLServerCentral.com da rastladığım 2 SP den bahsetmek istiyorum.

1. script ile DB de bulunan table lar için create scriptleri ve constraint create scriptleri generate edebilirsiniz. Bunları drop ettikten sonra bu script i kullanarak tekrar create edebilirsiniz.

2.script ile ise gene table ve bu table lara ait constraint leri drop etme scriptlerini generate edebilirsiniz.

Ben bu 2 SP yi bin sonraki yazım olan Change DB Collaction (Database Collaction ını Değiştirme) makalemde kullanacağım.

Table ve Constraint leri Create etmek için DDL Script Generate Etme


SP nin create script i aşağıdaki gibidir. Bu script i AdventureWorks DB sinde create edelim

CREATE PROCEDURE [dbo].[usp_Create_Table_DDLs]
    (
        @sTable_Name        SYSNAME,
        @Create_Table_Ind    BIT = 1,
        @PK_Ind                BIT = 1,
        @FK_Ind                BIT = 1,
        @Check_Ind            BIT = 1,
        @Default_Ind        BIT = 1
    )
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @Schema_Name        SYSNAME,
            @UniqueConstraints    BIT = 1,
            @sStr                VARCHAR(MAX)

    SELECT    @Schema_Name = SCHEMA_NAME(schema_id)
    FROM    sys.objects
    WHERE    name = @sTable_Name

    IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#PKObjectID'))    DROP TABLE #PKObjectID
    IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#Uniques'))        DROP TABLE #Uniques
    IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#Constraints'))    DROP TABLE #Constraints
    IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#ShowFields'))    DROP TABLE #ShowFields

    CREATE TABLE #Constraints (ID INT IDENTITY, Constraint_Type VARCHAR(100), SQL VARCHAR(8000), Column_Name SYSNAME DEFAULT '')

    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------

    -- Create table
    IF @Create_Table_Ind = 1
    BEGIN
        SELECT    FieldID                    = IDENTITY(INT,1,1),
                DatabaseName            = DB_NAME(),
                TableOwner                = TABLE_SCHEMA,
                TableName                = TABLE_NAME,
                FieldName                = COLUMN_NAME,
                ColumnPosition            = CAST(ORDINAL_POSITION AS INT),
                ColumnDefaultValue        = COLUMN_DEFAULT,
                ColumnDefaultName        = dobj.name,
                IsNullable                = CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
                DataType                = DATA_TYPE,
                MaxLength                = CAST(CHARACTER_MAXIMUM_LENGTH AS INT),
                NumericPrecision        = CAST(NUMERIC_PRECISION AS INT),
                NumericScale            = CAST(NUMERIC_SCALE AS INT),
                DomainName                = DOMAIN_NAME,
                FieldListingName        = COLUMN_NAME + ',',
                FieldDefinition            = '',
                IdentityColumn            = CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END,
                IdentitySeed            = CAST(ISNULL(ic.seed_value,0) AS INT),
                IdentityIncrement        = CAST(ISNULL(ic.increment_value,0) AS INT),
                IsCharColumn            = CASE WHEN DATA_TYPE NOT IN ('TEXT') AND st.collation_name IS NOT NULL THEN 1 ELSE 0 END
        INTO    #ShowFields
        FROM    INFORMATION_SCHEMA.COLUMNS            c
                JOIN sys.columns                    sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
                LEFT JOIN sys.identity_columns        ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
                JOIN sys.types                        st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
                LEFT OUTER JOIN sys.objects            dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'
        WHERE    c.TABLE_NAME = @sTable_Name
        ORDER    BY c.TABLE_NAME, c.ORDINAL_POSITION

        
        SELECT    @sStr = 'CREATE TABLE ' + QUOTENAME(@Schema_Name) + '.' + QUOTENAME(@sTable_Name) + '('

        SELECT    @sStr = @sStr + 
                CHAR(10) + CHAR(9) + QUOTENAME(FieldName) + ' ' +
                    CASE
                        WHEN DomainName IS NOT NULL THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
                        ELSE UPPER(DataType) 
                                + CASE WHEN IsCharColumn = 1 OR DataType IN ('Varbinary') THEN '(' + CASE WHEN MaxLength = -1 THEN 'MAX' ELSE CAST(MaxLength AS VARCHAR(10)) END + ')' ELSE '' END 
                                + CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END 
                                + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END 
                                --+ CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END
                    END + 
                    CASE WHEN FieldID = (SELECT MAX(FieldID) FROM #ShowFields) THEN '' ELSE ',' END
        FROM #ShowFields
        
        SELECT    @sStr = @sStr + ')'
                
        INSERT    INTO #Constraints (Constraint_Type, SQL)
        VALUES    ('CREATE_TABLE', @sStr)            
    END
    
    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------
    
    IF @PK_Ind = 1
    BEGIN
        PRINT 'Creating SQL for PK and Unique Constraints ...'

        -- Get Object ID of the PK
        SELECT    DISTINCT ObjectID = cco.object_id
        INTO    #PKObjectID
        FROM    sys.key_constraints            cco
                JOIN sys.index_columns        cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
                JOIN sys.indexes            i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
        WHERE    OBJECT_NAME(parent_object_id) = @sTable_Name 
        AND        i.type = 1 
        AND        is_primary_key = 1

        ---------------------------------------------------------------------------

        -- Get Object ID of the Uniques
        SELECT    DISTINCT ObjectID = cco.object_id
        INTO    #Uniques
        FROM    sys.key_constraints            cco
                JOIN sys.index_columns        cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
                JOIN sys.indexes            i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
        WHERE    OBJECT_NAME(parent_object_id) = @sTable_Name 
        AND        i.type = 2 
        AND        is_primary_key = 0 
        AND        is_unique_constraint = 1 

        ---------------------------------------------------------------------------

        INSERT    INTO #Constraints (Constraint_Type, SQL)
        SELECT    'PK_UNIQUE_CONSTRAINT',
                [PK_UNIQUE_CONSTRAINTS] = ISNULL('ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(@sTable_Name) + ' ADD CONSTRAINT ' 
                    + QUOTENAME(cco.name )
                    + CASE    type WHEN 'PK' THEN ' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN 'NONCLUSTERED ' ELSE 'CLUSTERED ' END
                                 WHEN 'UQ' THEN ' UNIQUE ' 
                     END 
                    + CASE    WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END 
                    + '(' + REVERSE(SUBSTRING(REVERSE((
                                                        SELECT    c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
                                                        FROM    sys.key_constraints            ccok
                                                                LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
                                                                LEFT JOIN sys.columns        c ON cc.object_id = c.object_id AND cc.column_id = c.column_id
                                                                LEFT JOIN sys.indexes        i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
                                                        WHERE    i.object_id = ccok.parent_object_id 
                                                        AND        ccok.object_id = cco.object_id
                                                        FOR        XML PATH('')
                                                     )
                                                     ), 2, 8000)) + ')', '')
        FROM    sys.key_constraints            cco
                INNER JOIN sys.schemas        s ON cco.schema_id = s.schema_id
                LEFT JOIN #PKObjectID        pk ON cco.object_id = pk.ObjectID
                LEFT JOIN #Uniques            u ON cco.object_id = u.objectID
        WHERE    OBJECT_NAME(cco.parent_object_id) = @sTable_Name
        AND        (type = 'PK'
        OR         type = CASE WHEN @UniqueConstraints = 1 THEN 'UQ' ELSE 'PK' END
                )
        --AND        s.name = @TableSchema
    END

    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------

    IF @FK_Ind = 1
    BEGIN
        PRINT 'Creating SQL for FK Constraints ...'

        -- Create all FKs for a table
        INSERT    INTO #Constraints (Constraint_Type, SQL)
        SELECT    'FK_CONSTRAINT',
                [FK_CONSTRAINTS] = 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' WITH CHECK ADD CONSTRAINT ' + QUOTENAME(a.name) + ' FOREIGN KEY (' + a.ParentColumns + ') REFERENCES ' + QUOTENAME(a.ReferencedSchema) +'.' + QUOTENAME(a.ReferencedObject) + '(' + a.ReferencedColumns + ')'
        FROM
            (
                SELECT    fk.OBJECT_ID as object_id,
                        ReferencedSchema    = SCHEMA_NAME(o.Schema_ID),
                        ReferencedObject    = OBJECT_NAME(fk.referenced_object_id), 
                        ParentObject        = OBJECT_NAME(fk.parent_object_id),
                        Name                = fk.name,
                        ParentColumns        = REVERSE(SUBSTRING(REVERSE((
                                                    SELECT    cp.name + ','
                                                    FROM    sys.foreign_key_columns fkc
                                                            JOIN sys.columns        cp    ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
                                                    WHERE    fkc.constraint_object_id = fk.object_id
                                                    FOR        XML PATH('')
                                                 )
                                                 ), 2, 8000)),
                        ReferencedColumns    = REVERSE(SUBSTRING(REVERSE((
                                                    SELECT    cr.name + ','
                                                    FROM    sys.foreign_key_columns fkc
                                                            JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
                                                    WHERE    fkc.constraint_object_id = fk.object_id
                                                    FOR        XML PATH('')
                                                 )
                                                 ), 2, 8000)) 
                FROM    sys.foreign_keys fk
                        INNER JOIN sys.objects o ON fk.referenced_object_id = o.object_id
            ) a
            INNER JOIN sys.objects    co ON a.object_id = co.object_id            
            INNER JOIN sys.objects    o ON co.parent_object_id = o.object_id
            INNER JOIN sys.schemas    s ON o.schema_id = s.schema_id
        WHERE a.ParentObject = @sTable_Name
        ORDER BY a.name
    END

    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------

    IF @Check_Ind = 1
    BEGIN
        PRINT 'Creating SQL for Check Constraints ...'

        -- Create check constraints for all the columns of a table
        INSERT    INTO #Constraints (Constraint_Type, SQL)
        SELECT    'CHECK_CONSTRAINT',
                [CHECK_CONSTRAINTS] = 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' WITH CHECK ADD CHECK ' + cc.definition + ';'
        FROM    sys.check_constraints cc
                INNER JOIN sys.objects co ON cc.object_id = co.object_id
                INNER JOIN sys.objects o ON co.parent_object_id = o.object_id
                INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE    OBJECT_NAME(cc.parent_object_id) = @sTable_Name
        ORDER    BY o.name
    END

    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------

    IF @Default_Ind = 1
    BEGIN
        PRINT 'Creating SQL for Default Constraints ...'
            
        -- Create defaults for all the columns of a table
        INSERT    INTO #Constraints (Constraint_Type, Column_Name, SQL)
        SELECT    'DEFAULT_CONSTRAINT',
                Column_Name = c.name,
                [DEFAULT_CONSTRAINTS] = 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' ADD DEFAULT ' + sc.text + ' FOR ' + c.name
        FROM    syscomments    sc
                INNER JOIN syscolumns c ON sc.id = c.cdefault
                INNER JOIN sys.objects o ON c.id = o.object_id
                INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE    sc.id    IN    (
                                SELECT    cdefault 
                                FROM    syscolumns
                                WHERE    id = OBJECT_ID(@Schema_Name + '.' + @sTable_Name) 
                                AND        cdefault > 0
                            )
        ORDER    BY c.name
    END

    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------
    

    SELECT    * 
    FROM    #Constraints
    WHERE    (1 = 1
    OR         Constraint_Type = CASE WHEN @Default_Ind = 1    THEN 'DEFAULT_CONSTRAINT'        ELSE '' END
    OR         Constraint_Type = CASE WHEN @Check_Ind = 1        THEN 'CHECK_CONSTRAINT'            ELSE '' END
    OR         Constraint_Type = CASE WHEN @PK_Ind = 1        THEN 'PK_UNIQUE_CONSTRAINT'        ELSE '' END
    OR         Constraint_Type = CASE WHEN @FK_Ind = 1        THEN 'FK_CONSTRAINT'            ELSE '' END
            )
    ORDER    BY ID

    ---------------------------------------------------------------------------

    PRINT 'Complete.'
END
-- EXEC usp_Create_Table_DDLs @sTable_Name = 'MY_TABLE', @Create_Table_Ind = 1, @PK_Ind = 1, @FK_Ind = 1, @Check_Ind = 1, @Default_Ind = 1

GO

 

Kullanımına bakacak olursak;

Örneğin AdventureWorks DB sinde bulunan Sales.SalesOrderDetail tablosu için create script generate etmek istersek aşağıdaki kodu kullanabiliriz.

EXEC usp_Create_Table_DDLs 
	@sTable_Name = 'SalesOrderDetail'
	, @Create_Table_Ind = 1
	, @PK_Ind = 1
	, @FK_Ind = 1
	, @Check_Ind = 1
	, @Default_Ind = 1

 

Parametrelerde ki 1 value ları scriptlerin generate edileceğini gösteriyor. 0 verirseniz o parametre ile alakalı scriptler generate edilmeyecektir.


Table ve Constraint leri Drop etmek için DDL Script Generate Etme


SP nin create script i aşağıdaki gibidir. Bu script i AdventureWorks DB sinde create edelim.

CREATE PROCEDURE [dbo].[usp_Drop_Table_Constraints]
    (
        @sConstraint_Type    VARCHAR(50),        -- PK, FK, CK, DF
        @sDB_Name            SYSNAME,
        @sTable_Name        SYSNAME,
        @sColumn_Name        SYSNAME = NULL
    )
AS
BEGIN
    SET NOCOUNT ON
    
    DECLARE    @sSQL            VARCHAR(8000),
            @sStr            VARCHAR(1000)
    
    CREATE TABLE #Temp (
        Type                VARCHAR(50),
        DBName                SYSNAME,
        Schema_Name            SYSNAME,
        Table_Name            SYSNAME,
        Column_Name            SYSNAME DEFAULT '',
        Constraint_Name        SYSNAME,
        DROP_SQL            VARCHAR(8000)
    )

    -------------------------------------------------------------
    
    IF @sConstraint_Type = 'PK'
    BEGIN
        -- Drop PK
        SET @sSQL = '    
                        SELECT    DISTINCT Type        = ''PK'',
                                DBName                = ''' + @sDB_Name + ''',
                                Schema_Name            = s.name,
                                Table_Name            = p.name,
                                PK_Constraint_Name    = o.name,
                                DROP_SQL            = ''ALTER TABLE ' + @sDB_Name + '.'' + s.Name + ''.'' + c.Table_Name + '' DROP CONSTRAINT '' + c.CONSTRAINT_NAME
                        FROM    ' + @sDB_Name + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
                                INNER JOIN ' + @sDB_Name + '.sys.objects o ON c.CONSTRAINT_NAME = o.name
                                INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
                        WHERE    c.TABLE_NAME = ''' + @sTable_Name + '''
                        AND        o.type = ''PK''
                    '
                    
        INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Constraint_Name, DROP_SQL)
        EXEC (@sSQL)
    END

    -------------------------------------------------------------

    ELSE IF @sConstraint_Type = 'FK'
    BEGIN
        -- Drop FK
        SET @sSQL = '
                        SELECT    DISTINCT Type        = ''FK'',
                                DBName                = ''' + @sDB_Name + ''',
                                Schema_Name            = s.name,
                                Table_Name            = p.name,
                                FK_Constraint_Name    = o.name,
                                DROP_SQL            = ''ALTER TABLE ' + @sDB_Name + '.'' + s.Name + ''.'' + c.Table_Name + '' DROP CONSTRAINT '' + c.CONSTRAINT_NAME
                        FROM    ' + @sDB_Name + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
                                INNER JOIN ' + @sDB_Name + '.sys.objects o ON c.CONSTRAINT_NAME = o.name
                                INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
                        WHERE    c.TABLE_NAME = ''' + @sTable_Name + '''
                        AND        o.type = ''F''
                    '

        INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Constraint_Name, DROP_SQL)
        EXEC (@sSQL)
    END

    -------------------------------------------------------------
    
    ELSE IF @sConstraint_Type = 'CK'
    BEGIN
        -- Drop Check Constraint
        SET @sSQL = '    
                        SELECT    DISTINCT Type        = ''CK'',
                                DBName                = ''' + @sDB_Name + ''',
                                Schema_Name            = s.name,
                                Table_Name            = p.name,
                                Column_Name            = c.column_name,
                                CK_Constraint_Name    = o.name,
                                DROP_SQL            = ''ALTER TABLE ' + @sDB_Name + '.'' + s.Name + ''.'' + c.Table_Name + '' DROP CONSTRAINT '' + c.CONSTRAINT_NAME
                        FROM    ' + @sDB_Name + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
                                INNER JOIN ' + @sDB_Name + '.sys.objects o ON c.CONSTRAINT_NAME = o.name
                                INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
                        WHERE    c.TABLE_NAME = ''' + @sTable_Name + '''
                        AND        o.type = ''C''
                    '
        INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Column_Name, Constraint_Name, DROP_SQL)
        EXEC (@sSQL)
    END

    -------------------------------------------------------------

    ELSE IF @sConstraint_Type = 'DF'
    BEGIN
        -- Drop Default Constraint
        SET @sSQL = '
                        SELECT    DISTINCT Type        = ''DF'',
                                DBName                = ''' + @sDB_Name + ''',
                                Schema_Name            = s.name,
                                Table_Name            = p.name,
                                Column_Name            = c.name,
                                DF_Constraint_Name    = o.name,
                                DROP_SQL            = ''ALTER TABLE ' + @sDB_Name + '..'' + p.name + '' DROP CONSTRAINT '' + o.name 
                        FROM    ' + @sDB_Name + '.sys.columns c 
                                INNER JOIN ' + @sDB_Name + '.sys.objects o on c.default_object_id = o.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
                        WHERE    o.type = ''D'' 
                        AND        c.object_id = object_id(''' + @sDB_Name + '..' + @sTable_Name + ''')
                    '
        INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Column_Name, Constraint_Name, DROP_SQL)
        EXEC (@sSQL)
    END

    -------------------------------------------------------------
    
    SELECT    *
    FROM    #Temp

    -------------------------------------------------------------
    
END

/*

EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'PK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'    -- Drop PK
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'FK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'    -- Drop FKs
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'CK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'    -- Drop Check Constraints
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'DF', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'    -- Drop Default Constraints

*/

GO

 

Kullanımına bakacak olursak;

Örneğin AdventureWorks DB sinde bulunan Sales.SalesOrderDetail tablosundaki Primary Key constraint ler için drop script generate etmek istersek aşağıdaki kodu kullanabiliriz.

EXEC usp_Drop_Table_Constraints 
	@sConstraint_Type = 'PK'
	, @sDB_Name = 'AdventureWorks'
	, @sTable_Name = 'SalesOrderDetail'

 

Gene aynı şekilde Foreign Key(FK),Check(CK) ve Default(DF) Constraint leri drop etmek için aşağıdaki scriptleri kullanabilirsiniz.

-- Drop FKs
EXEC usp_Drop_Table_Constraints 
	@sConstraint_Type = 'FK'
	, @sDB_Name = 'AdventureWorks'
	, @sTable_Name = 'SalesOrderDetail'
-- Drop Check Constraints
EXEC usp_Drop_Table_Constraints 
	@sConstraint_Type = 'CK'
	, @sDB_Name = 'AdventureWorks'
	, @sTable_Name = 'SalesOrderDetail'
-- Drop Default Constraints
EXEC usp_Drop_Table_Constraints 
	@sConstraint_Type = 'DF'
	, @sDB_Name = 'AdventureWorks'
	, @sTable_Name = 'SalesOrderDetail'

 

Daha önce bahsettiğim gibi bu 2 SP yi Change DB Collaction (Database Collaction ını Değiştirme) makalemde kullanacağım. O makalemde görüşmek üzere.

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan