SQL bir string değerin hangi tablonun tangi alanında geçtiğini bulma

Eğer bir string değerin hangi tablolarda ve bu tabloların hangi alanlarında geçtiğini ya da geçip geçmediğini bulmak istiyorsanız alın size ufak ama bir o kadar da YAVAŞ çalışacak bir query;

declare
@query nvarchar(4000),
@tbl_name sysname,
@tbl_id int,
@column_name sysname

declare cursor_tbl cursor for
select s.name+'.'+o.name name, object_id 
from sys.objects o (nolock)
join sys.schemas s (nolock) on o.schema_id=s.schema_id
where o.type = 'U'

declare @aranacaktext nvarchar(100)
Select @aranacaktext = N'Bulunacak string'

open cursor_tbl

fetch next from cursor_tbl into @tbl_name, @tbl_id

while (@@FETCH_STATUS = 0)
begin
    declare cursor_col cursor for 
    select name from sys.columns (nolock)
    where object_id = @tbl_id 
    and system_type_id in (167, 175, 231, 239)

    open cursor_col

    fetch next from cursor_col into @column_name
    while (@@FETCH_STATUS = 0)
    begin
        select @query = 'if exists 
        (select * from ' + @tbl_name + '(nolock) 
        where ['+ @column_name +'] like ''%'+@aranacaktext+'%'') 
        print ''' + @tbl_name + ', ' + @column_name + ''''
        exec (@query)

        fetch next from cursor_col into @column_name
    end
    close cursor_col
    deallocate cursor_col

    fetch next from cursor_tbl into @tbl_name, @tbl_id
end

close cursor_tbl
deallocate cursor_tbl

SQL (nolock)

SQL bazı drumlarda verileri kilitler. Örneğin bir tabloya update çekiyorsunuz ya da bir transaction başlattınız. Ama transaction henüz sonlanmadı. Bu tür durumlarda SQL dataya diğer connection’lardan erişimi durdurur.

Şimdi sizinle şöyle bir deney yapalım ;

SQL Server Management Studio’yu açalım ve “New Query” butonuna 2 defa tıklayarak 2 adet boş query penceresi açalım

nolock1

Management studio da 2 adet query penceresi demek, aslında sql server a oluşturulmuş 2 farklı bağlantı demektir. SQLQuery1.sql penceresinde yukarıda görüldüğü gibi  “Begin Tran” ve alt satıra bütün bir tabloyu UPDATE eden bir script yazın. Şimdi query i çalıştırın. Update ettiğiniz tabloya select çektiğinizde update ettiğiniz bütün alanların değiştiğini görüceksiniz. Aslında daha değişmedi siz “Begin Tran” ile başlattığınız transaction ı commit edene kadar hiçbir değişiklik veritabanına kaydedilmez ve siz commit verene kadar, yani “Commit Tran” yazana kadar SQL server tablodaki kayıtları kilitler. Kimse bu tablodan select ile bu verileri görüntüleyemez. Sizin çektiğiniz Select çalıştı ama değil mi. Bunun nedeni sizin Transaction ı başlattığınız aynı connection ile select çekiyor olmanız. Aynı Query Penceresinde olmak burada aynı connection da olmak demektir.

nolock2

Şimdi aynı select i açtığımız diğer Query Penceresinden yani SQLQuery2.sql den deneyelim

Select imizi SQLQuery2.sql penceresinde çektiğimizde, alt tarafta görüğümüz executing query yazısının sürekli orda olduğunu görürsünüz. Diğer connectiondan açtığınız transaction henüz tamamlanmadığı için verileriniz hala kilitli ve  onlara, şu anda başka bir connection da olduğunuz için ulaşamıyorsunuz. Bu select cümleciğinin size istediğiniz listeyi getirebilmesi için SQLQuery1.sql penceresinde, ilk resimde — işareti ile commitlenen”rollback tran” cümleciğini — işareti olmadan, yani commitsiz yazmalı ve çalıştırmalısınız. Tabi eğer yaptığınız Update e onay vermek istiyorsanız; “commit tran” cümleciğini de kullanabilirsiniz.

İşte böyle durumlarda çektiğiniz bir sorgunun bir nesnenin kilitli olmasına bakmadan size listesini getirmesini istiyorsanız yukarıdaki select cümleciğini şu şekilde yazmalısınız;

Select * from [AdventureWorks2008R2].[HumanResources].[Employee] (nolock)

Bu durumda sql ulaşmak istediğiniz verinin kilitli olup olmadığına bakmadan listeyi size vericektir. (Nolock) olayı bir çok durumda yapmış olduğunuz sorguların kilitleri beklemesini engelleyeceğinden programlarınızın daha hızlı çalışması için tercih edeceğiniz bir yöntemdir. Fakat yine de verilerin ne zaman nolock ile çekileceğine karar vermek dikkatli düşünülmesi gereken bir iştir. Çünkü verileriniz biryerlerde işleniyor ve süreç henüz sona ermedi. Siz sürecin sona ermesini beklemek istiyor musunuz? Yoksa bu durum sizin mevcut şartlarınızda önemsiz mi? Karar verilmesi gereken asıl nokta budur.

 

SQL Union All

Aynı sıra ile aynı veritipi bilgileri içeren 2 sorguyu birleştirmek istediğimizde birleştirmek istediğimiz query ler arasında “union all” kullanırız

Örneğin ;

SELECT * FROM [AdventureWorks2008R2].[HumanResources].[Employee]
where [JobTitle]='Design Engineer'

şeklinde bir sorgumuz var çıktısı aşşağıdaki gibi

SQLUnion1

Burada görevi ‘Design Engineer’ olan çalışanları görüyoruz.

SELECT * FROM [AdventureWorks2008R2].[HumanResources].[Employee]
where [JobTitle]='Marketing Assistant'

Sorgusunu yaparak ta görevi ‘Marketing Assistant’ olan çalışanların listesini alalım

SQLUnion2

Şimdi bu iki sorguyu “union all” ile birleştirelim.

SELECT * FROM [AdventureWorks2008R2].[HumanResources].[Employee]
where [JobTitle]='Design Engineer'
union all
SELECT * FROM [AdventureWorks2008R2].[HumanResources].[Employee]
where [JobTitle]='Marketing Assistant'

Artık aşağıda gördüğünüz gibi iki sorguyu birleştirerek, görevi ‘Marketing Assitant’ ve ‘Design Engineer’ olan  çalışanların listesini almış olduk

SQLUnion3

SQL de WITH CHECK OPTION

Örneğin bir sınıfta matematik dersinden 45 in altında not alan öğrencileri gördüğümüz basit bir view’ımız var

Create View DusukNotlar
as
Select Adi,Soyadı,Ders,Not from tblDersNotlari where Not<45

Şimdi diyelimki bu view a bir insert yapmamız gerekti;

insert into DusukNotlar
Select 'Barış','Eldem','Matematik',80

Bu insert script’ini çalıştırdığımız zaman SQL yeni kaydı girecek fakat kayıt viewde gözükmeyecektir. Çünkü view’in scriptinde not<45 diye belirtilmişti. Halbuki bizim girdiğimiz not 80.

Eğer SQL’in bu şekilde view’a göremeyeceğimiz bir kaydı insert emeye izin vermesini engellemek istersek, view’ımızı WITH CHECK OPTION ile yazmamız gerekir. Bu durumda view’ımız aşağıdaki gibi olacaktır;

Create View DusukNotlar
as
Select Adi,Soyadı,Ders,Not from tblDersNotlari where Not<45
WITH CHECK OPTION

SQL’de stored procedure içinde text arama

Bazı durumlarda bir kelimenin, veritabanındaki hangi sp’ler içinde geçtiğini öğrenmeniz gerekebilir. Örneğin bir tablodaki bir alan adını değiştirdiniz ve bu alan adının içinde geçtiği bütün procedure’leri bulup değiştirmeniz gerekiyor. Bu durumda şöyle ufak bir script size yardımcı olabilir;

SELECT OBJECT_NAME(id) 
    FROM syscomments 
    WHERE [text] LIKE '%bulunacak text%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

Bu script çalıştığında içinde aradığınız kelimeyi (bulunanacak text) içeren bütün sp’ler listelenecek ve sizde bunları değiştirerek muradınıza ermiş olacaksınız.

SQL de escape karakteri kullanmak

Bazen sql de select çekerken aramak istediğimiz kriter SQL in önceden aktarılmış karakterlerinden biri olabilir. Örnek olarak aşağıdaki gibi bir script oluşturalım ve #zz şeklinde geçici bir tablo yaratalım.

select '%Master Deneme Deneme' Adi,1 ID into #zz

insert into #zz
select '%Deneme 1' ,2
union all
select 'Deneme 2', 3
union all
Select 'Slave',4
union all
select 'Slave % deneme', 5

Şimdi bu tabloda, Adi alanında % karakteri olan satırları getirmek istediğimizi farzedelim. Eğer bunu yapmak için

Select * from #zz where adi like '%%%'

yazarsak tablodaki bütün sonuçların döneceğini görürüz. Fakat bizim istediğimiz kayıtlar bunlar değildi. Biz sadece Adi alanı içinde % karakteri geçen satırların listelenmesini istemiştik. Böyle durumlarda yapmamız gereken şey diğer dillerde olduğu gibi ESCAPE karakteri kullanmak. Diğer birçok dilden farklı olarak bu gibi durumlarda SQL de escape karakterimizi kendimiz belirleriz. Yukarıdaki sorunumuzun çözümü olarak aşağıdaki kodu yazmamız gerekir

select * from #zz where adi like '%/%%' escape('/')

Burada belirlediğimiz / karakteri, ondan sonra gelen karakterin aktarılmış bir karakter değil, herhangi diğer bir karakter olduğu anlamına gelir. Yani anlayacağınız üzere ‘/’ yerine ‘a’ da yazabilirdik. Bu durumda sql ‘a’ dan bir sonraki karakter sql in herhangi birşey için kullandığı özel bir karakter olsa bile onu bu özel durumuyla değil diğer karakterleri değerlendirdiği gibi normal bir karakter olarak değerlendirirdi.

SQL’de view içeriklerini şifrelemek

Normalde bir view oluştururken eğer bunu yazarak yapacaksak şöyle bir ifade kullanırız

Create view ViewAdi
as
Select * from TabloAdi

Bu view’ı görüntülemek için

sp_helptext ViewAdi

Komutunu verdiğimizde yukarıda yazdığımız create ile başlayan kodun karşımıza çıktığını görürüz. Eğer view’ı aşağıdaki şekilde oluşturursak ;

Create view ViewAdi with ENCRYPTION
as
Select * from TabloAdi

Bu defa sp_helptext komutu ile baktığımızda bize “The text for object ‘ViewAdi’ is encrypted.” şeklinde bir mesaj verecek ve içeriği göstermeyecektir. Ayrıca object explorer ile baksak ve view uzerinde sağ tuş tıklasak bile karşımıza Design seçeneği pasif olarak geleceğinden view üzerinde değişiklik yapmak yada kodu görmek mümkün olmayacaktır.

Oluşturma sırasında WITH ENCRYPTION kullanımı SP, FUNCTION ve TRIGGER lar da da aynı sonucu verecektir. Bu objelerin çalışmasında bir değişiklik olmayacak fakat kodu görüntülenemeyecektir.

SQL Row_Number Fonksiyonu

SQL de bir tabloya bir sorgu çektiğimizi düşünelim.Sorugumuz şöyle bir sorgu olsun ;rowcount1

Sorgu sonucunda gelen her satırın başında, her satır için artan bir numara yazmak isteseydik ne yapardık? İşte bu durumda SQL in Row_Number() fronkisyonu imdadımıza yetişiyor. Ve bunu yaptırmak için kullanacağımız sorgu da şu şekilde olmalı ;

rowcount2

Sorguda “order by [FirstName]” yazdığımız alanı ihtiyacımıza göre istediğimiz gibi değiştirebiliriz.

Basit bir fonksiyon için uzun bile yazdım. Yardımcı olabilmesi dileğimle.