question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked AndreiFomitchev answered

How to copy table with indexes and data

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered TZacks-2728 commented

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')

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks you so much for the script. it gives indexes related info but i like to know how could i copy indexes for the table which still not clear to me. thanks

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ErlandSommarskog commented

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
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sir please show me how to call this SP script_index_sp to copy all indexes for a specific table?

Thanks

0 Votes 0 ·

You have the indexes in sys.indexes. You get the object_id with the object_id() function. Then loop over all indexes with is_hypothetical = 0.

Yes, some parts are left as an exercise to the reader.

0 Votes 0 ·
AndreiFomitchev avatar image
1 Vote"
AndreiFomitchev answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.