How do i get a list of columns and data types from a table using SQL?

Question

I need to extract column names and their corresponding data types from ~200 tables on a SQL database. I could do this by hand by using MS Enterprise Manager, but a script similar to the functionality of this script would be helpful.

select name from sysobjects where type = 'U'

Answer 1

I'm not a sql server person but if you use the sysobjects table as you say...



select * from sysobjects where xtype = 'U'



The ID column links to the syscolumns table -

select * from syscolumns where id = ?; // Using ID from select above.



( See http://msdn.microsoft.com/en-us/library/aa260398%28v=SQL.80%29.aspx for columns in syscolumns ). It looks like the xtype column refers to the table systables which gives you the actual name of the datatype.

You could put this all together in one statement with the appropriate joins if thats what you need. Linking the sysobjects.id to syscolumns.id and syscolumns.xtype to systypes.xtype.

Answer 2

the previous responses are a number of the extra regularly occurring procedures for handing this. i could propose applying ENUM provided that that record is used for a single table...in any different case, you need to repeat the ENUM in each table precisely the comparable way (this is in simple terms inquiring for the archives to get out of sync.) Having the record in its very own table and used as a foreign places key's the a procedures extra suitable (and conventional) technique. via doing it that way, referential integrity ensures you are able to purely placed valid values in any tables that use the class as a foreign places key.

Answer 3

The two biggest commands I can think of would be:



SELECT * FROM INFORMATION_SCHEMA.TABLES



SELECT * FROM INFORMATION_SCHEMA.COLUMNS