Share via

Script table components but not table itself.

chrisrdba 361 Reputation points
2021-02-03T22:15:42.77+00:00

Greetings. I need to drop/ recreate > 250 tables w LOB data to a new filegroup, as this is the only way to reclaim unused space for the files in that filegroup.

The common approach for dealing w this is to 1) export the data to a new table in the new FG. 2) drop the old table. 3) Rename the new table

However, in pondering all of this it occurred to me I'd also need to recreate foreign keys, triggers, etc. (number 4)

I'm looking for an automated way to do this. My hope for # 1 was to simply use SELECT...INTO. This will create the tables correctly and handle the data itself.

Numbers 2 and 3 would be easy enough.

But # 4 is where I'm stuck. I can use either the GUI or SMO to generate the scripts for these objects, but of course that script will include the CREATE TABLE itself, which is what I wouldn't want in this scenario.

Is there a way to do this withOUT the actual CREATE TABLE statement? I know I could open the script once it's generated and remove it, but that's not exactly optimal for this scenario.

Thanks!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,871 questions
0 comments
Accepted answer
  1. Erland Sommarskog 102.2K Reputation points
    2021-02-05T15:38:39.057+00:00

    I posted a procedure last night, but I deleted it, because that procedure only moves foreign keys. The procedure below also moves indexes and triggers.

    The idea is this: you move the table to the guest schema: 

    ALTER SCHEMA guest TRANSFER dbo.tbl

    Then you create the table new in the dbo schema and copy data over. Then you run:

    EXEC MoveFkeyIxTri 'tbl'

    And it will copy all constraints, triggers and indexes, and re-target all referencing constraints. Once you done this, you can drop the old table.

    Since the procedure is a couple of hundred lines, I wanted to attach it, but the Q&A platform was not really co-operative in that regard today. 

    /*--------------------------------------------------------------------------------------------------
   This procedures copies foreign key, triggers and non-constraint indexes from guest.@tblname to
   @tblname and re-targets referencing foreign keys from pointing to guest.@tblname to @tbl.
   guest.@tblname is assumed to be the old version of @tblname, and @tblname is the new one. Date
   should have been copied to @tblname prior to running this procedure.

   @tblname must be in the dbo schema.

   If some object (fkey, index of trigger) is not to be copied, for instance because the definition
   is no longer valid, pass it in @exceptions.

   The copying part of the procedure is not transactional, and any erros are trapped and displayed,
   but execution continues. (Since in case of accidents you can drop @tbl and start over). The part
   that moves the referencing foreign keys is transactional and must pass in whole. (To save you from
   having a mess to clean up.)
   ----------------------------------------------------------------------------------------------------*/
go
-- Preliminaries. Since we have a table type, we cannot use CREATE OR ALTER, but we need to
-- drop, so type can be recreated.
DROP PROCEDURE IF EXISTS MoveFkeyIxTri

DROP TYPE IF EXISTS ExceptionList

CREATE TYPE ExceptionList AS TABLE (exception sysname NOT NULL PRIMARY KEY)
go
CREATE PROCEDURE MoveFkeyIxTri @tblname     sysname,
                               @exceptions  ExceptionList READONLY AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
   DECLARE @old_tbl     nvarchar(150),
           @new_tbl     sysname,
           @old_tbl_id  int,
           @new_tbl_id  int,
           @index_id    int,
           @stats_id    int,
           @ref_table   nvarchar(1024),
           @ref_schema  nvarchar(129),
           @cnst_name   nvarchar(1024),
           @fkcols      nvarchar(MAX),
           @parentcols  nvarchar(MAX),
           @upd_action  nvarchar(60),
           @del_action  nvarchar(60),
           @repl_action nvarchar(60),
           @sql         nvarchar(MAX),
           @cur         CURSOR

   SELECT @old_tbl = 'guest.' + quotename(@tblname),
          @new_tbl = 'dbo.' + quotename(@tblname)

   SELECT @old_tbl_id = object_id(@old_tbl), @new_tbl_id = object_id(@new_tbl)

   IF @old_tbl_id IS NULL
      RAISERROR('The table %s does not exist.', 16, 1, @old_tbl)

   IF @new_tbl_id IS NULL
      RAISERROR('The table %s does not exist.', 16, 1, @new_tbl)

   -- Basic check that data bas been copied.
   IF (SELECT MAX(rows) FROM sys.partitions WHERE object_id = @old_tbl_id) > 0 AND
      (SELECT MAX(rows) FROM sys.partitions WHERE object_id = @new_tbl_id) = 0
      RAISERROR('The data in %s has not been copied to %s', 16, 1, @old_tbl, @new_tbl)

   SET @cur = CURSOR STATIC LOCAL FOR
      SELECT CASE WHEN f.referenced_object_id <> f.parent_object_id
                  THEN quotename(s.name) + '.' + quotename(r.name)
                  ELSE @new_tbl
             END,
             quotename(f.name),
             fc1.collist.value('.', 'nvarchar(MAX)'),
             fc2.collist.value('.', 'nvarchar(MAX)'),
             replace(f.update_referential_action_desc, '_', ' '),
             replace(f.delete_referential_action_desc, '_', ' '),
             CASE WHEN f.is_not_for_replication = 1
                  THEN 'NOT FOR REPLICATION'
                  ELSE ''
             END
      FROM   sys.foreign_keys f
      JOIN   sys.objects r ON f.referenced_object_id = r.object_id
      JOIN   sys.schemas s ON r.schema_id = s.schema_id
      CROSS  APPLY (SELECT quotename(c.name) + ','
                    FROM   sys.foreign_key_columns fc
                    JOIN   sys.columns c ON fc.parent_object_id = c.object_id
                                        AND fc.parent_column_id = c.column_id
                    WHERE  fc.constraint_object_id = f.object_id
                    ORDER  BY fc.constraint_column_id
                    FOR XML PATH(''), TYPE) AS fc1(collist)
      CROSS  APPLY (SELECT quotename(c.name) + ','
                    FROM   sys.foreign_key_columns fc
                    JOIN   sys.columns c ON fc.referenced_object_id = c.object_id
                                        AND fc.referenced_column_id = c.column_id
                    WHERE  fc.constraint_object_id = f.object_id
                    ORDER  BY fc.constraint_column_id
                    FOR XML PATH(''), TYPE) AS fc2(collist)
      WHERE  f.parent_object_id = @old_tbl_id
        AND  f.name NOT IN (SELECT e.exception FROM @exceptions e)

   OPEN @cur

   WHILE 1 = 1
   BEGIN
      FETCH @cur INTO @ref_table, @cnst_name, @fkcols,
                      @parentcols, @upd_action, @del_action, @repl_action
      IF @@FETCH_STATUS <> 0
         BREAK

      SELECT @sql = 'ALTER TABLE ' + @new_tbl + ' ADD CONSTRAINT ' + @cnst_name + '
                     FOREIGN KEY (' + substring(@fkcols, 1, len(@fkcols) - 1) + ')
                     REFERENCES ' + @ref_table + '(' +
                         substring(@parentcols, 1, len(@parentcols) - 1) + ')
                     ON UPDATE ' + @upd_action + '
                     ON DELETE ' + @del_action + ' ' + @repl_action
      PRINT @sql
      EXEC(@sql)
   END

   -- Copy non-key indexes.
   SET @cur = CURSOR STATIC FOR
       SELECT index_id
       FROM   sys.indexes
       WHERE  is_primary_key = 0
         AND  is_unique_constraint = 0
         AND  is_hypothetical = 0
         AND  object_id = @old_tbl_id
         AND  name NOT IN (SELECT exception FROM @exceptions)

   OPEN @cur

   WHILE 1 = 1
   BEGIN
      FETCH @cur INTO @index_id
      IF @@FETCH_STATUS <> 0
         BREAK

      SELECT @sql  = 'CREATE ' +
                      CASE i.is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END +
                      CASE i.index_id WHEN 1 THEN 'CLUSTERED ' ELSE '' END +
                      CASE WHEN i.type = 3 THEN 'XML '
                           WHEN i.type = 4 THEN 'SPATIAL '
                           WHEN i.type IN (5, 6) THEN 'COLUMNSTORE '
                           ELSE ''
                       END + 'INDEX ' + quotename(i.name) +
                      ' ON ' + @new_tbl +
                      '(' + substring(ic.collist.value('.', 'nvarchar(MAX)'), 1,
                                len(ic.collist.value('.', 'nvarchar(MAX)')) - 1) + ')'
      FROM   sys.indexes i
      CROSS  APPLY (SELECT quotename(c.name) +
                           CASE ic.is_descending_key
                                WHEN 1 THEN ' DESC'
                                ELSE ''
                           END + ','
                    FROM   sys.index_columns ic
                    JOIN   sys.columns c ON ic.object_id = c.object_id
                                        AND ic.column_id = c.column_id
                    WHERE  ic.object_id = @old_tbl_id
                      AND  ic.index_id  = @index_id
                      AND  (i.type > 2 OR
                            ic.key_ordinal > 0)
                    ORDER  BY ic.key_ordinal
                    FOR XML PATH(''), TYPE) AS ic(collist)
      WHERE   object_id = @old_tbl_id
        AND   index_id  = @index_id

      -- Add any included columns. (We need to do this query by query, since a big
      -- query fails with a QP error, or least it did once upon a time.)
      IF EXISTS (SELECT *
                 FROM   sys.index_columns
                 WHERE  object_id = @old_tbl_id
                   AND  index_id  = @index_id
                   AND  is_included_column = 1)
      BEGIN
         SELECT @sql +=  ' INCLUDE(' +
                         substring(ic.incllist.value('.', 'nvarchar(MAX)'), 1,
                                   len(ic.incllist.value('.', 'nvarchar(MAX)')) - 1) + ')'
         FROM  (SELECT quotename(c.name) + ','
                FROM   sys.index_columns ic
                JOIN   sys.columns c ON ic.object_id = c.object_id
                                    AND ic.column_id = c.column_id
                WHERE  ic.object_id = @old_tbl_id
                  AND  ic.index_id  = @index_id
                  AND  ic.is_included_column = 1
                ORDER  BY ic.index_column_id
                FOR XML PATH(''), TYPE) AS ic(incllist)
      END

      -- Tack on index filter and IGNORE_DUP_KEY setting. Thet latter is always there,
      -- so that we know that with have WITH section for the rest.
      SELECT @sql +=  CASE WHEN i.filter_definition IS NOT NULL
                             THEN ' WHERE ' + i.filter_definition + ' '
                             ELSE ''
                      END +
                      ' WITH (IGNORE_DUP_KEY = ' + IIF(i.ignore_dup_key = 0, 'OFF', 'ON') + ',
                              FILLFACTOR = ' + IIF(i.fill_factor = 0, '100',
                                                   CONVERT(varchar(10), i.fill_factor)) + ',
                              PAD_INDEX = ' + IIF(i.is_padded = 0, 'OFF', 'ON') + ',
                              STATISTICS_NORECOMPUTE = ' + IIF(s.no_recompute = 0, 'OFF', 'ON') + ',
                              ALLOW_ROW_LOCKS = ' + IIF(i.allow_row_locks = 0, 'OFF', 'ON') + ',
                              ALLOW_PAGE_LOCKS = ' + IIF(i.allow_page_locks = 0, 'OFF', 'ON') + ',
                              DATA_COMPRESSION = ' + p.data_compression_desc + ')'
      FROM    sys.indexes i
      JOIN    sys.stats s ON i.object_id = s.object_id
                         AND i.index_id  = s.stats_id
      JOIN    sys.partitions p ON p.object_id = i.object_id
                              AND p.index_id  = i.index_id
                              AND p.partition_number = 1
      WHERE   i.object_id = @old_tbl_id
        AND   i.index_id  = @index_id

      PRINT @sql
      EXEC(@sql)
   END


   --- Copy triggers.
   SET @cur = CURSOR STATIC FOR
      SELECT sm.definition
      FROM   sys.triggers t
      JOIN   sys.sql_modules sm ON t.object_id = sm.object_id
      WHERE  t.parent_id = @old_tbl_id
        AND  t.name NOT IN (SELECT e.exception FROM @exceptions e)

   OPEN @cur

   WHILE 1 = 1
   BEGIN
      FETCH @cur INTO @sql
      IF @@FETCH_STATUS <> 0
         BREAK

      PRINT @sql
      EXEC(@sql)
   END


   -- Time to move referencing foreign keys.
   SET @cur = CURSOR STATIC FOR
      SELECT quotename(s.name),
             quotename(s.name) + '.' + quotename(r.name),
             quotename(f.name),
             fc1.collist.value('.', 'nvarchar(MAX)'),
             fc2.collist.value('.', 'nvarchar(MAX)'),
             replace(f.update_referential_action_desc, '_', ' '),
             replace(f.delete_referential_action_desc, '_', ' '),
             CASE WHEN f.is_not_for_replication = 1
                  THEN 'NOT FOR REPLICATION'
                  ELSE ''
             END
      FROM   sys.foreign_keys f
      JOIN   sys.objects r ON f.parent_object_id = r.object_id
      JOIN   sys.schemas s ON r.schema_id = s.schema_id
      CROSS  APPLY (SELECT quotename(c.name) + ','
                    FROM   sys.foreign_key_columns fc
                    JOIN   sys.columns c ON fc.parent_object_id = c.object_id
                                        AND fc.parent_column_id = c.column_id
                    WHERE  fc.constraint_object_id = f.object_id
                    ORDER  BY fc.constraint_column_id
                    FOR XML PATH(''), TYPE) AS fc1(collist)
      CROSS  APPLY (SELECT quotename(c.name) + ','
                    FROM   sys.foreign_key_columns fc
                    JOIN   sys.columns c ON fc.referenced_object_id = c.object_id
                                        AND fc.referenced_column_id = c.column_id
                    WHERE  fc.constraint_object_id = f.object_id
                    ORDER  BY fc.constraint_column_id
                    FOR XML PATH(''), TYPE) AS fc2(collist)
      WHERE  f.referenced_object_id = @old_tbl_id
        AND  f.referenced_object_id <> f.parent_object_id
        AND  f.name NOT IN (SELECT e.exception FROM @exceptions e)

   OPEN @cur

   BEGIN TRANSACTION

   WHILE 1 = 1
   BEGIN
      FETCH @cur INTO @ref_schema, @ref_table, @cnst_name, @fkcols,
                      @parentcols, @upd_action, @del_action, @repl_action
      IF @@fetch_status <> 0
         BREAK

      -- Drop old consraint, so that the new can be added.
      SELECT @sql = 'ALTER TABLE ' + @ref_table + ' DROP CONSTRAINT ' +  @cnst_name
      PRINT @sql
      EXEC (@sql)


      -- Then add the new one.
      SELECT @sql = 'ALTER TABLE ' + @ref_table + ' ADD CONSTRAINT ' + @cnst_name + '
                     FOREIGN KEY (' + substring(@fkcols, 1, len(@fkcols) - 1) + ')
                     REFERENCES ' + @new_tbl + '(' +
                     substring(@parentcols, 1, len(@parentcols) - 1) + ')
                     ON UPDATE ' + @upd_action + '
                     ON DELETE ' + @del_action + ' ' + @repl_action
      PRINT @sql
      EXEC(@sql)
   END

   COMMIT TRANSACTION
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ; THROW
END CATCH

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 41,011 Reputation points
    2021-02-04T07:32:25.653+00:00

    Hello,

    Are there clustered index on the tables?
    Data & clustered index are always stored in the same filegroup and if you move the CI to a different filegroup, the data will be moved, too. So move the CI's.

    See Move an Existing Index to a Different Filegroup => Limitations and Restrictions => "If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup."

    Keep in mind, during that process the table is locked and it can take some time till it finish. Test it first with a small table.

  2. Cris Zhan-MSFT 6,606 Reputation points
    2021-02-05T03:02:14.647+00:00

    Hi @chrisrdba ,

    >Is there a way to do this withOUT the actual CREATE TABLE statement?

    The "Generate Script" task in SSMS provides many options in the "Advanced" tab to determine the content of the script to be generated, such as foreign keys, triggers, indexes, etc. But there is no option to exclude the creation script of the table.
    It indeed includes an option: "Script DROP and CREATE", but this option determines that the t-sql statements in generated script to create objects, drop objects, or both. This option is for all objects in the script.

    And I couldn't find a quick way to do this after doing a lot of searching.

    You may need to consider manually modifying the script that generated in the SSMS database task to delete the create statement. Or search scripts that can generate triggers, foreign key constraints, etc. separately for the tables in the database. For the situation with a large number of tables, these operations are not easy.

    0 comments