query to identify all fields in a table

Newby question. We have a new vendor who will be needing to do data
convertion from our current SQL app into his new application, as part of the
implimentation. I need to identify the complete file structure in order to
get a quote for that part of the project.
I know how to export the list of the tables, but is there a similar way to
export the list of fields. OR..any query possibities that could be run per
table or mutiple tables that would list just the fields - no data?
Any help or ideas would be greatly appreciated - thanks in advance for your
time and help!
Cindy B
Hi,
im sure there is a way to query the system tables in order get the lis of
fields, but don't ask me how ;)
however, you may want to use the following excel vba macro:
Sub GetFields()
Dim A As Long
Dim TableName As String
Dim RS As ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Password=[PASSWORD];Persist
Security Info=True;User ID=[USERNAME];Initial Catalog=[DATABASE];Data
Source=[SERVERNAME]"
Conn.Open
TableName = InputBox("Enter table name: ")
If TableName = "" Then Exit Sub
Set RS = Conn.Execute("SELECT TOP 1 * FROM " & TableName)
Range("A1:A1000").Clear
For A = 1 To RS.Fields.Count
Range("A" & A).Value = RS.Fields(A - 1).Name
Next
RS.Close
Conn.Close
End Sub
just provide the necessary connection information in the connection string
(without the [ ] ), add a reference to the latest "microsoft activex data
objects" and run the macro. it will list all fields of a given table in the
current excel worksheet
"Cindy B" wrote:

> Newby question. We have a new vendor who will be needing to do data
> convertion from our current SQL app into his new application, as part of the
> implimentation. I need to identify the complete file structure in order to
> get a quote for that part of the project.
> I know how to export the list of the tables, but is there a similar way to
> export the list of fields. OR..any query possibities that could be run per
> table or mutiple tables that would list just the fields - no data?
> Any help or ideas would be greatly appreciated - thanks in advance for your
> time and help!
> --
> Cindy B
|||One way against the ANSI INFORMATIO_SCHEMA views
select c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.NUMERIC_P RECISION from
INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION
http://sqlservercode.blogspot.com/