Getting SQL 2005 Column information for field validation

Form fields should be validated to ensure that they are within the maximum allowable length. For database applications, ultimately that length is determined by the field size in the database. Any complex database application should build an keep of the database schema in memory and use that information to validate fields on a form. The following SQL 2005 query returns a single table of the most relevant information for a database, assuming the SQL login has permission to view this information. The information can be kept in memory in a dataset, or can be loaded into a dictionary for quick retrieval. Replace 'AdventureWorks' with the name of your database.


USE AdventureWorks
GO

SELECT C.Table_Name, C.Column_Name, C.Ordinal_Position, C.Column_Default, C.Is_Nullable, C.Data_Type, C.Character_Maximum_Length, C.Numeric_Precision, C.Numeric_Scale, C.DateTime_Precision

FROM information_schema.columns C

JOIN Information_Schema.Tables T ON C.Table_Name=T.Table_Name

WHERE Table_Type = 'Base Table'

ORDER BY C.table_name, C.Ordinal_Position



Hope that helps.
Joe Kunk
Okemos, MI USA