Veritabanı nesneler hakkında bilgi almak

SQL Server veri tabanı nesneler hakkında bilgi almak, metadata sorgulamak, öğrenmek

Database Management System-DBMS(Veritabanı Yönetim Sistemleri – VTYS)’larda veritabanının sahip olduğu nesneler hakkında bilgi almak, metadalarını(veri nesneleri hakkında veri tablosu) sorgulamak için bazı yöntemler sunulur. Bunlardan bir tanesi ANSI_SQL standartı olan INFORMATION_SCHEMA ile başlayan metadata viewleri diğer yöntem ise sistem tablolarıdır. Bu bilgiler, bir uygulamanın database katmanı için kodları generate etmekte kullanıldığı gibi SQL Injection gibi kötü niyetler için de kullanılmaktadır. Ayrıca database üzerinde yeni bir nesne oluşturulacağı zaman onun önceden var olup olmadığını kontrol etmek veya nesneyi silmeye çalıştığımızda gerçekten veritabanında öyle bir nesnenin olduğundan emin olmak için veritabanının metaverisini sorgulamak işlemlerin doğru sonuçlanması açısından önemlidir.
SQL Server’da kullanılan metadata viewleri şunlardır;

    CHECK_CONSTRAINTS,
    COLUMN_DOMAIN_USAGE
    COLUMN_PRIVILEGES
    COLUMNS
    CONSTRAINT_COLUMN_USAGE
    CONSTRAINT_TABLE_USAGE
    DOMAIN_CONSTRAINTS
    DOMAINS
    KEY_COLUMN_USAGE
    PARAMETERS
    REFERENTIAL_CONSTRAINTS
    ROUTINE_COLUMNS
    ROUTINES
    SCHEMATA
    TABLE_CONSTRAINTS
    TABLE_PRIVILEGES

Bunların başına INFORMATION_SCHEMA. ifadesi eklenerek kullanılır. Örneğin bulunduğumuzda databasedeki tabloları okumak için

SELECT * FROM INFORMATION_SCHEMA.TABLES 

ifadesi kullanılır. Bu ifadeyi Northwind için 5 tane kayıt getirecek şekilde çalıştırdığımızda aşağıdaki sonuçları elde ederiz.

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
Northwind dbo Orders BASE TABLE
Northwind dbo Products BASE TABLE
Northwind dbo Order Details BASE TABLE
Northwind dbo Customer and Suppliers by City VIEW
Northwind dbo Alphabetical list of products VIEW

SQL Server’da bu viewler her databasein altında bulunur. “dbo” kullanıcısının altında bulunmadıkları için her veritabanının altında karşılık sistem tabloları kullanılır. DB2’taki System Catalog, Oracle’daki Data Dictionary yapılarına denk gelen bu tabloların bazıları master databaseinde bulunup sistem düzeyinde veriler içerir bazıları da her databaseinde altında bulunup o databasein sahip olduğu nesne ve kaynaklar hakkında veri taşırlar. Sistem tabloların isimleri, “sys” sözcüğüyle başlar. Fakat “sys” ile başlayan her nesne, sistem tablosu değildir. Her sistem tablosunun bir ID’si olup databaseler altında bulunan sysobjects tablosunda tanımlıdırlar. Sistem tablolarında SQL Server’daki veritabanlarına ait tüm metadata bilgileri bulunur. Mesela, konunun başında bahsettiğimiz metada viewlerinin listesine ulaşmak için aşağıdaki sorguyu çalıştırabiliriz.

SELECT * FROM master.dbo.sysobjects WHERE uid=3

Sistem databaselerin altında farklı sistem tabloları bulunsa da en çok kullanılan sistem tabloları şunlardır;

    sysservers (Master – REMOTE SERVICES)
    sysprocesses (Master – ACTIVE USER PROCESSES)
    syslogins (Master – REMOTE SERVICES)
    sysremotelogins (Master – REMOTE SERVICES)
    syscursors (Master – CURSORS)
    sysconfigures (Master – CONFIGURATION)
    sysdevices (Master – STORAGE)
    sysaltfiles (Master – STORAGE)
    syslanguages (Master)
    sysmessages (Master)
    sysdatabases (Master)
    syscharsets (Master)
    sysmembers (Model – USERS & PERMISSIONS)
    sysusers (Model – USERS & PERMISSIONS)
    syspermissions (Model – USERS & PERMISSIONS)
    syscomments (Model – OBJECTS)
    sysobjects (Model – OBJECTS)
    sysdepends (Model – OBJECTS)
    sysconstraints (Model – OBJECTS)
    sysindexes (Model – OBJECTS)
    syscolumns (Model – OBJECTS)
    systypes (Model – OBJECTS)
    sysforeignkeys (Model – OBJECTS)
    sysreferences (Model – OBJECTS)
    sysindexkeys (Model – OBJECTS)
    sysfiles (Model – STORAGE)
    sysfilegroups (Model – STORAGE)
    sysallocations (Model – STORAGE)
    backupset (Msdb – BACKUP)
    backupfile (Msdb – BACKUP)
    restorehistory (Msdb – BACKUP)
    restorefilegroup (Msdb – BACKUP)
    restorefile (Msdb – BACKUP)
    sysalerts (Msdb – SQL SERVER AGENT)
    sysoperators (Msdb – SQL SERVER AGENT)
    sysjobhistory (Msdb – SQL SERVER AGENT)
    sysjobsteps (Msdb – SQL SERVER AGENT)
    syscategories (Msdb – SQL SERVER AGENT)
    sysjobs (Msdb – SQL SERVER AGENT)
    sysjobschedules (Msdb – SQL SERVER AGENT)
    sysjobservers (Msdb – SQL SERVER AGENT)
    sysdtspackages (Msdb – DTS)
    sysdtscategories (Msdb – DTS)
    sysdbmaintplans (Msdb – DATABASE MAINTENANCE)
    sysdbmaintplan_databases (Msdb – DATABASE MAINTENANCE)
    sysdbmaintplan_history (Msdb – DATABASE MAINTENANCE)
    sysdbmaintplan_jobs (Msdb – DATABASE MAINTENANCE)

SQL Server’in sistem tablolarında nesneler, birer ID ile temsil edilir. Object Identification Number olarak tanımlanan bu ID bilgisi nesneleri, diğer nesnelerden ayırmış olur. SQL Server’da aynı isimde farklı türde iki nesne olabileceği için bunları ancak ID’lerinden ayırabiliriz. Yani Email isminde hem bir Table hem de bir stored procedure nesnesi olabilir. sysobjects tablosunda bu nesnelerin ID’leri “object_id”, isimleri “name”, tür bilgileri de “type” ve “type_desc” kolonlarında tutulmaktadır. Dolayısıyla bir nesne yaratacağımız zaman öncelikle bu tabloyu bu kolonlarla sorgulamalıyız ki querymiz hata vermesin. Bu sorgulamayı isimle yapabileceğimiz gibi ID ile yapmamız daha doğru olacaktır. SQL Server, herhangi bir nesnenin ID ve türünü kolayca öğrenmemiz için OBJECT_ID ve OBJECTPROPERTY fonksiyonlarını sunar. OBJECT_ID, parametre olarak char veya nchar türünde değer alır.

USE master
SELECT OBJECT_ID('pubs..authors')

ifadesi, 1977058079 değerini döndürür.OBJECTPROPERTY fonksiyonu ise id ve property isminde iki parametre alır. Buradaki property parametresi, sorgulanan nesnenin tür bilgisini ifade eder. Örneğin “IsTable” ifadesi, nesnenin table olup olmadığını, “IsTrigger” ifadesi, nesnenin trigger olup olmadığını döndürür. Şimdi bir örnek yapalım. DENEME isimli databasede L_User tablonun olup olmadığını kontrol edelim.

USE DENEME
if exists (
	select * from dbo.sysobjects where id = object_id(N'L_User')
	and OBJECTPROPERTY(id, N'IsUserTable') = 1
)
	print 'Tablo Var'
else
	print 'Tablo Yok'

SQL 2005’te sistem tabloları mantıksal olarak yukarıda bahsettiğimiz databaseler altında bulunsa da fiziksel olarak RDB olarak isimlendirilen bu sürümle birlikte gelmiş olan Resource database‘inde bulunur. mdf dosyası mssqlsystemresource.mdf olarak tanımlanmış olan bu database, readonly olup sadece SQL Server 2005’in sistem nesnelerini içerir, user veya sistem data veya metadata bilgisi içermez. Bütün sistem objeleri, fiziksel olarak bu veritabanının altında bulunur ancak bütün veritabanlarının sys schemasına mantıksal olarak bağlanmıştır. Onun için herhangi bir databaseden rahatlıkla erişilebilirler. Bütün sistem objelerine erişmek aşağıdaki query çalıştırılabilir;

SELECT name as object_name, object_definition(object_id) as sql_tanimlama
FROM sys.system_objects

L_User örneğini SQL 2005 için şu şekilde uyarlayabiliriz;

USE DENEME
IF NOT EXISTS (
	SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EMAIL]')
	AND type in (N'U')
)
	print 'Tablo Var'
else
	print 'Tablo Yok'

Yeri gelmişken bu Resource database ile ilgili birkaç cümle daha yazmak faydalı olacaktır. Bu database içerisinde neler olduğunu öğrenmek için şu şekilde bir yanıltma işlemi yapabiliriz.

    Öncelikle servislerin altındaki Microsoft SQL Server 2005 system service (SQL Server (MSSQLSERVER)) servisini durduralım.mssqlsystemresource.mdf dosyasını mssqlsystemresource_copy.mdf ve mssqlsystemresource.ldf dosyasını mssqlsystemresource_copy.ldf olarak kopyalarını oluşturalım.SQL Server (MSSQLSERVER) servisini başlatalım. Kopyalarını aldığımız mdf ve ldf dosyalarını aşağıdaki gibi attach edelim.
EXEC sp_attach_db 'MSSQLServerResource_Copy',
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MSSQLSystemResource_copy.mdf',
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MSSQLSystemResource_copy.ldf'

SQL Server Management Studio’ı kullanarak ta farklı bir database ismiyle Resource databasein kopyasını attach edebiliriz.

Resource veritabanı, master veritabanının bulunduğu yerle ilişkilidir. master veritabanını başka yere taşığımızda Resource database’i de o lokasyona taşımalıyız.Resource veritabanını NTFS ile compress veya encrypt edilmiş bir klasörde saklamamalıyız. Bu durum, performansı ve güncellemeyi engelleyecektir.
Resource veritabanının yedeği SQL Server Management Studio ve T-SQL koduyla alınmaz. Böyle bir durumda mssqlsystemresource.mdf ve mssqlsystemresource.ldf dosyalarının manual olarak yedeklenmesi gerekir. SQL Server 2005’te sistem veritabanlarının nasıl yedeklenecekleri MSDN’de anlatılmaktadır.
Son olarak şunu söyleyebiliriz, sistem tabloları database programlarının sürümlerine bağlı olarak değişkenlik arzettikleri ve kullanıcı tarafından manual değiştirilebilme ihtimalleri için bunların yerine konunun başında bahsettiğimiz metadata viewlerinin kullanılması daha sağlıklı olur. Bununla birlikte SQL Server 2005’te sistem metadatasıyla ilgili hem bahsi geçen eksiklikler kaldırılmış hem de yeni yaklaşımlar sunulmaktadır. Örneğin daha önce programcı servis tablolarını kolaylıkla değiştirebiliyordu fakat SQL Server 2005’te bunu yapması çok zor. Ayrıca daha önce kullanıcı bu tablolar aracılığıyla yetkili olmadığı nesneleri de görebilmekteydi. 2005 sürümünde metadata sorgulama için bir güvenlik katmanı oluşturulmuş böylece kullanıcı sadece yetkili olduğu nesneleri görebilmektedir.Dolayısıyla SQL Server 2005 için sys şemasına bağlı görüntülerin kullanılması daha işlevsel ve daha fazla bilgi sağlayıcı olacaktır.

Konuyla ilgili query örneklerinin bulunduğu linkteki MSDN makalesinin faydalı olacağına inanıyorum.

Veritabanı nesneler hakkında bilgi almak” üzerine 17 düşünce

  1. rukiye sarı

    acilllllllll yardım
    bilgisayar programcısıyım ödevim master database de sys tablolarının açıklaması 10 tane tablonun açıklaması ne işe yaradığı bana yardımcı olurmusunuz ödev teslim 31 mayıs son lütfen başarılarınızın devamını dilerim

    Cevapla
  2. Ahmet Kaymaz Yazar

    Merhaba,SQL 2000’deki master database’in altındaki sys tabloları, database sunucusunun konfigurasyon bilgilerini ve veritabanı üzerindeki nesneler(table, view, user .) hakkında bilgi tutmaya yarar. Örneğin sysservers tablosu sunucu üzerindeki linked server tanımlamalarını, sysusers tablosu veritabanı üzerinde tanımlı kullanıcıları, sysdatabases tablosu, sunucu üzerinde tanımlı databaseleri, sysprocesses tablosu, o anda sunucu üzerinde oturum açmış kullanıcı bilgilerini bildirir. Bütün sistem nesnelerine aşağıdaki query ile erişebilirsiniz;SELECT name FROM master..sysobjects WHERE name LIKE 'sys%'

    Cevapla
  3. askin er

    Merhabalar bende sunu merak ediyorum database senkronizasyonu için program yazmaya çalışıcama ama sql’i daha ilk defa kullanmış olucam kısmetse.BU sysobjects tam olarak ne işe ayrıo onu anlamadım ki ayrıca benim projemde database senkronizasyonu olucagı için nasıl bu database’leri bir birleri ile aynı yapıya getiemem gerektiğini merak ediyorum yani ilk veri tabanı ile ikinci veri tabanı bir birinin aynısı olucaklar

    Cevapla
  4. Ahmet Kaymaz Yazar

    Sayın Er,sysobjects tablosu, SQL Server’a ait sistem tablosu olup bir bulunduğu database’deki table, view, rule, stored procedure, default ve trigger nesnelerinin tanımlarını içerir.
    “İki database birbirinin aynısı olacak” derken sanırım hem şema yapısı yani içerdikleri nesneler aynı olacak hem de tablo içindeki datalar aynı olacak demek istiyorsunuz. Bu konuyu DTS’ler aracılığıyla data export/import yapısıyla yapabileceğiniz gibi replikasyon yöntemiyle de yapabilirsiniz. Fakat SQL Server’a yeni başlamış biri olarak direk bu konudan başlamanız sizi yorabilir. Konuyla ilgili olarak “SQL Server Database Mirroring” veya “SQL Server Replication” teknolojilerini incelemenizi tavsiye ederim.

    Cevapla
  5. askın er

    senkronızasyondan kastım sadece yapısal olarak bır bırnın aynı olan ıkı database yaartmaya calısırum yanı ıcndekı datalara herahangı bı mudahalem olmayacak fakat bunu yaparken stored proedure ve view kısımlarınında nasıl kaynak databaseden hedef database ile kıyaslanıp sonrasında guncelleme yapılacagını bılmıorum bana bu konuda yardımcı olursanız sevınırım.

    Cevapla
  6. Elmis Numan

    Iyi gunler,Ben iki veritabanı arasında senkronizasyon yapmaya yarayan bir uygulama yazmaya calişıyorum. Askın Er’in sordugu “sadece yapısal olarak bırbırnın aynı olan ıkı database yaratmaya calısıyorum yanı ıcndekı datalara herahangı bı mudahalem olmayacak fakat bunu yaparken stored proedure ve view kısımlarınında nasıl kaynak databaseden hedef database ile kıyaslanıp sonrasında guncelleme yapılacagını bılmıorum bana bu konuda yardımcı olursanız sevınırım.” kısmını siz anlatmışsınız fakat bunu benim kodla yapmam lazım. Bana bu konuda fikir verebilirseniz sevinirim.Ilginiz icin tesekkur ederim.

    Cevapla
  7. Ahmet Kaymaz Yazar

    Aklıma gelen başka bir yöntem olarak sp_helptext procedure kullanılabilir. sp_helptext procedure, view, stored procedure, function gibi nesnelerin script kodlarını döndürür.

    Cevapla
  8. Poyraz

    Selam benim öğrenmek istediğim iki database arasinda table aktarimi nasil yapabileceğim neredeyse birbirinin ayni iki db sadece aralarinda farklı olan bir iki table i bir diğerine aktarmam gerek bu konuda nasıl bir yol izlemem gerektiği hakkında bilgi verirseniz sevinirim

    Cevapla
  9. Ahmet Kaymaz Yazar

    Bu işin en kolay en pratik yöntemi SQL Server içerisindeki Import/Export işlemini kullanmanızdır. Bu işlemde kaynak ve hedef veritabanlarını seçerek ilgili aktarımları kolayca yapabilirsiniz.

    Cevapla
  10. Murat Merdogan

    selamlar sql üzerindeki iki farklı serverdeki aynı databasedeki aynı bir tabloyo şemasal olarak nasıl comparison edebilirim
    Saygılar
    Kolay Gelsin.

    Cevapla
  11. Ahmet Kaymaz Yazar

    Uzaktaki sunucunun adı SERVER2, karşılaştırılacak tablo adının TB ve bu tablonun DB isimli veritabanı altında olduğunu düşünelim. Öncelikle yereldeki tablonun kolon listesi alınıp #T1 tablosuna aktarılır.SELECT COLUMN_NAME INTO #T1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=’Butce’Aynı şekilde uzaktaki sunucu için de kolon listesi alınır.SELECT * INTO #T2 FROM OPENQUERY
    (SERVER2,’SELECT COLUMN_NAME FROM DB.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME=”TB”‘)Ardından #T1 ile #T2 tabloları JOIN edilerek iki tablo arasındaki fark çıkarılır.

    Cevapla
  12. Murat Merdogan

    Hocam sağolun peki bu farkın sql scriptini çıkarmak ıcın nasıl bır yol ızlemek gerekır.bu scrıpt calıstırılarak uzaktakı tablonun localdakı tabloyla aynı sema halıne getırılmesı gerkıyor.

    Cevapla
  13. Ahmet Kaymaz Yazar

    Bu script’i burada yazmak zaman alacaktır. Eğer bir kereliğine yapacaksan sqldbtools.com’un SQLDBDiff veya AdeptSQL Diff araçlarını kullanabilirsin. Mutlaka kendim yapmak istiyorsan şu şekilde yönlendirme yapabilirim.Önceki mesajda bahsettiğim gibi #T1 ve #T2 kolonlarını Join edip yereldeki tabloda olup uzaktaki tabloda olmayan kolonlar listelenir. INFORMATION_SCHEMA.COLUMNS isiml view’deki COLUMN_NAME, kolon adını, DATA_TYPE kolonu o kolonun veri tipini, diğer kolonlar da veri uzunluğu varsa üzerinde NULL izni gibi bilger içermektedir. Burada hangi kolonlara ihtiyacınız varsa onları alıp ona göre bir T-SQL Script’i oluşturmanız gerekiyor. Her kolon için ALTER UzaktakiTablo ADD BuradakiKolonAdi . . . seklinde cumleler olusturup en sonda sp_executesql ile bu cümlerleri çalıştırmanız yeterli olacaktır. Adını veriğim toollar bunun scriptini oluşturmaktadır.Kolay gelsin

    Cevapla
  14. Murat Merdogan

    Hocam Merhaba Yine bir sorum olacak,
    Diskimde tutulan sql uzantılı dosyanın sadece yolunu gosterek run edebileceğim bir yontem varmısql dızınınde bulunan com dızını altında tablediff gıbı yardımcı araclar mevcutmu ?Kolay Gelsin
    Teşekkürler

    Cevapla
  15. Ahmet Kaymaz Yazar

    Doğrudan Query Analyzer içerisinde bunu yapabilecek bir yordam bulunmamaktadır Murat. Fakat ISql, OSql veya SqlCmd gibi araçlar kullanılarak herhangi bir konumdaki dosya çalıştırılabilir.osql -Usa -PSifre -i c:\dosya.sql

    Cevapla
  16. khakan

    paradoxta oluşturulmuş(md) database içinde bulunan resimleri ilave veya değiştirme nasıl yapabilirim.
    teşekkür ederim.

    Cevapla

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

Time limit is exhausted. Please reload CAPTCHA.