How does SQL Server dynamically add fields to a table?

This example shows that in SQL Server, when you add a field to a table, a corresponding field is automatically added to another table through the trigger.

1. Create a field information table fields

 

Create   Table   [ DBO ] . [ Fields ] (
  [ Fieldid ]   [ Int ]   Identity ( 1 , 1 ) Not   Null ,
  [ Name ]   [ Varchar ] ( 50 ) Collate chinese_prc_ci_as Not   Null ,
  [ Datatype ]   [ Varchar ] ( 50 ) Collate chinese_prc_ci_as Not   Null  
) On   [ Primary ]
Go

Alter   Table   [ DBO ] . [ Fields ]   Add  
  Constraint   [ Pk_fields ]   Primary   Key Clustered  
(
[ Fieldid ]
) On   [ Primary ]  
Go

 

2. Create a dynamic field table customeres. When fields adds a record, this table adds a field accordingly.

 

Create   Table   [ DBO ] . [ Customeres ] (
  [ ID ]   [ Int ]   Identity ( 1 , 1 ) Not   Null  
) On   [ Primary ]
Go

Alter   Table   [ DBO ] . [ Customeres ]   Add  
  Constraint   [ Pk_customeres ]   Primary   Key Clustered  
(
[ ID ]
) On   [ Primary ]  
Go

 

3. Add a trigger for the table fields. when data is added, the trigger is automatically called. Note: The length of a field is hard-coded only for demonstration purposes. You should dynamically adjust the length as needed.

 

Create   Trigger Trigger_addfield On Fields
For   Insert
As

Declare   @ Fieldid   Int ,
  @ Name   Varchar ( 50 ),
  @ Datatype   Varchar ( 50 ),
  @ SQL   Varchar ( 1000 )

Select   @ Fieldid   = Fieldid, @ Name   =   [ Name ] , @ Datatype   = Datatype
From Inserted
If   Not   Exists ( Select   *   From Syscolumns
  Where ID = Object_id ( ' Customeres ' )
  And Name = @ Name )
Begin
  Set   @ SQL   =   ' Alter table customeres add '   +   @ Name   +   '   '  
+   @ Datatype   +   ' (64) null '
  Exec ( @ SQL )
End
Print   @ Name   +   ' , '   +   @ Datatype

4. Run the command in query analysis.

Insert   Into Fields ( [ Name ] , Datatype) Values ( ' Name ' , ' Varchar ' )

Reference Source:
Add a column to a table unless it already exists
Using triggers in ms SQL Server