Generate DDL-Script based on Meta-Data

Question

Generate DDL-Script based on Meta-Data

info 58414

Hall of Fame

Points: 3386
More actions
May 26, 2021 at 12:41 pm

#3888219

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](50) NOT NULL,
[col] [nvarchar](50) NOT NULL,
[data_type] [nvarchar](50) NOT NULL,
[length] [nvarchar](50) NOT NULL,
[PRECISION] [nvarchar](50) NOT NULL,
[scale] [nvarchar](50) NOT NULL,
[Col_Is_Nullable] [bit] NOT NULL,
[Is_Business_Key] [bit] NOT NULL
) ON [PRIMARY]
GO

Attachments:
You must be logged in to view attached files.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

I haven't fully tested this, but it should get you started:

DROP TABLE IF EXISTS #SomeData;

CREATE TABLE #SomeData
(
    entity VARCHAR(50)
   ,col VARCHAR(50)
   ,data_type VARCHAR(50)
   ,length SMALLINT
   ,Precision TINYINT
   ,scale TINYINT
   ,Col_Is_Nullable BIT
   ,Is_Business_Key BIT
);

INSERT #SomeData
(
    entity
   ,col
   ,data_type
   ,length
   ,Precision
   ,scale
   ,Col_Is_Nullable
   ,Is_Business_Key
)
VALUES
('table_1', 'column_1', 'datetime(2)', NULL, NULL, NULL, 1, 0)
,('table_1', 'column_2', 'nvarchar()', 8, NULL, NULL, 0, 1)
,('table_1', 'column_3', 'decimal', NULL, 12, 2, 1, 0)
,('table_1', 'column_4', 'nvarchar()', 255, NULL, NULL, 1, 0)
,('table_2', 'column_1', 'nvarchar()', 6, NULL, NULL, 0, 1)
,('table_2', 'column_2', 'decimal', NULL, 2, 0, 1, 0)
,('table_2', 'column_3', 'nvarchar()', 8, NULL, NULL, 1, 0)
,('table_2', 'column_4', 'nvarchar()', 6, NULL, NULL, 0, 1)
,('table_2', 'column_5', 'decimal', NULL, 4, 0, 1, 0);

WITH ordered
AS (SELECT sd.entity
          ,sd.col
          ,ColumnDef = CONCAT(calcs.Exp_dt, ' ', calcs.NullClause)
          ,rn = ROW_NUMBER() OVER (PARTITION BY sd.entity ORDER BY sd.col)
    FROM #SomeData sd
        CROSS APPLY
    (
        SELECT Exp_dt = REPLACE(
                                   REPLACE(sd.data_type, 'nvarchar()', CONCAT('nvarchar(', sd.length, ')'))
                                  ,'decimal'
                                  ,CONCAT('decimal(', sd.Precision, ',', sd.scale, ')')
                               )
              ,NullClause = IIF(sd.Col_Is_Nullable = 1, 'NULL', 'NOT NULL')
    ) calcs )
SELECT ordered.entity
      ,TableDef = CONCAT(
                            'CREATE TABLE dbo.'
                           ,ordered.entity
                           ,' ('
                           ,STRING_AGG(CONCAT(ordered.col, ' ', ordered.ColumnDef), ', ')
                           ,') ON [PRIMARY]'
                        )
FROM ordered
GROUP BY ordered.entity
ORDER BY ordered.entity;

This reply was modified 3 years, 6 months ago by Phil Parkin. Reason: Removed unnecessary columns from select

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

info 58414 Hall of Fame Points: 3386 More actions · Answer 2

Hi Phil, it's always something special if you present a solution. Thank you, for your awsome script! 🙂

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

It'll be interesting when someone realizes the mistake they made my omitting the parentheses from the "decimal" notations and either includes them in the future or decides to remove them from the other datatypes. Like I asked on the other forum, you should post what you finally ended up using so that we can have a little try at making the code a bit more "bullet proof". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Generate DDL-Script based on Meta-Data

Attachments: