INFORMATION_SCHEMA ile veritabanindaki tablo ve view isimlerine ulasmak. Bir tabloya ait Kolon bilgileri nasil alinir.

SQL-92 standarti olan INFORMATION_SCHEMA viewleri seçilen veritabanina ait meta datalara ulasmamizi saglar. Bunlardan bazilari veritabaninda bulunan tablo veya view isimleri, bunlara ait kolon bilgileri, bir viewin verileri hangi tablolardan aldigi olarak sayilabilir. INFORMATION_SCHEMA viewlerini nasil kullanacagimizi ve bu bilgilere nasil erisecegimizi kisa ve örneklerle açiklamaya çalisalim.

INFORMATION_SCHEMA altinda bulunan viewler :

Information_Schema.Check_Constraints

Information_Schema.Column_Domain_Usage

Information_Schema.Column_Privileges

Information_Schema.Columns

Information_Schema.Constraint_Column_Usage

Information_Schema.Constraint_Table_Usage

Information_Schema.Domain_Constraints

Information_Schema.Domains

Information_Schema.Key_Column_Usage

Information_Schema.Parameters

Information_Schema.Referential_Constraints

Information_Schema.Routine_Columns

Information_Schema.Routines

Information_Schema.Schemata

Information_Schema.Table_Constraints

Information_Schema.Table_Privileges

Information_Schema.Tables

Information_Schema.Views

Information_Schema.View_Column_Usage

Information_Schema.View_Table_Usage

 

 Yukarida listesi verilen viewlerden bazilarina deginelim. Örneklerimizi Microsoft AdventureWorks veritabani üzerinde yapacagiz. Siz sisteminizdeki herhangi bir veritbanini kullanarak örnekleri deneyebilirsiniz.
.

Veritabanindaki tüm tablolarin listesi:

SELECT

   TABLE_CATALOG as DatabaseAdi

  ,TABLE_SCHEMA as SchemaAdi

  ,TABLE_NAME as  TabloAdi

 FROM INFORMATION_SCHEMA.TABLES

 WHERE TABLE_TYPE = ‘BASE TABLE’

 ORDER BY SchemaAdi, TabloAdi

Yukaridaki sorguyu çalistirdigimizda veritabaninda bulunan tüm tablo isimlerini bu tablolarin sema bilgilerini liste halinde döndürür. Veritabaninda tablo sayisi çok oldugu için çiktiya sadece ilk yirmisini gösteriyoruz.

DatabaseAdi

SchemaAdi

TabloAdi

AdventureWorks

dbo

AWBuildVersion

AdventureWorks

dbo

DatabaseLog

AdventureWorks

dbo

ErrorLog

AdventureWorks

HumanResources

Department

AdventureWorks

HumanResources

Employee

AdventureWorks

HumanResources

EmployeeAddress

AdventureWorks

HumanResources

EmployeeDepartmentHistory

AdventureWorks

HumanResources

EmployeePayHistory

AdventureWorks

HumanResources

JobCandidate

AdventureWorks

HumanResources

Shift

AdventureWorks

Person

Address

AdventureWorks

Person

AddressType

AdventureWorks

Person

Contact

AdventureWorks

Person

ContactType

AdventureWorks

Person

CountryRegion

AdventureWorks

Person

StateProvince

AdventureWorks

Production

BillOfMaterials

AdventureWorks

Production

Culture

AdventureWorks

Production

Document

AdventureWorks

Production

Illustration

 

Veritabanindaki tüm viewlerin listesi:

SELECT

   TABLE_CATALOG as DatabaseAdi

  ,TABLE_SCHEMA as SchemaAdi

  ,TABLE_NAME as  ViewAdi

 FROM INFORMATION_SCHEMA.TABLES

 WHERE TABLE_TYPE = ‘VIEW’

ORDER BY SchemaAdi, ViewAdi

 

DatabaseAdi

SchemaAdi

ViewAdi

AdventureWorks

HumanResources

vEmployee

AdventureWorks

HumanResources

vEmployeeDepartment

AdventureWorks

HumanResources

vEmployeeDepartmentHistory

AdventureWorks

HumanResources

vJobCandidate

AdventureWorks

HumanResources

vJobCandidateEducation

AdventureWorks

HumanResources

vJobCandidateEmployment

AdventureWorks

Person

vAdditionalContactInfo

AdventureWorks

Person

vStateProvinceCountryRegion

AdventureWorks

Production

vProductAndDescription

AdventureWorks

Production

vProductModelCatalogDescription

AdventureWorks

Production

vProductModelInstructions

AdventureWorks

Purchasing

vVendor

AdventureWorks

Sales

vIndividualCustomer

AdventureWorks

Sales

vIndividualDemographics

AdventureWorks

Sales

vSalesPerson

AdventureWorks

Sales

vSalesPersonSalesByFiscalYears

AdventureWorks

Sales

vStoreWithDemographics

 

Bir viewde kullanilan tablo bilgilerinin listesi:

SELECT VIEW_NAME,TABLE_NAME FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE

ORDER BY TABLE_SCHEMA, TABLE_NAME

 

VIEW_NAME

TABLE_NAME

vEmployeeDepartment

Department

vEmployeeDepartmentHistory

Department

vEmployee

Employee

vProductAndDescription

ProductModelProductDescriptionCulture

vEmployeeDepartmentHistory

Employee

vSalesPerson

Employee

vSalesPersonSalesByFiscalYears

Employee

vEmployee

EmployeeAddress

vSalesPerson

EmployeeAddress

vEmployeeDepartment

EmployeeDepartmentHistory

vEmployeeDepartmentHistory

EmployeeDepartmentHistory

vJobCandidate

JobCandidate

vJobCandidateEducation

JobCandidate

vJobCandidateEmployment

JobCandidate

vEmployeeDepartmentHistory

Shift

vEmployee

Address

vVendor

Address

 

Tablo ve viewlere ait kolon bilgileri:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

 

Yukaridaki sorgu sonucunda veritabaninda bulunan tüm tablo ve viewlerin kolonlarina ait bilgiler lstelenir. Bu bilgilerin çikti tablosunda çok fazla kolon oldugu için sadece kolon isimlerini içeren sonuç tablosunu ekledim. Sorguyu kendiniz çalistirarak sonucu görebilirsiniz.

TABLE_NAME

COLUMN_NAME

ORDINAL_POSITION

AWBuildVersion

SystemInformationID

1

AWBuildVersion

Database Version

2

AWBuildVersion

VersionDate

3

AWBuildVersion

ModifiedDate

4

DatabaseLog

DatabaseLogID

1

DatabaseLog

PostTime

2

DatabaseLog

DatabaseUser

3

DatabaseLog

Event

4

DatabaseLog

Schema

5

DatabaseLog

Object

6

DatabaseLog

TSQL

7

DatabaseLog

XmlEvent

8

ErrorLog

ErrorLogID

1

ErrorLog

ErrorTime

2

ErrorLog

UserName

3

ErrorLog

ErrorNumber

4

ErrorLog

ErrorSeverity

5

 

Veritabaninda bulunan stored procedure ve functionlara ait scriptleri dahil bilgiler.

SELECT

       Routine_Schema as SchemaAdi,

       Routine_Name as SProcAdi,

       Routine_Definition as SProcScripti

FROM INFORMATION_SCHEMA.ROUTINES

ORDER BY SchemaAdi, SProcAdi

 

Stored Procedure ve Functionlara ait Parametre Bilgileri

SELECT

SPECIFIC_SCHEMA,

SPECIFIC_NAME,

ORDINAL_POSITION,

PARAMETER_MODE,

PARAMETER_NAME,

DATA_TYPE

FROM INFORMATION_SCHEMA.PARAMETERS

ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION

 

Bu sorgu sonucunda giren ve çikan tüm parametre bilgileri asagidaki gibi listelenir.
.

SPECIFIC_NAME

ORDINAL_POSITION

PARAMETER_MODE

PARAMETER_NAME

DATA_TYPE

ufnGetAccountingEndDate

0

OUT

 

datetime

ufnGetAccountingStartDate

0

OUT

 

datetime

ufnGetContactInformation

1

IN

@ContactID

int

ufnGetDocumentStatusText

0

OUT

 

nvarchar

ufnGetDocumentStatusText

1

IN

@Status

tinyint

ufnGetProductDealerPrice

0

OUT

 

money

ufnGetProductDealerPrice

1

IN

@ProductID

int

ufnGetProductDealerPrice

2

IN

@OrderDate

datetime

ufnGetProductListPrice

0

OUT

 

money

ufnGetProductListPrice

1

IN

@ProductID

int

ufnGetProductListPrice

2

IN

@OrderDate

datetime

uspGetEmployeeManagers

1

IN

@EmployeeID

int

uspGetManagerEmployees

1

IN

@ManagerID

int

uspGetWhereUsedProductID

1

IN

@StartProductID

int

uspGetWhereUsedProductID

2

IN

@CheckDate

datetime

 

Bir cevap yazın

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