October 7, 2016 at 5:59 pm
Hello,
I have a table called "Tables" which stores a list of tables by id, name, etc.
I also have a table called "Columns" which stores a list of columns by id, name, and tableID.
I am trying to create a stored procedure that uses dynamic sql to create a database and spawn the tables and associated columns based on the information in the "Tables" and "Columns" tables.
This is a little complex for me, can someone provide guidance? As you will see in my code (below) this will work for dynamic columns, but now I am realizing that I need to somehow embed for dynamic tables - and I am getting SUPER confused:
SET @SQLDBQuery =
(
'
USE [master]
CREATE DATABASE [User_Type_' + @AdminUserLastName + '_' + CAST(@AdminUserID AS NVARCHAR(25)) + '] ON PRIMARY
( NAME = N''' + 'User_Type_' + @AdminUserLastName + '_' + CAST(@AdminUserID AS NVARCHAR(25)) + '_Data'', FILENAME = N''' + @DrivePath + '\User_Type_' + @AdminUserLastName + '_' + CAST(@AdminUserID AS NVARCHAR(25)) + '_Data.mdf'' , SIZE = 167872KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
LOG ON
( NAME = N''' + 'User_Type_' + @AdminUserLastName + '_' + CAST(@AdminUserID AS NVARCHAR(25)) + '_Log'', FILENAME = N''' + @DrivePath + '\User_Type_' + @AdminUserLastName + '_' + CAST(@AdminUserID AS NVARCHAR(25)) + '_Log.ldf'' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
'
)
SET @SQLTableQuery =
(
'
USE [User_Type_' + @AdminUserLastName + '_' + CAST(@AdminUserID AS NVARCHAR(25)) + ']
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N' + '''' + @TableName + '''' + ') AND type in (N''U''))
BEGIN
CREATE TABLE ' + @TableName + '(
--***Dynamic Columns***
CONSTRAINT [PK_' + @TableName + '] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
'
)
October 7, 2016 at 11:29 pm
matt.warren 33350 (10/7/2016)
Hello,I have a table called "Tables" which stores a list of tables by id, name, etc.
I also have a table called "Columns" which stores a list of columns by id, name, and tableID.
I am trying to create a stored procedure that uses dynamic sql to create a database and spawn the tables and associated columns based on the information in the "Tables" and "Columns" tables.
This is a little complex for me, can someone provide guidance? As you will see in my code (below) this will work for dynamic columns, but now I am realizing that I need to somehow embed for dynamic tables - and I am getting SUPER confused:
Quick thought, this code looks very flimsy, too complex for what it's doing and it is wide open to SQL Injections, suggest you research that subject.
😎
On your initial question, can you elaborate further what you are trying to do, not how you are trying to do it? There are many options for dynamic DDL and object manipulations in SQL Server but one has to have more information on the actual business and technical requirements in order to provide a reasonable answer.
October 8, 2016 at 6:40 am
your Columns table would need the column name, data type+ size and probably whether it's null/not null;
defaults and foreign keys, even when coming from our definitions table would be relevant.
you can create the string containing the columns and their definitions with a FOR XML statement, but i'n not grasping why you are not storing the whole table definition, instead of the columns, it's not obvious from what you posted so far.
a basic example from existing metadata:
SELECT DISTINCT
t.name,
sq.Columns
FROM sys.tables t
JOIN (
SELECT OBJECT_ID,
Columns = STUFF((SELECT ','
+ sc.name --the column name
+' ' + type_name(sc.system_type_id) + ' ' --the data type, not even fiddling with maxsize/dive by two for nchars/precision/scale
+ case when sc.is_nullable = 0 THEN ' NOT NULL ' ELSE ' NULL ' END
FROM sys.columns sc
WHERE sc.object_id = s.object_id
FOR XML PATH('')),1,1,'')
FROM sys.columns s
) sq ON t.object_id = sq.object_id
Lowell
October 10, 2016 at 10:00 am
Hello Eirikur,
Thank you so very much for taking the time to help me.
I am creating a small web portal that allows the user to register. Then upon registration, will be afforded the predefined modules and options. The problem is that I want the solution to be extensible and be able to add new items (thus new columns and new tables). If I can do this by adding the new column name (data type, size, nullability, etc) into a table that maintains a relationship to tables in the system, then that would accomplish what I am trying to do. The same goes for one level above - i.e., Tables. If I have a table of Tables, and a table of Columns, then each time someone new registers, they will get a client database with the latest columns a tables - instead of having to hardcode the table and column definitions.
Is this making sense?
Thank you very much, again, for your time and attention.
Sincerely,
Matt
October 10, 2016 at 5:19 pm
Hello,
Here is the answer:
drop table tables
drop table columns
create table tables (table_id int, tableName varchar(128))
create table columns (table_id int, columnname varchar(128), columntypedef varchar(128))
insert into tables
select 1, 'table1'
union
select 2, 'table2'
union
select 3, 'table3'
insert into columns
select 1, 'column_id', 'int'
union
select 1, 'field1', 'varchar(12)'
union
select 2, 'column_id', 'int'
union
select 2, 'field2', 'varchar(12)'
union
select 3, 'column_id', 'int'
union
select 3, 'field3', 'varchar(12)'
declare @SQLDBQuery nvarchar(max) = ''
declare @SQLTableQuery nvarchar(max) = ''
declare @AdminUserLastName varchar(128) = 'Smith'
declare @AdminUserID varchar(128) = 'e10390472'
declare @DrivePath varchar(1028) = 'E:\temp'
SET @SQLDBQuery = @SQLDBQuery +
'
USE [master]
CREATE DATABASE [User_Type_' + @AdminUserLastName + '_' + CAST(@AdminUserID AS NVARCHAR(25)) + '] ON PRIMARY
( NAME = N''' + 'User_Type_' + @AdminUserLastName + '_' + CAST(@AdminUserID AS NVARCHAR(25)) + '_Data'', FILENAME = N''' + @DrivePath + '\User_Type_' + @AdminUserLastName + '_' + CAST(@AdminUserID AS NVARCHAR(25)) + '_Data.mdf'' , SIZE = 167872KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
LOG ON
( NAME = N''' + 'User_Type_' + @AdminUserLastName + '_' + CAST(@AdminUserID AS NVARCHAR(25)) + '_Log'', FILENAME = N''' + @DrivePath + '\User_Type_' + @AdminUserLastName + '_' + CAST(@AdminUserID AS NVARCHAR(25)) + '_Log.ldf'' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
'
print @SQLDBQuery
exec sp_executesql @SQLDBQuery
-- spin through the tables and columns table
select
@SQLTableQuery = @SQLTableQuery +
'
USE [User_Type_' + @AdminUserLastName + '_' + CAST(@AdminUserID AS NVARCHAR(25)) + ']
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N' + '''' + t.TableName + '''' + ') AND type in (N''U''))
BEGIN
CREATE TABLE ' + t.TableName + ' (' +
cl.columnlist + '
CONSTRAINT [PK_' + t.TableName + '] PRIMARY KEY CLUSTERED
(
[Column_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
'
from tables t inner join
(SELECT
t.table_id,
STUFF((SELECT ', ' + c.columnname + ' ' + c.columntypedef
FROM columns c
WHERE t.table_id = c.table_id
FOR XML PATH('')), 1, 1, '') columnlist
FROM tables t
GROUP BY t.table_id) cl
on t.table_id = cl.table_id
print @SQLTableQuery
exec sp_executesql @SQLTableQuery
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply