Creating a Dynamic Table In SQL Server

This blog describes creating a dynamic table name and Alter Table of User Registration Form

Introduction

How to Create A Dynamic Table with Dynamic Name and Parameter in SQL Server. To Use Create A Dynamic Form As per Your Website Requirement to reference of this code.

Step 1: Create A Table Name:

DECLARE @ptablename VARCHAR(max) = ( SELECT CONVERT(VARCHAR(50), 'tbl' + tm1.name + tm2.name + tm3.name + CONVERT(VARCHAR(10), tm3.ID)) AS tableName
FROM dbo.tbltemp1 tm1 WITH ( NOLOCK ) ,
dbo.tbltemp2 tm2 WITH ( NOLOCK ) ,
dbo.tbltemp3 tm3 WITH ( NOLOCK )
WHERE tm3.ID= 1
AND tm3.ID = tm1.ID
AND tm1.ID = tm2.ID
)

Step 2: Remove Space of Table Name:

SET @ptablename = REPLACE(@ptablename, ' ', '')

Step 3: Check Table name availability and Create A Table:

IF NOT EXISTS ( SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_NAME = @ptablename )
BEGIN
SET @sql = 'CREATE TABLE ' + @ptablename+ '(ID INT IDENTITY PRIMARY KEY NOT NULL,UserIdentity varchar(50),tm3ID INT)'
EXEC (@sql)
END

 

Step 4: Alter Table

DECLARE @intFlag INT = 1
Declare @count int = ( SELECT COUNT(*)
FROM dbo.tbltemp1 tm1 WITH ( NOLOCK ) ,
dbo.tbltemp2 tm2 WITH ( NOLOCK ) ,
dbo.tbltemp3 tm3 WITH ( NOLOCK )
WHERE tm3.ID= 1
AND tm3.ID = tm1.ID
AND tm1.ID = tm2.ID
AND tm3.isSubmited=0
)
WHILE ( @intFlag <= @count )
BEGIN
SET @sql = 'ALTER TABLE"' + @ptablename + '"ADD "' Columnname + '" VARCHAR(' + size + ') NULL'
EXEC (@sql)
SET @intFlag = @intFlag + 1;
END