Dynamically create table with full structure

  • Hello All,
    I have a large table "LARGETable" and till now my developers created a job that run every 2 minutes and delete records if the date is older than 2 months.
    Of course it  cause many locks.

    Thus, I renamed the table name to "LARGETable_OLD" and also created new table with the same name "LARGETable" (with the same structure indexes, triggers etc) in one transaction.

    I would like to create a job that will run every 2 months and will rename the table name "LARGETable" to "LARGETable_OLD" and will dynamically create the "LARGETable" with full structure (indexes, triggers etc).

    Can you please advise?

    Thanks in advance

  • 89netanel - Thursday, September 28, 2017 5:27 AM

    Hello All,
    I have a large table "LARGETable" and till now my developers created a job that run every 2 minutes and delete records if the date is older than 2 months.
    Of course it  cause many locks.

    Thus, I renamed the table name to "LARGETable_OLD" and also created new table with the same name "LARGETable" (with the same structure indexes, triggers etc) in one transaction.

    I would like to create a job that will run every 2 months and will rename the table name "LARGETable" to "LARGETable_OLD" and will dynamically create the "LARGETable" with full structure (indexes, triggers etc).

    Can you please advise?

    Thanks in advance

    So, every two months, you want to start with an empty 'LARGETable'?

    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

  • Yes.

    but i want to keep the data on table that will be call "LARGETable_OLD" (truncate is not an option).

  • Have you considered table partitioning?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I don'r have many experience with partitioning.
    and also the instance Edition is Standard 🙁

  • 89netanel - Thursday, September 28, 2017 5:42 AM

    Yes.

    but i want to keep the data on table that will be call "LARGETable_OLD" (truncate is not an option).

    What do you want to happen to old versions of LARGETable_OLD? Will they be dropped and replaced every two months?

    Also, if this is an archive table, why would you want it to retain triggers?

    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

  • exactly

  • Sounds like you should investigate the use of SYNONYMs.
    Create two identical tables LARGETable1 and LARGETable2 and use a synonym to switch between them every two months (after truncating the 'current' _OLD version of the synonym).

    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

  • 89netanel - Thursday, September 28, 2017 6:00 AM

    I don'r have many experience with partitioning.
    and also the instance Edition is Standard 🙁

    Partitioning is in all editions from SQL 2016 SP1 onwards, so that's not a problem, and this kind of thing is what partitioning is for, so worth doing some reading.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Seems pretty simple.   Instead of dropping the regular table, just make a copy of it and then truncate, that way, all your triggers, indexes, constraints, etc are not lost.  You may not be able to truncate if there are FKeys, but this is the basic idea.  Schedule a job to run this T-SQL:

    DROP TABLE [LARGETable_OLD];

    SELECT * INTO [LARGETable_OLD] FROM [LARGETable];

    TRUNCATE TABLE [LARGETable];

  • Hi all,
    What is the best way to see all the objects (indexes,PK,constraint,triggers) that related to my table "LARGETable" by a sql query?

  • For that, you can just right click on your table in SSMS Object Explorer and choose Script Table As -> Create To...

    You can also right-click and choose "View Dependencies"..

    You can also query the metadata, for instance, this will list views, triggers, functions and procedures that reference the table:

    select
       [o].[xtype]
     , [o].[name]
     , [c].[text]
      , [o].[crdate] AS [createdDate]
    from [sys].[syscomments] as [c]
    join [sys].[sysobjects] as [o] on [c].[id] = [o].[id]
    where text like '%LARGETable%'
    order by 4 desc, 1, 2;

  • Hi All,
    I found a solution!!!

    i were looked for a script that will Dynamically create table with full structure.
    I have 2 tables "workout" (with the most recent data) and "workout_old" (with data from the last 2 months) 
    and i wanted that every 2 months i will have a job that will automatically drop the old table "workout_old" than rename the "workout" table to "workout_old" and than create a table "workout" without any data but with all the constraints, indexes,triggers, etc
    (For the first time I manually created a table that called "workout" and also an archive table that called "workout_old" that will keep data from last 2 moths.)

    i created a job (that run in one transaction) that do the following:
    1. run a script that generate the table structure of "workout" with all constraints, indexes,triggers, etc.
    2. after the script generated i drop the old table "workout_old"
    3. than rename the table "workout" to "workout_old"
    4. and than create from section 1 the table "workout" with all constraints, indexes,triggers, etc

    i used this script and changed it a little bit.
    https://stackoverflow.com/questions/706664/generate-sql-create-scripts-for-existing-tables-with-query

    /*

    1. begin tran and set parameters
    2. rename [WorkOut_old] to [WorkOut_old_delete]
    3. execute the @sql that generate table script on [WorkOut]
    4. export snapshot of the check constraints on table [WorkOut]
    5. export snapshot of the triggers on table [WorkOut]
    6. drop triggers from [WorkOut]
    7. rename [WorkOut] to [WorkOut_old]
    8. drop [WorkOut_old_delete]
    9. sp_executesql @sql in order to create WorkOut
    10. After the original table dropped and recreated - execute the Check Constraints snapshot #CheckC and recreate the Check Constraints
    11. After the original table dropped and recreated - execute the trigger snapshot #tr and recreate the triggers
    12. commit
    */

    --1
    BEGIN TRAN

    DECLARE @Schema_Name     NVARCHAR(1000)
            ,@Table_Name     NVARCHAR(1000)
            ,@SchemaAndTable NVARCHAR(2000)
            ,@Object_Name     NVARCHAR(2000)
            ,@Object_ID         INT
       ,@SQL             NVARCHAR(MAX)
            ,@DropTrigger     NVARCHAR (max)
            ,@Table_Name_old NVARCHAR (1000)
            ,@Table_Name_old_delete NVARCHAR (1000)

    SET @Schema_name    = 'dbo' 
    SET @Table_Name        = 'WorkOut'
    SET @SchemaAndTable = @Schema_name+'.'+@Table_Name
    SET @Table_Name_old    = @Table_Name+'_old'
    SET @Table_Name_old_delete = @Table_Name_old+'_delete'

    --2
    /**************************
    Rename _old to _delete
    ***************************/

    EXEC SP_RENAME @Table_Name_old,@Table_Name_old_delete;

    --3
    /**************************
    Select @sql - Generate script table with PK,FK,indexes,triggers 
    ***************************/

    SELECT 
      @Object_Name = '[' + s.name + '].[' + o.name + ']'
      , @Object_ID = o.[object_id]
    FROM sys.objects o WITH (NOLOCK)
    JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id]
    WHERE s.name + '.' + o.name = @SchemaAndTable
      AND o.[type] = 'U'
      AND o.is_ms_shipped = 0
        
    ;WITH index_column AS 
    (
      SELECT 
        ic.[object_id]
       , ic.index_id
       , ic.is_descending_key
       , ic.is_included_column
       , c.name
      FROM sys.index_columns ic WITH (NOLOCK)
      JOIN sys.columns c WITH (NOLOCK) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
      WHERE ic.[object_id] = @Object_ID
    ),
    fk_columns AS 
    (
      SELECT 
        k.constraint_object_id
       , cname = c.name
       , rcname = rc.name
      FROM sys.foreign_key_columns k WITH (NOLOCK)
      JOIN sys.columns rc WITH (NOLOCK) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id 
      JOIN sys.columns c WITH (NOLOCK) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
      WHERE k.parent_object_id = @Object_ID
    )

    SELECT @sql =
    'CREATE TABLE ' + @Object_Name + CHAR(13) + '(' + CHAR(13) + STUFF((
      SELECT CHAR(9) + ', [' + c.name + '] ' + 
       CASE WHEN c.is_computed = 1
        THEN 'AS ' + cc.[definition] 
        ELSE UPPER(tp.name) + 
          CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
            THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
           WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
            THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
           WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') 
            THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
           WHEN tp.name = 'decimal' 
            THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
           ELSE ''
          END +
          CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
          CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
          CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END + 
          CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END 
       END + CHAR(13)
      FROM sys.columns c WITH (NOLOCK)
      JOIN sys.types tp WITH (NOLOCK) ON c.user_type_id = tp.user_type_id
      LEFT JOIN sys.computed_columns cc WITH (NOLOCK) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
      LEFT JOIN sys.default_constraints dc WITH (NOLOCK) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
      LEFT JOIN sys.identity_columns ic WITH (NOLOCK) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
      WHERE c.[object_id] = @Object_ID
      ORDER BY c.column_id
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
      + ISNULL((SELECT CHAR(9) + ', PRIMARY KEY (' + 
           (SELECT STUFF((
             SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
             FROM sys.index_columns ic WITH (NOLOCK)
             JOIN sys.columns c WITH (NOLOCK) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
             WHERE ic.is_included_column = 0
              AND ic.[object_id] = k.parent_object_id 
              AND ic.index_id = k.unique_index_id  
             FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
        + ')' + CHAR(13)
        FROM sys.key_constraints k WITH (NOLOCK)
        WHERE k.parent_object_id = @Object_ID 
          AND k.[type] = 'PK'), '') + ')' + CHAR(13)
      + ISNULL((SELECT (
       SELECT CHAR(13) +
         'ALTER TABLE ' + @Object_Name + /*' WITH' 
        + CASE WHEN fk.is_not_trusted = 1 
          THEN ' NOCHECK' 
          ELSE ' CHECK' 
         END*/ + 
         ' ADD'/*CONSTRAINT [' + fk.name + ']*/+' FOREIGN KEY(' 
         + STUFF((
          SELECT ', [' + k.cname + ']'
          FROM fk_columns k
          WHERE k.constraint_object_id = fk.[object_id]
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
         + ')' +
         ' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('
         + STUFF((
          SELECT ', [' + k.rcname + ']'
          FROM fk_columns k
          WHERE k.constraint_object_id = fk.[object_id]
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
         + ')'
        + CASE 
          WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE' 
          WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
          WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT' 
          ELSE '' 
         END
        + CASE 
          WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
          WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
          WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT' 
          ELSE '' 
         END 
        + CHAR(13) /*+ 'ALTER TABLE ' + @Object_Name + ' CHECK CONSTRAINT [' + fk.name + ']' + CHAR(13)*/
       FROM sys.foreign_keys fk WITH (NOLOCK)
       JOIN sys.objects ro WITH (NOLOCK) ON ro.[object_id] = fk.referenced_object_id
       WHERE fk.parent_object_id = @Object_ID
       FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
      + ISNULL(((SELECT
       CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END 
          + ' NONCLUSTERED INDEX [' + i.name + '] ON ' + @Object_Name + ' (' +
          STUFF((
          SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
          FROM index_column c
          WHERE c.is_included_column = 0
           AND c.index_id = i.index_id
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')' 
          + ISNULL(CHAR(13) + 'INCLUDE (' + 
           STUFF((
           SELECT ', [' + c.name + ']'
           FROM index_column c
           WHERE c.is_included_column = 1
            AND c.index_id = i.index_id
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '') + CHAR(13)
       FROM sys.indexes i WITH (NOLOCK)
       WHERE i.[object_id] = @Object_ID
        AND i.is_primary_key = 0
        AND i.[type] = 2
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
      ), '')
    --PRINT @sql

    --4
    /******
    export snapshot of the check constraints on table 
    *******/

    CREATE TABLE #CheckC (id INT IDENTITY(1,1),definition NVARCHAR (MAX))

    INSERT INTO #CheckC
    SELECT 'ALTER TABLE ' + @SchemaAndTable + ' ADD CHECK ' + ck.definition 
    FROM sys.check_constraints ck
    INNER JOIN sys.tables t ON ck.parent_object_id = t.object_id 
    WHERE t.object_id=@Object_ID

    --5
    /******
    export snapshot of the triggers on table 
    *******/

    CREATE TABLE #tr (id INT IDENTITY(1,1),definition NVARCHAR (MAX))

    INSERT INTO #tr
    SELECT SM.definition 
    FROM sys.triggers TR
    INNER JOIN sys.sql_modules SM
    ON TR.object_id = SM.object_id
    WHERE OBJECT_NAME(tr.parent_id)= @Table_Name

    --6
    /**************************
    Drop Triggers
    ***************************/

    SELECT @DropTrigger =
    ISNULL(((
         SELECT CHAR(13)+'DROP TRIGGER '+ obj.name+CHAR(13) from sys.sql_modules m
    INNER JOIN sys.objects obj on obj.object_id=m.object_id 
    INNER JOIN sys.tables tbl on tbl.object_id = obj.parent_object_id
    WHERE obj.type ='TR'
    AND tbl.name = @Table_Name
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
      ), '')+ CHAR(13)
    --    print @DropTrigger

    EXEC sys.sp_executesql @DropTrigger

    --7
    /**************************
    Rename the table to _old
    ***************************/

    EXEC sp_rename @Table_Name, @Table_Name_old;

    --8
    /**************************
    Drop the table _old_delete 
    ***************************/

    DECLARE @DropTable_old_delete NVARCHAR (MAX)
    SET @DropTable_old_delete = N'drop table '+QUOTENAME(@Schema_name)+'.'+QUOTENAME(@Table_Name_old_delete)+';'

    EXEC sys.sp_executesql @DropTable_old_delete 

    --9
    /**************************
    sp_execute @sql 
    ***************************/

    EXEC sys.sp_executesql @sql

    --10
    /********
    After the original table dropped and recreated
    execute the Check Constraints snapshot and recreate the Check Constraints
    ********/

    DECLARE @CKCCounter INT,
    @CKCMax INT,
    @CKCCommand NVARCHAR (MAX)

    SELECT @CKCMax = MAX(id) FROM #tr
    SET @CKCCounter = 1

    WHILE @CKCCounter <= @CKCMax
    BEGIN

    SELECT @CKCCommand = definition FROM #CheckC WHERE id = @CKCCounter

    EXEC sp_executesql @CKCCommand

    SET @CKCCounter = @CKCCounter+1

    END

    DROP TABLE #CheckC

    --11
    /********
    After the original table dropped and recreated
    execute the triggers snapshot and recreate the triggers
    ********/

    DECLARE @TRCounter INT,
    @TRMax INT,
    @TRCommand NVARCHAR (MAX)

    SELECT @TRMax = MAX(id) FROM #tr
    SET @TRCounter = 1

    WHILE @TRCounter <= @TRMax
    BEGIN

    SELECT @TRCommand = definition FROM #tr WHERE id = @TRCounter
    EXEC sp_executesql @TRCommand

    SET @TRCounter = @TRCounter+1

    END

    DROP TABLE #tr

    --12
    COMMIT

Viewing 13 posts - 1 through 12 (of 12 total)

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