How to find out names and data types of table fields

[pigsie]

Hi I'm aware that there are some system tables that hold the name and datatypes of table fields how can i retreive these in a query. Also is there a way (again using a query or stored proc) to find out other info about the field such as range,defaults, constraints etc)?


Thanks

 

[pankajv]

Below query returns the field name, Corresponding Datatype, Deafult Value, Nullable Flag, Maximum Legth:

SELECT Column_name, Data_type, Column_Default,Is_Nullable,
Character_Maximum_Length FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = '<Table Name>'

// in the above query INFORMATION_SCHEMA.Columns is a view which contains all the information.

There are many other views from where you can find the other information needed. If you have online help search for INFORMATION_SCHEMA and you will get all the relevant information.

 

[jamey]

You can also run the stored procedure sp_columns. This stored procedure will return all of the columns in the specified table with their data types and field lengths.

Code:
exec sp_columns @table_name = '[YourTableName]'

Jamey