:::: MENU ::::

get list of column meta data in a table from INFORMATION_SCHEMA

  • Mar 26 / 2009
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts

get list of column meta data in a table from INFORMATION_SCHEMA

To day some one asked me that how to get columns count in a table. For information like list of columns of a table, i mostly used sysobjects, syscolumns and systypes by joining these. During a little googling i find that meta data about columns of a table can be efficiently and easily found in system views like INFORMATION_SCHEMA.Columns. For example following script may generate a list of columns in a table along with there relevanmt meta data

 
SELECT  
 table_name AS [TABLE Name],  
 column_name AS [COLUMN Name],  
 data_type AS [COLUMN DATA TYPE],  
 character_maximum_length AS [MAX length],  
 is_nullable as[Nullable],  
 ordinal_position AS [Postion IN TABLE]  
 FROM INFORMATION_SCHEMA.Columns  
 WHERE table_name = 'tableNamehere'  
 ORDER BY ordinal_position  

Columns aliases are self explanatory. Above script may be modified through altering parameters in select, where and order by clauses. Using such system views is far more easy and efficient than to use system tables.
And also to get count of columns in a table i just modified the script to simpler form as

 
SELECT count(column_name)  
 FROM INFORMATION_SCHEMA.Columns  
 WHERE table_name = 'tableNameHere'  
Consult us to explore the Databases. Contact us