Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P2

Tham khảo tài liệu programming microsoft sql server 2000 with microsoft visual basic .net - p2, công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả
Nội dung trích xuất từ tài liệu:
Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P2 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 app- licat 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 Chapter02 database. USE Chapter02 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 Chapter02.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ’EmailContacts’ GO --List data on columns constrained as keys in --the EmailContacts table. SELECT * FROM Chapter02.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 hePlease purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 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 il ...