printing list of fields and their characteristics 1

[MikeT]

There's got to be an easy way to print a list of the fields and there characteristics (type, length, etc...) from a SQL 7 db. I just need a hard copy of the db design.

How do I do this?

 

[Niv3k]

Do you have VB installed? If you do, you could open the database with ADOX Security extensions as a reference, and just do a for..each on all the tables and a for..each on all the fields, and dump it into a text file. It would take ten lines of code or so. This would work on Access db's as well.

 

[MikeT]

No, and there's no chance of that happening. But, you gave me a good idea: I could write an asp script to access the db and get the field info from there. I'd have to do it one table at a time, but I only need a few tables.
Thanks for the idea!

 

[Niv3k]

You intrigued me. I found a better way: After playing around with a SQL 7 database from SQL 2000 Enterprise manager, the following worked better.

SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE, 
	CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS

Hope this works straight in SQL 7, I think it will. Just run the query from the SQL Query window, or create a new view.

 

[MikeT]

Yep, thats exactly what I wanted, thanks!