// 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

// @TableName is obviously the TABLE name u use
// @ColumnName is obviously the COLUMN name u use

   1  
   2  ---Get next ID number
   3  	DECLARE 
   4  		@ID int
   5  	
   6  	IF (SELECT count(*) FROM @TableName ) > 0
   7  		BEGIN
   8  			SELECT @ID  = max(ColumnName ) from @TableName
   9  			SET @ID = @ID + 1 
  10  		END
  11  	ELSE
  12  	BEGIN
  13  		SET @ID  = 1
  14  	END