N ot e in VB.NET

Encoding ECC200 in VB.NET N ot e

infor m at ion about views as well as t ables. Specify a TABLE_TYPE colum n value of VI EW in t he WHERE clause for a SELECT st at em ent t o ret urn only v iews.

Wit h t he COLUMNS v iew of t he I NFORMATI ON_SCHEMA, you can ret urn inform at ion about colum ns in a dat abase. The t hird bat ch illust rat es t his applicat ion. I t also r eveals a new sy nt ax for specify ing t he dat abase ser ving as t he source for t he v iew. Not ice t hat t he specificat ion of t he v iew nam e has t hr ee part s. The first of t hese is t he dat abase nam e Chapt er02. Designat ing a dat abase nam e as t he first part r em ov es t he need t o designat e a dat abase cont ext w it h a USE st at em ent . This is because no m at t er w hat dat abase cont ext t he st at em ent ex ecut es, it always ext ract s infor m at ion fr om t he dat abase t hat is, t he first part of t he I NFORMATI ON_SCHEMA view nam e. The second and t hird part s follow t he conv ent ion for t he pr eceding bat ches except for t he nam e of t he specific I NFORMATI ON_SCHEMA v iew ( COLUMNS) . The sam ple also includes a WHERE clause t o reference a part icular t able in part icular, Em ailCont act s. Wit hout t he WHERE clause, t he T- SQL st at em ent in t he bat ch will ret ur n inform at ion for all t he colum ns w it hin t he Chapt er02 dat abase, including t hose from syst em and user - defined t ables. The final bat ch shows t he I NFORMATI ON_SCHEMA sy nt ax for r eport ing about t he keys in a dat abase. These include t he pr im ar y k eys, for eign k eys, and unique keys. The inform at ion is r eally about t he colum ns on w hich an applicat ion defines it s keys. As w it h t he pr eceding bat ch, t his sam ple rest r ict s t he r esult only t o keys for t he Em ailCont act s t able. --INFORMATION_SCHEMA_Samples --List databases on current server. USE master SELECT * FROM INFORMATION_SCHEMA.SCHEMATA GO --List user-defined tables in 02 database. USE 02 SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE NOT(SUBSTRING(TABLE_NAME,1,3) = sys OR SUBSTRING(TABLE_NAME,1,3) = dtp ) GO --List all columns in EmailContacts table. SELECT * FROM 02.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = EmailContacts GO --List data on columns constrained as keys in --the EmailContacts table. SELECT * FROM 02.INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = EmailContacts GO Figur e 2- 2 displays an excerpt from t he r esult set for t he preceding script . The ret ur n for each bat ch begins wit h a new set of colum n headers. The list of dat abases includes our user - defined dat abase, Chapt er02, along w it h t he t wo SQL Ser ver sam ple dat abases, pubs and Nort hwind, as well as t he four syst em dat abases. The second header shows j ust one r ow for t he lone t able in Chapt er02. The t hird header r ows rev eal t he nam es of t he four colum ns wit hin t he Em ailCont act s t able. This view pr ov ides m uch addit ional inform at ion about each colum n, such as it s nullabilit y, dat a t ype, and relat ed set t ings, including it s precision and scale if appropriat e. The row for t he last set of colum n headers prov ides inform at ion about t he lone k ey for t he Em ailCont act s t able. This is t he t able s pr im ar y k ey . Each k ey has a nam e, w hich appears in t he

CONSTRAI NT_NAME colum n. Because our synt ax for t he creat ion of t he t able didn t specify a nam e for t he pr im ar y k ey , t he last row of out put in Figure 2- 2 shows t he syst em - generat ed nam e for t he t able s pr im ary key in t he CONSTRAI NT_NAME colum n. A subsequent sam ple in t he Scr ipt ing Key s and I ndexes sect ion illust rat es t he synt ax for assigning a specific nam e t o a pr im ar y key . Figu r e 2 - 2 . Sa m ple ou t pu t from a se t of fou r T- SQL ba t ch e s illust r a t in g t h e b eh avior of I N FORM ATI ON _ SCH EM A vie w s.

I NFORMATI ON_SCHEMA offers m any m or e v iew s besides t hose illust rat ed in t he preceding four bat ches. For exam ple, you can gat her inform at ion about check const raint s for colum n v alues, t able const raint s, st ored pr ocedur es, and userdefined funct ions. Refer t o t he I nform at ion Schem a View t opic in Book s Online for an overv iew of t he I NFORMATI ON_SCHEMA views along w it h links defining t he result set for each t ype of view available.