[mssql] CREATE tabel script genereren

[hoi1234]

Mijn vraag
Voor een schoolopdracht moet ik een stored procedure schrijven die aangeroepen kan worden met één parameter (namelijk de tabelnaam). Deze stored procedure dient een create tabel statement te retourneren.

SQL:

--aanroepen van de stored procedure
EXEC SP_GENERATE_CREATE_TABLE_SCRIPT 'EMP'

Ik moet middels de stored procedure een script genereren op basis van de Emp tabel om HIST_Emp aan te maken. Dit is echter een voorbeeld. In principe moet elke tabel die in het standaard schema (dbo) staat, gegenereerd kunnen worden. Onderstaand een voorbeeld van de output:

SQL:

 
CREATE TABLE [HIST_EMP](
    [EMPNO]     [NUMERIC](4, 0)     NOT NULL,
    [TIMESTAMP] [TIMESTAMP]         NOT NULL, 
    [ENAME]     [VARCHAR](8)        NOT NULL,
    [JOB]       [VARCHAR](9)        NOT NULL,
    [BORN]      [DATE]              NOT NULL,
    [HIRED]     [DATE]              NOT NULL,
    [SGRADE]    [NUMERIC](2, 0)     NOT NULL,
    [MSAL]      [NUMERIC](7, 2)     NOT NULL,
    [USERNAME]  [VARCHAR](15)       NOT NULL,
    [DEPTNO]    [NUMERIC](2, 0)     NOT NULL

 CONSTRAINT [HIST_EMP_PK ] PRIMARY KEY(EMPNO, TIMESTAMP)

Het enige verschil tussen emp en hist_emp is de timestamp kolom die er bij komt en de primary key. De Emp tabel heeft alleen Empno als primary key.

Relevante software en hardware die ik gebruik
MSSQL server 2017

Wat ik al gevonden of geprobeerd heb
Ik heb al rondgegoogled maar kom op google alleen erg ingewikkelde scripts tegen, die ik niet begrijp. Als ik mijn script werkend krijg, ben ik volgens mij al een heel eind en daarom wil ik eigenlijk geen scripts van internet halen.

SQL:

 
DECLARE @TABEL_NAAM VARCHAR(MAX) = 'EMP'
DECLARE @Q VARCHAR(MAX) = '';


SELECT @Q   += ISC.COLUMN_NAME + ' ' + ISC.DATA_TYPE  + CASE DATA_TYPE WHEN 'NUMERIC' THEN  '(' + CONVERT(VARCHAR(MAX), NUMERIC_PRECISION)  + ',' + CONVERT(VARCHAR(MAX),NUMERIC_SCALE) + ') ' WHEN 'VARCHAR' THEN  '(' + CONVERT(VARCHAR(MAX), CHARACTER_MAXIMUM_LENGTH) + ')' ELSE '' END + ' ' + CASE IS_NULLABLE WHEN 'NO' THEN 'NOT NULL, ' ELSE 'NULL, ' END 
FROM INFORMATION_SCHEMA.COLUMNS ISC 
WHERE ISC.TABLE_NAME = @TABEL_NAAM


SELECT 'CREATE TABLE HIST_' + 'EMP'  + ' ( ' +@q + ' )' + 'CONSTRAINT HIST_' + 'EMP' + '_PK' + COLUMN_NAME   FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ISCCU INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ISTC
                                                                                                            ON ISCCU.CONSTRAINT_NAME = ISTC.CONSTRAINT_NAME 
                                                                                                            WHERE ISCCU.TABLE_NAME = 'EMP' AND CONSTRAINT_TYPE = 'PRIMARY KEY'

het probleem is dat deze achter de laatste "NOT NULL' ook een komma zet. Dat wil ik niet, want dat is foutieve syntax. Kan ik dat er gewoon afknippen met LEFT() of zou dit op een elegantere manier kunnen?

SQL:

CREATE TABLE HIST_EMP (
    empno       numeric(4,0)    NOT NULL,
    ename       varchar(8)      NOT NULL,
    job         varchar(9)      NOT NULL,
    born        date            NOT NULL,
    hired       date            NOT NULL,
    sgrade      numeric(2,0)    NOT NULL,
    msal        numeric(7,2)    NOT NULL,
    username    varchar(15)     NOT NULL,
    deptno      numeric(2,0)    NOT NULL,

    CONSTRAINT HIST_EMP_PK PRIMARY key (empno)
     )

[Daos]

Misschien kan je iets doen met STRING_AGG?

[HansvDr]

Dit kan gewoon vanuit SQL Server management studio.

Rechtermuisknop op tabel, script table as. Of rechtermuisknop op de database als je meerdere tabellen tegelijk wilt doen of data mee wilt scripten, Tasks, Generate Scripts.

[hoi1234]

HansvDr schreef op woensdag 22 januari 2020 @ 12:09:
Dit kan gewoon vanuit SQL Server management studio.

Rechtermuisknop op tabel, script table as. Of rechtermuisknop op de database als je meerdere tabellen tegelijk wilt doen of data mee wilt scripten, Tasks, Generate Scripts.

Dat weet ik. Maar het punt is dat we met code generatie dit zelf moeten doen. Dat is nou net de opdracht.

[HansvDr]

Dan zou ik hier eens kijken:

https://stackoverflow.com...xisting-tables-with-query


Ook even naar je script gekeken.

In de laatste Select:

SELECT 'CREATE TABLE HIST_' + 'EMP' + ' ( ' +@q + ' )' ....

@q vervangen voor: LEFT(@q, LEN(@q)-1)

[ Voor 38% gewijzigd door HansvDr op 22-01-2020 13:45 ]

[Jurgle]

Volgens mij kan je je een hoop ellende besparen door

SQL:

SELECT TOP 0 * INTO HIST_EMP FROM EMP;
ALTER TABLE HIST_EMP ADD [TIMESTAMP] timestamp;
--todo: add key constraint, maar makkelijk

Maar, omdat je een string moet returnen met een create table statement, kun je het volgende doen. Ik snap dat gewoon een antwoord hier neerplempen niet gebruikelijk is, maar omdat dit geen kant-en-klaar antwoord is en ik nog genoeg ruimte laat voor verbetering (datatypen (parameters), andere indices, etc..) dacht ik dat dit ook als oplossingsrichting gezien kan worden met uitleg

SQL:

-- ik neem aan dat je de @tabel_naam hebt, merk op dat dat van SQLServer geen varchar(max) mag zijn, maar dat terzijde.

--begin van @result query met de create table waarbij je timestamp vast toevoegt
declare @result varchar(max) = 'CREATE TABLE ' + QUOTENAME('hist_' + @tabel_naam) + ' (
  [TIMESTAMP] timestamp NOT NULL';

-- dan gaan we loopen over de kolommen in de COLUMNS
declare @position int = 0;
--dus zo lang dat kan
while(@position IS NOT NULL) begin;
    --selecteer je de eerstvolgende kolom
    SELECT @position = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @tabel_naam AND TABLE_SCHEMA = 'dbo' AND ORDINAL_POSITION > @position;

    --en als er nog een volgende kolom is, voeg je die toe aan @result merk op
    --dat de CASE WHEN nu nog behoorlijk naief is
    if(@position IS NOT NULL) begin;
        SELECT @result = @result + '
, ' + QUOTENAME(COLUMN_NAME) + ' ' + DATA_TYPE +
            (CASE
                WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(varchar(max), CHARACTER_MAXIMUM_LENGTH) + ')'
                WHEN NUMERIC_PRECISION IS NOT NULL THEN '(' + CONVERT(varchar(max), NUMERIC_PRECISION) +', ' + CONVERT(varchar(max), NUMERIC_SCALE) + ')'
                ELSE ''
            END) + (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' END)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = @tabel_naam AND TABLE_SCHEMA = 'dbo' AND ORDINAL_POSITION = @position
    end;
end

--daarna gaan we op zoek naar de kolommen van de bestaande PRIMARY KEY en zetten we het begin
--van de nieuwe kolommen vast klaar
declare @key_colnames varchar(max) = '    [TIMESTAMP] ASC';

SELECT @key_colnames = @key_colnames + '
  , ' + QUOTENAME(COLUMN_NAME) + ' ASC'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = @tabel_naam AND TABLE_SCHEMA = 'dbo'

--dan de nieuwe key declaratie nog even toevoegen aan @result
set @result = @result + '
, PRIMARY KEY CLUSTERED (
' + @key_colnames + '
  ) 
)';
--en voor dit voorbeeld print ik het resultaat naar de console
print @result;

Edit: Oja, foutafhandeling in je SP: vergeet bijvoorbeeld niet dat je eigenlijk ook nog even moet checken of er al een kolom [TIMESTAMP] bestaat in je brontabel en of hist_{tabelnaam} bestaat, maar dat is natuurlijk pas bij uitvoeren van @result

[ Voor 4% gewijzigd door Jurgle op 23-01-2020 17:05 . Reden: foutafhandeling vergeten ]

[hoi1234]

Jurgle schreef op donderdag 23 januari 2020 @ 17:02:
Volgens mij kan je je een hoop ellende besparen door

SQL:

SELECT TOP 0 * INTO HIST_EMP FROM EMP;
ALTER TABLE HIST_EMP ADD [TIMESTAMP] timestamp;
--todo: add key constraint, maar makkelijk

Maar, omdat je een string moet returnen met een create table statement, kun je het volgende doen. Ik snap dat gewoon een antwoord hier neerplempen niet gebruikelijk is, maar omdat dit geen kant-en-klaar antwoord is en ik nog genoeg ruimte laat voor verbetering (datatypen (parameters), andere indices, etc..) dacht ik dat dit ook als oplossingsrichting gezien kan worden met uitleg

SQL:

-- ik neem aan dat je de @tabel_naam hebt, merk op dat dat van SQLServer geen varchar(max) mag zijn, maar dat terzijde.

--begin van @result query met de create table waarbij je timestamp vast toevoegt
declare @result varchar(max) = 'CREATE TABLE ' + QUOTENAME('hist_' + @tabel_naam) + ' (
  [TIMESTAMP] timestamp NOT NULL';

-- dan gaan we loopen over de kolommen in de COLUMNS
declare @position int = 0;
--dus zo lang dat kan
while(@position IS NOT NULL) begin;
    --selecteer je de eerstvolgende kolom
    SELECT @position = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @tabel_naam AND TABLE_SCHEMA = 'dbo' AND ORDINAL_POSITION > @position;

    --en als er nog een volgende kolom is, voeg je die toe aan @result merk op
    --dat de CASE WHEN nu nog behoorlijk naief is
    if(@position IS NOT NULL) begin;
        SELECT @result = @result + '
, ' + QUOTENAME(COLUMN_NAME) + ' ' + DATA_TYPE +
            (CASE
                WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(varchar(max), CHARACTER_MAXIMUM_LENGTH) + ')'
                WHEN NUMERIC_PRECISION IS NOT NULL THEN '(' + CONVERT(varchar(max), NUMERIC_PRECISION) +', ' + CONVERT(varchar(max), NUMERIC_SCALE) + ')'
                ELSE ''
            END) + (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' END)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = @tabel_naam AND TABLE_SCHEMA = 'dbo' AND ORDINAL_POSITION = @position
    end;
end

--daarna gaan we op zoek naar de kolommen van de bestaande PRIMARY KEY en zetten we het begin
--van de nieuwe kolommen vast klaar
declare @key_colnames varchar(max) = '    [TIMESTAMP] ASC';

SELECT @key_colnames = @key_colnames + '
  , ' + QUOTENAME(COLUMN_NAME) + ' ASC'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = @tabel_naam AND TABLE_SCHEMA = 'dbo'

--dan de nieuwe key declaratie nog even toevoegen aan @result
set @result = @result + '
, PRIMARY KEY CLUSTERED (
' + @key_colnames + '
  ) 
)';
--en voor dit voorbeeld print ik het resultaat naar de console
print @result;

Edit: Oja, foutafhandeling in je SP: vergeet bijvoorbeeld niet dat je eigenlijk ook nog even moet checken of er al een kolom [TIMESTAMP] bestaat in je brontabel en of hist_{tabelnaam} bestaat, maar dat is natuurlijk pas bij uitvoeren van @result

Bedankt. Ik had me al gerealiseerd dat mijn realisatie veel te ingewikkeld is. Ik denk dat checks niet nodig zijn. Ik weet namelijk zeker dat er geen timestamp-kolom is. Ik heb het nu gewoon werkend gekregen door mbv left() en Len() de laatste komma te strippen. Ik laat het voor nu dus even zo. Als ik tijd over heb, ga ik kijken in hoeverre het lukt om jouw uitwerking verder uit te werken. Ik wil uiteraard graag een hoog cijfer, maar liever dat ik alles af heb en een 6 krijg dan dat ik niet alles af heb en een knock-out krijg, maar wat ik wel heb een 8 of 9 zou scoren.

Overigens:
Ik vraag me af of "SELECT TOP 0* FROM <BRONTABEL> niet gewoon al voldoende is. Maar dat ga ik dan zelf even uitzoeken. Volgens mij wil ik de tabelstrucuur kopiëren + pk definiëren.

Laten we het zo zeggen. Soms kom je er later achter dat je implementatie veel te ingewikkeld is. Ik heb me gewoon niet goed gerealiseerd dat ik waarschijnlijk gewoon de structuur wil kopiëren.

[ Voor 5% gewijzigd door hoi1234 op 23-01-2020 21:17 ]

[Gomez12]

Ik zou zeggen lees de gestelde vraag eens goed na, want in wezen kan je dit van supersimpel (select top 0 * into ... from ...) naar redelijk complex (voeg eens wat foreign keys toe en wat constraints) naar complex (stel gebruikersrechten in) naar erg complex (verdeel hem identiek over een HA-cluster heen).

En allemaal is het slechts het copieren van een tabel-definitie...