All tables and fields in the SQL Server database

Program Idea: Use select name from sysobjects where xtype = 'U' to obtain all tables, and then open the table cyclically, according to rs_colums.fields (I ). name: field name. fieldtype (rs_colums.fields (I ). type) to obtain the field type, rs_colums.fields (I ). definedsize 'width

Because rs_colums.fields (I). Type Returns numbers, a fieldtype function is written in the program to convert it to a Chinese type.

Private sub commandementclick ()

Dim cn as new ADODB. Connection

Dim rs_table as new ADODB. recordset

Dim rs_colums as new ADODB. recordset

With Cn 'define a connection

. Cursorlocation = aduseclient

. Provider = "sqloledb"

. Properties ("Data Source"). value = "lihg"

. Properties ("Initial catalog"). value = "northwind"

. Properties ("User ID") = "sa"

. Properties ("password") = "sa"

. Properties ("prompt") = adpromptnever

. Connectiontimeout = 15

. Open

If. State = adstateopen then

Rs_table.cursorlocation = aduseclient 'to get all 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 'loop 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 check with the system administrator! ", 16, cprogramname

End

End if

End

End sub

'*************************************** ******************

'* Name: fieldtype

'* Function: Return Field Type

'* Usage: fieldtype (ntype as integer)

'*************************************** ******************

Function fieldtype (ntype as integer) as string

Select case ntype

Case 1, 128

Fieldtype = "binary"

Case 11

Fieldtype = "bit"

Case 1, 129

Fieldtype = "char"

Case 1, 135

Fieldtype = "datetime"

Case 1, 131

Fieldtype = "decimal"

Case 5

Fieldtype = "float"

Case 1, 205

Fieldtype = "image"

Case 3

Fieldtype = "int"

Case 6

Fieldtype = "money"

Case 1, 130

Fieldtype = "nchar"

Case 1, 203

Fieldtype = "ntext"

Case 1, 131

Fieldtype = "numeric"

Case 1, 202

Fieldtype = "nvarchar"

Case 4

Fieldtype = "real"

Case 1, 135

Fieldtype = "smalldatetime"

Case 2

Fieldtype = "smallmoney"

Case 6

Fieldtype = "text"

Case 1, 201

Fieldtype = "timestamp"

Case 1, 128

Fieldtype = "tinyint"

Case 17

Fieldtype = "uniqueidentifier"

Case 72

Fieldtype = "varbinary"

Case 1, 204

Fieldtype = "varchar"

Case 1, 200

Fieldtype = ""

End select

End Function

This program is just a prototype and can be developed as a tool on this basis.

This program runs in: VB 6.0, SQL Server 2000 through

Note that ActiveX Data Objects (ADO) must be referenced in the program)