CODEWITHSUNDEEP
sql-server
Nada
Nada

Reputation: 33

Dynamically create tables from table parameters SQL Server

I want someway to automate table creations as every day customer can add some columns ,remove some ,so my idea is to pass table name and columns into a table then use this table in stored procedure to automatically creates the table.

This is table that will hold tables structure

create table nada_test 
(
    table_name varchar(500),
    col_name varchar(100),
    col_type varchar(100)
)

Sample data:

insert into nada_test 
values ('data', 'salary', 'int'), ('data', 'id', 'int'),
       ('data', 'job', 'varchar(100)')

Could someone show me how to achieve this?

Upvotes: 0

Views: 933

Answers (3)

Slava Murygin
Slava Murygin

Reputation: 1955

As it already been mentioned, your approach is very vulnerable to SQL injections. See example:

insert into #nada_test
values ('TestTable] (TestColumn int);SELECT * FROM sys.tables--', 'TestColumn', 'INT')
GO
DECLARE @TableName sysname, @ColumnName sysname, @Type VARCHAR(100), @SQL VARCHAR(2000)

WHILE EXISTS (SELECT TOP 1 1 FROM #nada_test)
BEGIN
    SELECT TOP 1 @TableName = table_name, @ColumnName = [col_name], @Type = col_type FROM #nada_test
    DELETE FROM #nada_test WHERE @TableName = table_name and @ColumnName = [col_name]

    IF NOT EXISTS ( SELECT TOP 1 1 FROM sys.tables WHERE name = @TableName)
        SET @SQL = 'CREATE TABLE [' + @TableName + '] ([' + @ColumnName + '] ' + @Type + ');'
    ELSE IF NOT EXISTS ( SELECT TOP 1 1 FROM sys.columns WHERE name = @ColumnName AND object_id = OBJECT_ID(@TableName))
        SET @SQL = 'ALTER TABLE [' + @TableName + '] ADD [' + @ColumnName + '] ' + @Type + ';'
    ELSE
        SET @SQL = 'PRINT ''TABLE name [' + @TableName + '] with column [' + @ColumnName + '] is already exists'';'
    PRINT @SQL
    EXEC (@SQL)
END

Upvotes: 0

Ilyes
Ilyes

Reputation: 14926

How about that

CREATE TABLE T
(
  TableName varchar(500),
  ColName varchar(100),
  ColType varchar(100)
);

INSERT INTO T VALUES
('data','salary','int'),
('data',   'id',   'int'),
('data',   'job',  'varchar(100)');

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = N'CREATE TABLE Data ('+ STUFF((
            SELECT ',' + ColName + ' ' + ColType
            FROM T
            FOR XML PATH('')
            ), 1, 1, '') + N' );'
FROM T;

SELECT @SQL [CreateTable];

--EXECUTE sp_executesql @SQL;

But that won't help you

  • What will happen to the data already exists in your table?

  • What if the table already exists, ok you can pass that by IF OBJECT_ID() .., but still, what will happen to the data already in your table?

  • You will face another problem even if you store the data in temp table because the structure of both tables is not the same even the datatypes of the columns.

Upvotes: 1

Himanshu
Himanshu

Reputation: 3970

Generally we can use like 

create table x as select * from y ;

using some existing table structure say y in this case You can create a ddl trigger on your existing requirement i.e. if theres any change to this table then fire the same query above.

Upvotes: 0

Related Questions