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
<<  Ekim 2017  >>
PaSaÇaPeCuCuPa
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345
Keywords

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


http://www.turgaysahtiyan.com/post/SQL-Server-e28093-SP%28Stored-Procedure%29-lere-Topluca-Yetki-Verme.aspx adresindeki yazımda Stored Procedure lere topluca nasıl yetki verilebileceğini anlatmıştım. Şimdi ise geçenlerde yazdığım bir başka SP den bahsedeğim. Bu SP ile sadece SP lere değil server da ki tüm objelere (table,function,view vs) topluca yetki verebilirsiniz.

SP şu şekilde.

CREATE PROC GivePermissionForAllObjects
	@UserName varchar(max) = '' --Yetki verilmek istenen user ya da role
	,@Databases varchar(Max) = '' --Yetki verilmek istenen DB ya da DB ler, Boş bırakılırsa bütün DB ler
	,@GrantDeny varchar(Max) = 'DENY' --GRANT, DENY, REVOKE
	,@PermissionType varchar(Max) = 'EXECUTE' --Alter, Execute, View Definition vs.
	,@ObjectType varchar(2)='' --SP,View,Function vs.
AS
if (isnull(@ObjectType,'')='') or (isnull(@ObjectType,'')='?') begin
print '
--Object Types
--FN	SQL_SCALAR_FUNCTION
--C 	CHECK_CONSTRAINT
--UQ	UNIQUE_CONSTRAINT
--SQ	SERVICE_QUEUE
--U 	USER_TABLE
--D 	DEFAULT_CONSTRAINT
--PK	PRIMARY_KEY_CONSTRAINT
--V 	VIEW
--S 	SYSTEM_TABLE
--IT	INTERNAL_TABLE
--P 	SQL_STORED_PROCEDURE
--TF	SQL_TABLE_VALUED_FUNCTION
--TR	SQL_TRIGGER
--PC	CLR_STORED_PROCEDURE
--
'
Return
end
SET NOCOUNT ON
--variables
declare @DatabasesTmp varchar(max)=@Databases
declare @DBName sysname,@ProcName sysname,@SchemaName sysname
declare @sql varchar(max)
Declare @Tbl_DB Table(DB varchar(Max))
Create Table ##Prodecures(DBName sysname, ProcName sysname, SchemaName sysname)
--

--Split Databases
if @DatabasesTmp<>'' begin
	while @DatabasesTmp <> '' begin
		if CHARINDEX(',',@DatabasesTmp)>0 begin
			insert @Tbl_DB select SUBSTRING(@DatabasesTmp,1,CHARINDEX(',',@DatabasesTmp)-1)
			set @DatabasesTmp = SUBSTRING(@DatabasesTmp,CHARINDEX(',',@DatabasesTmp)+1,LEN(@DatabasesTmp))
		end else begin
			insert @Tbl_DB select @DatabasesTmp
			set @DatabasesTmp = ''
		end
	end
end else begin
	insert @Tbl_DB select name from sys.databases where database_id>4
end
--

--code
declare CursorX cursor for 
select DB from @Tbl_DB
open Cursorx
fetch from Cursorx into @DBName
while @@FETCH_STATUS=0
begin
	set @sql = 'Use ' +@DBName + '; '+CHAR(10)+'
		insert ##Prodecures
		select '''+@DBName+''',name,SCHEMA_NAME(schema_id) from sys.objects where type='''+@ObjectType+''' '
	exec(@sql)
	fetch next from Cursorx into @DBName	
end
close Cursorx
deallocate Cursorx

declare CursorY cursor for 
select DBName,ProcName,SchemaName from ##Prodecures
open CursorY
fetch from CursorY into @DBName,@ProcName,@SchemaName
while @@FETCH_STATUS=0
begin
	set @sql = 'use ['+ @DBName + ']; ' +
		@GrantDeny+' '+@PermissionType+' ON ['+@SchemaName+'].['+@ProcName+'] TO ['+@UserName+'] AS [dbo]; '
	exec(@sql)
  fetch next from CursorY into @DBName, @ProcName, @SchemaName
end
close CursorY
deallocate CursorY

Drop Table ##Prodecures

SET NOCOUNT OFF
--code

 

Object Type için kullanılabilecek parametreler aşağıdaki gibidir.

FN SQL_SCALAR_FUNCTION
C CHECK_CONSTRAINT
UQ UNIQUE_CONSTRAINT
SQ SERVICE_QUEUE
U USER_TABLE
D DEFAULT_CONSTRAINT
PK PRIMARY_KEY_CONSTRAINT
V VIEW
S SYSTEM_TABLE
IT INTERNAL_TABLE
P SQL_STORED_PROCEDURE
TF SQL_TABLE_VALUED_FUNCTION
TR SQL_TRIGGER
PC CLR_STORED_PROCEDURE

 

Bir kaç örnek vermek gerekirse;

turgay user ına bütün AdventureWorks ve AdventureWorks2008 DB lerinde ki bütün table lar için SELECT yetkisi vermek.

exec GivePermissionForAllObjects 'turgay','AdventureWorks,AdventureWorks2008','GRANT','SELECT','U'

 

turgay user ıne bütün DB lerde ki Stored Procedure ler için EXECUTE yetkisi vermek.

exec GivePermissionForAllObjects 'turgay','','GRANT','EXECUTE','P'

 

turgay user ına bütün DB lerde verilmiş View SELECT hakkını geri almak. (REVOKE)

exec GivePermissionForAllObjects 'turgay','','REVOKE','SELECT','V'

 

turgay user ı bütün DB lerde Sistem Table larını Inherit alsa dahi SELECT çekemesin

exec GivePermissionForAllObjects 'turgay','','DENY','SELECT','S'

 

Bu şekilde istediğiniz DB de ki objelere topluca yetki verebilir yada geri alabilirsiniz.

 

İyi çalışmalar.

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