ASP fortgeschrittene DB-Techniken: Datenbank-Schema auslesen

ASP Datenbankanbindung: Datenbank-Schema auslesen

Beispiel einer Access-DB

0 adSchemaAsserts
is not supported

2 adSchemaCharacterSets
is not supported

3 adSchemaCollations
is not supported

4 adSchemaColumns

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	COLUMN_GUID	COLUMN_PROPID	ORDINAL_POSITION	COLUMN_HASDEFAULT	COLUMN_DEFAULT	COLUMN_FLAGS	IS_NULLABLE	DATA_TYPE	TYPE_GUID	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_CATALOG	CHARACTER_SET_SCHEMA	CHARACTER_SET_NAME	COLLATION_CATALOG	COLLATION_SCHEMA	COLLATION_NAME	DOMAIN_CATALOG	DOMAIN_SCHEMA	DOMAIN_NAME	DESCRIPTION
		MSysAccessObjects	Data			1	False		106	True	128		3992	3992
		MSysAccessObjects	ID			2	False		122	True	3				10
		MSysAccessXML	Id			1	False		90	False	3				10
		MSysAccessXML	LValue			2	False		234	True	128		0	0
		MSysAccessXML	ObjectGuid			3	False		122	True	72
		MSysAccessXML	ObjectName			4	False		106	True	130		65	130
		MSysAccessXML	Property			5	False		106	True	130		65	130
		MSysAccessXML	Value			6	False		106	True	130		255	510
		tblfieldtest	Autowert			1	False		90	False	3				10
		tblfieldtest	Byte			8	False		122	True	17				3
		tblfieldtest	Datum			15	False		122	True	7						0
		tblfieldtest	Dezimal			13	False		122	True	131				18	3
		tblfieldtest	Double			12	False		122	True	5				15
		tblfieldtest	fldEingabeErforderlich			4	False		74	False	130		50	100
		tblfieldtest	fldLeereZeichenfolge			5	False		106	True	130		30	60
		tblfieldtest	Hyperlink			20	False		234	True	130		0	0
		tblfieldtest	IndiziertMitDuplikaten			6	False		106	True	130		50	100
		tblfieldtest	Integer			9	False		122	True	2				5
		tblfieldtest	Ja_Nein			18	False		90	False	11		2
		tblfieldtest	LongInteger			10	False		122	True	3				10
		tblfieldtest	Memo			7	False		234	True	130		0	0
		tblfieldtest	Ole_Objekt			19	False		234	True	128		0	0
		tblfieldtest	PKText			2	False		106	True	130		5	10
		tblfieldtest	ReplikationsID			14	False		122	True	72
		tblfieldtest	Single			11	False		122	True	4				7
		tblfieldtest	Text			3	False		106	True	130		255	510
		tblfieldtest	VergangenesDatum			16	False		122	True	7						0
		tblfieldtest	Waehrung			17	True	0	122	True	6				19
		tblMehrfachKey	pk1			1	False		122	True	3				10
		tblMehrfachKey	pk2			2	False		122	True	3				10
		tblMehrfachKey	pk3			3	False		106	True	130		50	100
		tblMehrfachKey	text			4	False		106	True	130		50	100

5 adSchemaCheckConstraints

CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	CHECK_CLAUSE	DESCRIPTION
		[tblfieldtest].[VergangenesDatum].ValidationRule	<=Now()

6 adSchemaConstraintColumnUsage

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	COLUMN_GUID	COLUMN_PROPID	CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME
		MSysAccessObjects	ID					AOIndex
		MSysAccessXML	Id					Id
		MSysAccessXML	ObjectGuid					ObjectGuidProperty
		MSysAccessXML	ObjectName					ObjectNameProperty
		MSysAccessXML	Property					ObjectGuidProperty
		MSysAccessXML	Property					ObjectNameProperty
		tblfieldtest	Autowert					PrimaryKey
		tblfieldtest	PKText					PrimaryKey
		tblfieldtest	VergangenesDatum					ValidationRule
		tblMehrfachKey	pk1					PrimaryKey
		tblMehrfachKey	pk2					PrimaryKey
		tblMehrfachKey	pk3					PrimaryKey

7 adSchemaConstraintTableUsage
is not supported

8 adSchemaKeyColumnUsage

CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	COLUMN_GUID	COLUMN_PROPID	ORDINAL_POSITION
		AOIndex			MSysAccessObjects	ID			1
		Id			MSysAccessXML	Id			1
		ObjectGuidProperty			MSysAccessXML	ObjectGuid			1
		ObjectGuidProperty			MSysAccessXML	Property			2
		ObjectNameProperty			MSysAccessXML	ObjectName			1
		ObjectNameProperty			MSysAccessXML	Property			2
		PrimaryKey			tblfieldtest	Autowert			1
		PrimaryKey			tblfieldtest	PKText			2
		PrimaryKey			tblMehrfachKey	pk1			1
		PrimaryKey			tblMehrfachKey	pk2			2
		PrimaryKey			tblMehrfachKey	pk3			3

9 adSchemaReferentialContraints

CONSTRAINT_CATALOG

CONSTRAINT_SCHEMA

CONSTRAINT_NAME

UNIQUE_CONSTRAINT_CATALOG

UNIQUE_CONSTRAINT_SCHEMA

UNIQUE_CONSTRAINT_NAME

MATCH_OPTION

UPDATE_RULE

DELETE_RULE

DESCRIPTION

10 adSchemaTableConstraints

CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE	IS_DEFERRABLE	INITIALLY_DEFERRED	DESCRIPTION
		[VergangenesDatum].ValidationRule			tblfieldtest	CHECK	False	False
		AOIndex			MSysAccessObjects	PRIMARY KEY	False	False
		Id			MSysAccessXML	PRIMARY KEY	False	False
		ObjectGuidProperty			MSysAccessXML	UNIQUE	False	False
		ObjectNameProperty			MSysAccessXML	UNIQUE	False	False
		PrimaryKey			tblfieldtest	PRIMARY KEY	False	False
		PrimaryKey			tblMehrfachKey	PRIMARY KEY	False	False

11 adSchemaColumnsDomainUsage
is not supported

12 adSchemaIndexes

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	INDEX_CATALOG	INDEX_SCHEMA	INDEX_NAME	PRIMARY_KEY	UNIQUE	CLUSTERED	TYPE	FILL_FACTOR	INITIAL_SIZE	NULLS	SORT_BOOKMARKS	AUTO_UPDATE	NULL_COLLATION	ORDINAL_POSITION	COLUMN_NAME	COLUMN_GUID	COLUMN_PROPID	COLLATION	CARDINALITY	PAGES	FILTER_CONDITION	INTEGRATED
		MSysAccessObjects			AOIndex	True	True	False	1	100	4096	1	False	True	4	1	ID			1	5	1		True
		MSysAccessXML			Id	True	True	False	1	100	4096	1	False	True	4	1	Id			1	0	1		True
		MSysAccessXML			ObjectGuidProperty	False	True	False	1	100	4096	0	False	True	4	1	ObjectGuid			1	0	1		True
		MSysAccessXML			ObjectGuidProperty	False	True	False	1	100	4096	0	False	True	4	2	Property			1	0	1		True
		MSysAccessXML			ObjectNameProperty	False	True	False	1	100	4096	0	False	True	4	1	ObjectName			1	0	1		True
		MSysAccessXML			ObjectNameProperty	False	True	False	1	100	4096	0	False	True	4	2	Property			1	0	1		True
		tblfieldtest			PrimaryKey	True	True	False	1	100	4096	1	False	True	4	1	Autowert			1	1	1		True
		tblMehrfachKey			PrimaryKey	True	True	False	1	100	4096	1	False	True	4	1	pk1			1	2	1		True
		tblfieldtest			PrimaryKey	True	True	False	1	100	4096	1	False	True	4	2	PKText			1	1	1		True
		tblMehrfachKey			PrimaryKey	True	True	False	1	100	4096	1	False	True	4	2	pk2			1	2	1		True
		tblMehrfachKey			PrimaryKey	True	True	False	1	100	4096	1	False	True	4	3	pk3			1	2	1		True
		tblfieldtest			IndiziertMitDuplikaten	False	False	False	1	100	4096	0	False	True	4	1	IndiziertMitDuplikaten			1	1	1		True
		tblfieldtest			ReplikationsID	False	False	False	1	100	4096	0	False	True	4	1	ReplikationsID			1	1	1		True

13 adSchemaColumnPrivileges
is not supported

14 adSchemaTablePrivileges
is not supported

15 adSchemaUsagePrivileges
is not supported

16 adSchemaProcedures

PROCEDURE_CATALOG

PROCEDURE_SCHEMA

PROCEDURE_NAME

PROCEDURE_TYPE

PROCEDURE_DEFINITION

DESCRIPTION

DATE_CREATED

DATE_MODIFIED

17 adSchemaSchemata
is not supported

18 adSchemaSQLLanguages
is not supported

19 adSchemaStatistics

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	CARDINALITY
		MSysAccessObjects	5
		MSysAccessXML	0
		MSysACEs	61
		MSysObjects	21
		MSysQueries	0
		MSysRelationships	0
		tblfieldtest	1
		tblMehrfachKey	2

20 adSchemaTables

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_GUID	DESCRIPTION	TABLE_PROPID	DATE_CREATED	DATE_MODIFIED
		MSysAccessObjects	ACCESS TABLE				07.07.2004 15:15:28	07.07.2004 15:15:28
		MSysAccessXML	ACCESS TABLE				07.07.2004 15:20:13	07.07.2004 15:20:13
		MSysACEs	SYSTEM TABLE				07.07.2004 15:15:28	07.07.2004 15:15:28
		MSysObjects	SYSTEM TABLE				07.07.2004 15:15:28	07.07.2004 15:15:28
		MSysQueries	SYSTEM TABLE				07.07.2004 15:15:28	07.07.2004 15:15:28
		MSysRelationships	SYSTEM TABLE				07.07.2004 15:15:28	07.07.2004 15:15:28
		tblfieldtest	TABLE				07.07.2004 15:20:13	14.07.2004 13:33:32
		tblMehrfachKey	TABLE				07.07.2004 23:11:58	14.07.2004 13:29:20

21 adSchemaTranslations
is not supported

22 adSchemaProviderTypes

TYPE_NAME

DATA_TYPE

COLUMN_SIZE

LITERAL_PREFIX

LITERAL_SUFFIX

CREATE_PARAMS

IS_NULLABLE

CASE_SENSITIVE

SEARCHABLE

UNSIGNED_ATTRIBUTE

FIXED_PREC_SCALE

AUTO_UNIQUE_VALUE

LOCAL_TYPE_NAME

MINIMUM_SCALE

MAXIMUM_SCALE

GUID

TYPELIB

VERSION

IS_LONG

BEST_MATCH

IS_FIXEDLENGTH

Short

True

False

True

False

True

Long

True

False

True

False

True

Single

True

False

True

Double

True

False

True

Currency

True

False

True

False

True

DateTime

True

False

True

False

True

Bit

False

True

False

True

Byte

True

False

True

False

True

GUID

True

False

True

False

True

BigBinary

128

4000

True

False

True

False

LongBinary

128

1073741823

True

False

True

False

True

False

VarBinary

128

510

max length

True

False

True

False

True

False

LongText

130

536870910

True

False

True

False

True

False

VarChar

130

255

max length

True

False

True

False

True

False

Decimal

131

precision,scale

True

False

True

False

True

23 adSchemaViews

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

VIEW_DEFINITION

CHECK_OPTION

IS_UPDATABLE

DESCRIPTION

DATE_CREATED

DATE_MODIFIED

24 adSchemaViewColumnUsage
is not supported

25 adSchemaViewTableUsage
is not supported

26 adSchemaProcedureParameters
is not supported

27 adSchemaForeignKeys

PK_TABLE_CATALOG

PK_TABLE_SCHEMA

PK_TABLE_NAME

PK_COLUMN_NAME

PK_COLUMN_GUID

PK_COLUMN_PROPID

FK_TABLE_CATALOG

FK_TABLE_SCHEMA

FK_TABLE_NAME

FK_COLUMN_NAME

FK_COLUMN_GUID

FK_COLUMN_PROPID

ORDINAL

UPDATE_RULE

DELETE_RULE

PK_NAME

FK_NAME

DEFERRABILITY

28 adSchemaPrimaryKeys

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	COLUMN_GUID	COLUMN_PROPID	ORDINAL	PK_NAME
		MSysAccessObjects	ID			1	AOIndex
		MSysAccessXML	Id			1	Id
		tblfieldtest	PKText			2	PrimaryKey
		tblfieldtest	Autowert			1	PrimaryKey
		tblMehrfachKey	pk3			3	PrimaryKey
		tblMehrfachKey	pk2			2	PrimaryKey
		tblMehrfachKey	pk1			1	PrimaryKey

29 adSchemaProcedureColumns
is not supported

Letzter Update: 29.01.2012 15:49

Zurück zur Liste mit ASP-Beispielen auf www.ecotronics.ch