Create DDL-Statement based on Meta-Data

Transact-SQL
1

Hi,

we have metadata in a table with this information (Attachment).

The task is to generate the CREATE TABLE statements for all Tables - based on these metadata (SQL2016) Simple Create Table like the Example below, with Datatype, null and pk. -> Meta-Data in Attachment.

Thanks and best Regards

Nicole

-- Example for finished Script

CREATE TABLE table_1
(
column_1 datetime(2) NULL,
column_2 nvarchar(8) PRIMARY KEY,
column_3 decimal NULL.
column_4 nvarchar(255) NULL
);

-- MetaData Schema (csv in Attachment)

CREATE TABLE [dbo].[EntityTerms](
[entity] nvarchar NOT NULL,
[col] nvarchar NOT NULL,
[data_type] nvarchar NOT NULL,
[length] nvarchar NOT NULL,
[PRECISION] nvarchar NOT NULL,
[scale] nvarchar NOT NULL,
[Col_Is_Nullable] [bit] NOT NULL,
[Is_Business_Key] [bit] NOT NULL
) ON [PRIMARY]
GO

-- Meta-Data
entity;col;data_type;length;PRECISION;scale;Col_Is_Nullable;Is_Business_Key
table_1;column_1;datetime(2);NULL;NULL;NULL;true;false
table_1;column_2;nvarchar();8;NULL;NULL;false;true
table_1;column_3;decimal;NULL;12;2;true;false
table_1;column_4;nvarchar();255;NULL;NULL;true;false
table_2;column_1;nvarchar();6;NULL;NULL;false;true
table_2;column_2;decimal;NULL;2;0;true;false
table_2;column_3;nvarchar();8;NULL;NULL;true;false
table_2;column_4;nvarchar();6;NULL;NULL;false;true
table_2;column_5;decimal;NULL;4;0;true;false

2

I've created a sample for you, you can easily adjust it to complete it. First I selected all the unique enities

WITH CTE AS
(SELECT DISTINCT
entity
FROM [dbo].[EntityTerms]
)

then I join the entities with your table and use the function STRING_AGG() to make a single column for all your columns. With CONCAT() you can combine strings. You should only complete the case statement to make it work.

WITH CTE AS
(SELECT DISTINCT
entity
FROM [dbo].[EntityTerms]
)
SELECT CTE.entity, CONCAT('CREATE TABLE ', CTE.entity , ' ', STRING_AGG(
CASE WHEN [EntityTerms].[data_type] ='datetime(2)' THEN 'datetime(2)'
WHEN [EntityTerms].[data_type] ='nvarchar()' THEN 'nvarchar(' + [length] + ')'
END, ' '))
FROM CTE
INNER JOIN [dbo].[EntityTerms]
ON [dbo].[EntityTerms].entity=CTE.entity
GROUP BY CTE.entity

3

one possible way

select t.name, c.name, ty.name data_type, c.max_length/2 length, 
       ty.precision, ty.scale, c.is_nullable Col_Is_Nullable, 
	   case 
	      when ic.column_id is null then 0
		  else 1
		  end as Is_Business_Key
  from sys.tables t
  join sys.columns c 
    on t.object_id = c.object_id
  join sys.types ty 
    on c.user_type_id = ty.user_type_id
  left join sys.index_columns AS ic 
    on ic.column_id = c.column_id 
   and ic.object_id = c.object_id
 where t.name = 'table_1'
 order by c.name
4

In your real-life problem, is the data_type for DECIMAL always missing the parentheses? Any other little "gotchas" we should know about? And can you please convert the Meta-Data to an INSERT/SELECT so that it's readily consumable data?

5

Thanks to all. :love_you_gesture: I have fixed the Problem

6

Excellent! With the idea of this being a two way street, can you post your final code for your "fix". Thanks!

2 Likes
7

You mean "Phil" fixed the problem for you. :wink: Generate DDL-Script based on Meta-Data – SQLServerCentral Forums

It's a shame that you didn't reply to my question because I was going to try to make your solution a bit more bullet-proof for when someone finally gets religious and adds the missing () to the "decimal" notations in your example code or decides that all parentheses are superfluous and stops including them.
:smiley:

8

Hi Jeff,
here is the complete Solution based on Phil's Example. This Solution generates DDL for MS-SQL with Meta-Data from Spark with PK-Constraints... It is complete ..
Regards Nicole

;WITH c
AS (SELECT *,
( CASE
WHEN dType= 'Timestamp' THEN 'datetime2'
WHEN dType= 'String' THEN 'nvarchar()'
WHEN dType= 'Integer' THEN 'int'
WHEN dType= 'Byte' THEN 'bit'
WHEN dType= 'Long' THEN 'bigint'
WHEN dType= 'Decimal' THEN 'Decimal'
ELSE 'nvarchar(255)'
END ) AS dt
FROM (SELECT project,
entity,
column,
data_type,
[length],
[precision],
scale,
cNullable,
isBK,
rn = Row_number() OVER (partition BY sd.entity ORDER BY sd.column)
FROM [dbo].[vGetEntityAndcolumns] sd)
x),
d
AS (SELECT * ,ColumnDef = CONCAT(calcs.Exp_dt, ' ', calcs.NullClause)
FROM c
CROSS APPLY
(
SELECT Exp_dt = REPLACE(
REPLACE(c.dt, 'nvarchar()', CONCAT('nvarchar(', c.[length], ')'))
,'decimal'
,CONCAT('decimal(', c.[PRECISION], ',', c.scale, ')')
)
,NullClause = IIF(c.cNullable = 'True', 'NULL', 'NOT NULL')
) calcs )
--SELECT * FROM d;

SELECT d.entity
,TableDef = CONCAT( 'USE TestDataSpark '
,'DROP TABLE IF EXISTS dbo.'
,d.entity
,'; CREATE TABLE dbo.'
,d.entity
,' ('
,STRING_AGG(CONCAT(d.column, ' ', d.ColumnDef), ', ')
,', CONSTRAINT [PK_'
,d.entity
,' ] '
,' PRIMARY KEY CLUSTERED ('
,LEFT(STRING_AGG(IIF(d.isBK = 'True', d.column + ',', ''), ''),
LEN(STRING_AGG(
IIF(
d.isBK = 'True'
,d.column
+ ','
,'')
,''
)
)
- 1)
,')) ON [PRIMARY]'
)
FROM d
WHERE d.project = 'Booking'
GROUP BY d.entity
ORDER BY d.entity;