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 |