Share via

How to copy table with indexes and data

T.Zacks 3,996 Reputation points
2021-04-16T15:34:21.86+00:00

When i copy table with the help of Select * into then table structure with data copied but indexes not copied. so share some script which will copy indexes with data too.

Thanks
Tridip

Developer technologies | Transact-SQL
0 comments
Accepted answer
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2021-04-16T22:12:06.2+00:00

    Here is a query that returns information about indexes. I use this query to get information about index from client sites, so it produces tabular format. The scripting part is left as an exercise to the reader. You should rip out the part that references sys.dm_db_index_usage_stats, as that does not affect the definition of the index. 

    SELECT dbname = db_name(), s.name, o.name, o.type, i.name, i.index_id,
       autype = p.type_desc, ixtype = i.type_desc, PK = i.is_primary_key,
       U = i.is_unique_constraint, UIX = i.is_unique,
       Filter = i.filter_definition,
       indexcols = left(ic.indexcols, len(ic.indexcols) - 1) +
                   CASE WHEN incl.includedcols IS NOT NULL
                        THEN '  INCL ' +
                             left(incl.includedcols, len(incl.includedcols) - 1)
                        ELSE ''
                    END,
        p.rows,
        CASE WHEN i.index_id IN (0,1) AND p.type_desc = 'IN_ROW_DATA'
             THEN SUM(p.total_pages) OVER (PARTITION BY o.object_id)
        END * 8192 / 1000000 AS table_size,
        p.total_pages * 8192 / 1000000 AS reserved,
        p.used_pages * 8192 / 1000000 AS reserved_in_use, p.no_of_parts,
        ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates
FROM   sys.indexes i
LEFT JOIN   (SELECT p.object_id, p.index_id, au.type_desc,
               SUM(p.rows) AS rows, SUM(au.total_pages) AS total_pages,
               SUM(au.used_pages) AS used_pages, COUNT(*) AS no_of_parts
        FROM   sys.partitions p
        JOIN   sys.allocation_units au ON p.partition_id = au.container_id
        GROUP  BY p.object_id, p.index_id, au.type_desc) AS p
    ON p.object_id = i.object_id
   AND p.index_id  = i.index_id
JOIN   sys.objects o ON i.object_id = o.object_id
JOIN   sys.schemas s ON o.schema_id = s.schema_id
LEFT   JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = db_id()
                                      AND ius.object_id   = i.object_id
                                      AND ius.index_id    = i.index_id
OUTER  APPLY (SELECT c.name + ', ' AS [text()]
              FROM   sys.index_columns ic
              JOIN   sys.columns c ON ic.column_id = c.column_id
                                  AND ic.object_id = c.object_id
              WHERE  ic.object_id = i.object_id
                AND  ic.index_id = i.index_id
                AND  ic.is_included_column = 0
              ORDER  BY ic.key_ordinal
              FOR XML PATH('')) AS ic(indexcols)
OUTER  APPLY (SELECT c.name + ', ' AS [text()]
              FROM   sys.index_columns ic
              JOIN   sys.columns c ON ic.column_id = c.column_id
                                  AND ic.object_id = c.object_id
              WHERE  ic.object_id = i.object_id
                AND  ic.index_id = i.index_id
                AND  ic.is_included_column = 1
              ORDER  BY c.name
              FOR XML PATH('')) AS incl(includedcols)
WHERE  o.type NOT IN ('IT', 'S', 'TF')

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2021-04-17T09:56:56.58+00:00

    Sorry, I forgot that I actually had a stored procedure that performs scripting as well. Below is a stripped-down version of that procedure - I ripped out parts that uses tables that part of the toolset I took the procedure from.

    Thus there are some limitations:

    1. Does not handle things you have in the WITH clause, except for IGNORE_DUP_KEY. (But all these options are rarely used.)
    2. Does not handle placement on a different file group - which may not be relevant if you are copying the table.
    3. It does not work properly with XML, spatial and columnstore indexes. (In the latter case, I think it is just a matter to avoid generating the IGNORE_DUP_KEY clause.) CREATE OR ALTER PROCEDURE script_index_sp @tblname sysname, @ixname sysname, @ixcmd nvarchar(MAX) OUTPUT AS DECLARE @object_id int = object_id(@tblname), @index_id int SELECT @index_id = index_id FROM sys.indexes WHERE object_id = @object_id AND name = @ixname -- Get the mandatory part of the index definition. That is type and columns. SELECT @ixcmd = '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(@ixname) + ' ON ' + quotename(@tblname) + '(' + 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 = @object_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 = @object_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, see Connect 777049. IF EXISTS (SELECT * FROM sys.index_columns WHERE object_id = @object_id AND index_id = @index_id AND is_included_column = 1) BEGIN SELECT @ixcmd = @ixcmd + ' 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 = @object_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 @ixcmd = @ixcmd + CASE WHEN filter_definition IS NOT NULL THEN ' WHERE ' + filter_definition + ' ' ELSE '' END + ' WITH (IGNORE_DUP_KEY=' + (CASE ignore_dup_key WHEN 0 THEN 'OFF' WHEN 1 THEN 'ON' END) + ')' FROM sys.indexes WHERE object_id = @object_id AND index_id = @index_id
    1 person found this answer helpful.
  2. AndreiFomitchev 91 Reputation points
    2021-04-19T02:10:40.77+00:00

    You can scripts:
    Right Click DB -> Tasks -> Generate Scripts...
    Choose Objects -> Select Specific Database Objects -> Next
    Open in New Query Window ->Advanced -> Script Indexes -> True -> OK -> Next
    -> Next

    It will create a script of your selection. You even can script with it data and permissions as well. From the result you can execute whaever you need.

    I usually create an empty table from such script, then load data, then create indexes. - It works quicker in this way.

    1 person found this answer helpful.
    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.