Post
Ask Question

Consistent Table Structure In SQL Server

We generally maintain consistent table structure in database. Like we maintain CreatedDate to store record creation date, Created By to store user id of the user and similarly LastModifiedDate and LastModifiedBy, etc. columns in every table. Whenever we create a new table, we have to add these columns with their default value. It’s a repetitive thing.

So we can create a procedure which will create a table with default structure.

  1. CREATE PROCEDURE [dbo].[CreateTable]    
  1. (@TableName NVARCHAR(MAX))   
  2. as   
  3. BEGIN  
  4.   
  5. DECLARE @sql nvarchar(max);  
  6.   
  7. SET @sql = N'CREATE TABLE '+@TableName+'  
  8.  (  
  9.    ChangeMe BIGINT PRIMARY KEY IDENTITY,  
  10.    CreatedDate DATETIME DEFAULT GETDATE(),  
  11.    CreatedBy BIGINT DEFAULT 1,  
  12.    LastModifiedDate DATETIME DEFAULT GETDATE(),  
  13.    LastModifiedBy BIGINT DEFAULT 1,  
  14.  )'  
  15.  EXEC sp_executesql @sql, N'@TableName NVARCHAR(MAX)', @TableName  
  16. END  

We need to just call the stored procedure with TableName as parameter.

  1. exec CreateTable 'TestTable' ;