Sorgunuza Özel Create Table Scripti Oluşturma

Veritabanı Yöneticisi veya geliştirici olarak büyük sorunlardan bir tanesi hazırladığımız sorguyu bir tabloya kayıt edilirken tablo yapısını oluşturulma sürecidir. Acaba bu kolona hangi veri türünde oluşturmalıyız şeklinde sorgunun incelemesini yaparız. Şimdi size bahsedeceğim dynamic management function – DMF ( sys.dm_exec_describe_first_result_set / sys.dm_exec_describe_first_result_set_for_object ) kullanımı ile bir hazırladığınız sorguya özel create table scriptini çıkartabiliyor olacaksınız. Yazıyı okumaya devam ederseniz neden SELECT INTO haricinde bir yapıdan bahsettiğimi de anlatacağım :)

Syntax

sys.dm_exec_describe_first_result_set(@tsql, @params, @include_browse_information)

@tsql : Sorgunuz yada batch kümesi

@params : Sp_executesql benzeri parametre verilebilimesini sağlar. Bu parametre için NULL varsayılan değerdir.

@include_browse_information : 1 olarak ayarlanırsa, her sorgu, sorguda FOR BROWSE seçeneğine sahipmiş gibi analiz edilir. Ek anahtar sütunlar ve kaynak tablo bilgileri döndürülür.

Sorgu kullanım sonrasında aşağıdaki meta verileri verir.

column_ordinal ( int ) : Sonuç kümesindeki sütunun sıra konumunu içerir. İlk sütunun konumu 1 olarak belirtilecektir.

Isim ( sysname ) : Bir isim belirlenebilirse sütunun adını içerir. Değilse, NULL içerecektir.

is_nullable ( bit ) : Sütun NULLs izin veriyorsa Değer 1. Sütun NULLs izin vermiyorsa değer 0. Sütun NULLs izin verdiği belirlenemiyorsa Değer 1.

system_type_name ( nvarchar(256)) Sütunun veri tipini döndürür. Veri türü CLR kullanıcı tanımlı bir türse, bu sütuna NULL döndürülür.

max_length : Sütunun maksimum uzunluğu (bayt cinsinden). -1 = Sütun veri türü varchar (max),nvarchar (max), varbinary (max) veya xml’dir.

Örnek Kullanım:

SELECT * FROM sys.dm_exec_describe_first_result_set(N’SELECT BusinessEntityID,Title,FirstName,LastName FROM HumanResources.vEmployee’, NULL, 0)

Resim-1

Gördüğünüz gibi “sys.dm_exec_describe_first_result_set” kullanımı ile sorgunun özelliklerini görmüş olduk ve buradaki bilgiler aslında bizim sorgu sonucuna uygun create table scripti oluşturmak için kullanmamız gereken özellikleri içeriyor. Create scripti oluşturacak bir function yazıp, create table işlemlerimizi de otomatikleştirmiş olabiliriz.

Sorgu :

CREATE FUNCTION fn_Create_Table_Structure (@InputSQL AS NVARCHAR(4000), @TableName AS NVARCHAR(128) = NULL)

RETURNS NVARCHAR(4000)

AS

BEGIN

DECLARE @SQL AS NVARCHAR(4000)

DECLARE @name NVARCHAR(128)

DECLARE @is_nullable BIT

DECLARE @system_type_name NVARCHAR(128)

DECLARE @collation_name NVARCHAR(128)

DECLARE @NewLine NVARCHAR(2) = CHAR(13) + CHAR(10) — CRLF

DECLARE CUR_Table CURSOR LOCAL FAST_FORWARD

FOR

SELECT name ,

is_nullable ,

system_type_name ,

collation_name

FROM sys.dm_exec_describe_first_result_set(@InputSQL, NULL, NULL)

WHERE is_hidden = 0

ORDER BY column_ordinal ASC

OPEN CUR_Table

FETCH NEXT FROM CUR_Table INTO @name, @is_nullable, @system_type_name,

@collation_name

SET @SQL = ‘CREATE TABLE [‘ + ISNULL(@TableName, ‘TableName’) + ‘] (‘

+ @NewLine

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL += @NewLine + ‘[‘ + @name + ‘]’ + ‘ ‘ + @system_type_name

+ CASE WHEN @collation_name IS NOT NULL

THEN ‘ COLLATE ‘ + @collation_name + ‘ ‘

ELSE ”

END + CASE WHEN @is_nullable = 0 THEN ‘ NOT NULL ‘

ELSE ”

END + ‘,’

FETCH NEXT FROM CUR_Table INTO @name, @is_nullable, @system_type_name,

@collation_name

END

SET @SQL = LEFT(@SQL, LEN(@SQL) – 1) + @NewLine + ‘)’

CLOSE CUR_Table

DEALLOCATE CUR_Table

RETURN @SQL

end

Yazdığımız fonksiyonu ise aşağıdaki gibi kullanabiliriz.

declare @script varchar(max)
DECLARE @InputSQL varchar(1000) = ‘SELECT BusinessEntityID,Title,FirstName,LastName FROM HumanResources.vEmployee’
select @script = dbo.fn_Create_Table_Structure(@InputSQL,‘PersonelOzet’)
print @script
exec (@script)

Resim-2

@script değişkenine aldığımız create sorgusunu da exec ile çalıştırdıktan sonra PersonelOzet isimli tablomuz oluşmuş ve kullanıma hazır durumdadır.

Farklı kaynaklardan gelen verileri tek bir ortak tabloya alma ihtiyacımız olabileceği için SELECT INTO kullanamıyoruz. SELECT INTO kullanımında sorgunun metadata cıktısını alabilmek adına SET FMTONLY kullanımı vardı fakat bu özellikte SQL Server 2012 ile sona ( https://docs.microsoft.com/en-us/sql/t-sql/statements/set-fmtonly-transact-sql ) erdi. Bu yüzden bu işlemi kullanmak adın artık bu DMF kullanmalıyız.

Bu konuyla ilgili sorularınızı  alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.

Referanslar

www.mshowto.org

TAGs: Auto Generate Create Table Script, Create Table Script, DMF, dynamic management function, SELECT INTO, SET FMTONLY, sys.dm_exec_describe_first_result_set, sys.dm_exec_describe_first_result_set_for_object