9CBS Many netizens ask how to list the databases and table information, the following program is what you want,
Program mind: Get all tables with select name from sysobjects where xtype = 'u', then turn on the table, get the field name according to RS_COLUMS.FIELDS (I) .Name, FieldType (rs_colums.fields (i) .Type) Get field type , RS_COLUMS.FIELDS (i) .definedsize 'width
Since RS_COLUMS.FIELDS (i) .Type returns type is a number, a FieldType function is transformed into a Chinese type in the program.
Private sub fascist1_click () DIM CN AS New Adodb.connectionDIM RS_TABLE AS NEW AdoDB.Recordsetdim rs_colums as new adoDb.recordset
WITH CN 'Definition Connection .CursorLocation = aduseclient .provider = "SQLOLEDB" .properties. Value = "lihg" .properties ("Initial Catalog"). Value = "northwind" .properties ("user ID" .properties ) = "sa" .Properties ( "Password") = "sa" .Properties ( "prompt") = adPromptNever .ConnectionTimeout = 15 .Open If .State = adStateOpen Then Rs_Table.CursorLocation = adUseClient 'to give all the table names Rs_Table.Open "SELECT name From sysobjects WHERE xtype = 'u'", Cn, adOpenDynamic, adLockReadOnly Rs_Table.MoveFirst Do While Not Rs_Table.EOF Debug.Print Rs_Table.Fields ( "name") Rs_Colums.CursorLocation = adUseClient Rs_Colums.Open "select top 1 * from ["& rs_table.fields (" Name ") &"] ", CN, AdopenStatic, AdlockReadonly for i = 0 to rs_colums.fields.count - 1 'Cycles All columns Debug.Print Rs_Colums.fields (i) .name 'Field name Debug.Print FieldType (Rs_Colums.Fields (I) .Type) 'field type Debug.Print Rs_Colums.Fields (I) .DefinedSize' Width Next Rs_Colums.Close Rs_Table.MoveNext Loop Rs_Table.Close Set Rs_Colums = Nothing Set Rs_Table = Nothing Else MsgBox "Database connection failed, please find the system administrator to check! ", CPROGRAMNAME End End Withend Sub
'********************************************************** ******** '* Name: FieldType' * Function: Return Field Type "* Usage: FieldType (NTYPE AS Integer) '*************************** ********************************************** FUNCTION FIELDTYPE (NTYPE AS INTEGER) AS STRING SELECT CASE NTYPE Case 128 FieldType = "BINARY" Case 11 FieldType = "BIT" Case 129 FieldType = "CHAR" Case 135 FieldType = "DATETIME" Case 131 FieldType = "DECIMAL" Case 5 FieldType = "FLOAT" Case 205 FieldType = "IMAGE" Case 3 FieldType = "int" case 6 FieldType = "Money" case 130 FieldType = "nchar" case 203 fieldtype = "nText" case 131 fieldtype = "numeric" case 202 fieldType = "nvarchar" case 4 FieldType = "real" CASE 135 FieldType = "SmallDatetime" case 2 FieldType = "Smallmon EY "Case 6 FieldType =" TEXT "Case 201 FieldType =" TIMESTAMP "Case 128 FieldType =" TINYINT "Case 17 FieldType =" UNIQUEIDENTIFIER "Case 72 FieldType =" VARBINARY "Case 204 FieldType =" VARCHAR "Case 200 FieldType =" " End SelectEnd Function This program is just a prototype, which can be developed as a tool on this basis.
This program is in: VB 6.0, SQL Server 2000 running through the note to reference ActiveX Data Objects (ADO)
