Export SQL Table Structure to Text File

[crj...@earthlink.net]

Is there an easy way to export the table sturcture of SQL Server 2000
tables to a text file or even an Excel spreadsheet? I would like to
at least obtain the "Column Name", "Data Type", "Length", and "Allow
Nulls" for all the tables in my database (Excluding the sys*
tables.).

I thought that I might be able to do this using the DTS Import/Export
Wizard. If it can be done this way, then I must be doing something
wrong.

Thanks,
C.R. Junk

[Jeffrey Williams]

Take a look at INFORMATION_SCHEMA.COLUMNS - this will have all of the
information you are looking for. To get this into an Excel
spreadsheet, you can run your query in QA and cut & paste the results
into Excel or change the output results property to create a delimited
file and import the file into Excel.

[crj...@earthlink.net]

> Take a look at INFORMATION_SCHEMA.COLUMNS - this will have all of the
> information you are looking for. To get this into an Excel
> spreadsheet, you can run your query in QA and cut & paste the results
> into Excel or change the output results property to create a delimited
> file and import the file into Excel.

Thanks Jeffery! I was able to use the following to get the info. that
I needed:
SELECT
ORDINAL_POSITION AS POSITION,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH,
COLUMN_DEFAULT,
IS_NULLABLE AS ALLOW_NULL
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
Table_Name LIKE 'tbl%' or
Table_Name like 'res%'
ORDER BY
Table_Name,
Position

Sincerely,
C.R. Junk