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
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
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')
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
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
Sir please show me how to call this SP script_index_sp to copy all indexes for a specific table?
Thanks
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.
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.
9 people are following this question.