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: 246695 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 2 years, 11 months ago by Phil Parkin. Reason: Removed unnecessary columns from select

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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: 1003864 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: