// Select database schema.
//This could be used to recreate or test for existence of columns/tables
//or could also be used to create a database template system to enable the writing //of database schema into txt template file to be recreated again by reading the //txt file via an application
//
//You can also use SELECT * instead of defining each schema property (column)

   1  
   2  SELECT TABLE_CATALOG
   3  , TABLE_SCHEMA
   4  , TABLE_NAME
   5  , ORDINAL_POSITION
   6  , COLUMN_DEFAULT
   7  , IS_NULLABLE
   8  , DATA_TYPE
   9  , CHARACTER_MAXIMUM_LENGTH
  10  , COLLATION_NAME 
  11  FROM 
  12  INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = (N'Persons')

// Check to see if column exists and then create if not

   1  
   2  IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
   3  WHERE TABLE_NAME = ‘TEST’ AND COLUMN_NAME = ‘TEST_DATE’)
   4  BEGIN
   5     ALTER TABLE TEST ADD TEST_DATE DATETIME
   6  END