February 11, 2010 at 10:39 am
How can I script include columns from the index.
We can script Table Name of the Index, Index columns of the Index but how do we script include columns of the Index?
February 11, 2010 at 11:05 am
How are you trying to script the Index, I did try and could script the included columns..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 11, 2010 at 11:15 am
sihaab (2/11/2010)
How can I script include columns from the index.We can script Table Name of the Index, Index columns of the Index but how do we script include columns of the Index?
Here's what I use for scripting out all indexes. You'll need to make appropriate variables. The key is the sys.index_columns table and the column is_included_column. There is even a line commented out that will handle filtered indexes in 2008.
declare cIXs cursor for
select
'IF NOT EXISTS (select 1 from sys.indexes where object_id = OBJECT_ID(''' + QuoteName(schema_name(st.schema_id)) + '.' +
QuoteName(Object_Name(si.object_id)) + ''') and name = ''' + si.Name + ''') begin' + @CRLF +
' PRINT ''Creating index ' + QUOTENAME(si.name) + ' ON ' + QuoteName(schema_name(st.schema_id)) + '.' + QuoteName(Object_Name(si.object_id)) + '''' + @CRLF +
' CREATE ' + si.type_desc + ' INDEX ' + QuoteName(si.Name) +
' ON ' + QuoteName(SCHEMA_NAME(st.schema_id)) + '.' + QuoteName(Object_Name(si.object_id)) + ' (INDEXCOLUMNLIST) ' +
CASE when @DestServerVersion = 2000 then '--' else '' end + @CRLF +
' WITH (PAD_INDEX = ' + case when si.is_padded = 0 then 'OFF' else 'ON' end +
', FILLFACTOR = ' + CASE WHEN si.fill_factor = 0 then '100' else CONVERT(varchar(5), si.fill_factor) end +
', STATISTICS_NORECOMPUTE = OFF' + -- is this meta-data?
', SORT_IN_TEMPDB = OFF' +
', IGNORE_DUP_KEY = ' + case when si.ignore_dup_key = 0 then 'OFF' else 'ON' end +
', DROP_EXISTING = OFF' +
--', ONLINE = ' +
', ALLOW_ROW_LOCKS = ' + case when si.allow_row_locks = 0 then 'OFF' else 'ON' end +
', ALLOW_PAGE_LOCKS = ' + case when si.allow_page_locks = 0 then 'OFF' else 'ON' end +
--CASE when si.filter_definition is null then '' else ' WHERE ' + si.filter_definition end +
')' + @CRLF +
'end' COLLATE Latin1_General_CI_AS,
si.object_id,
si.index_id
from sys.indexes si
INNER JOIN sys.tables st ON si.object_id = st.object_id
where si.type > 0
and is_primary_key = 0
and is_unique_constraint = 0
order by st.name, si.name
open cIXs
fetch next from cIXs into @cmd, @tableid, @indexid
while @@FETCH_STATUS = 0 begin
select @csv = (
select ',' + QuoteName(sc.name) + ' ' + case when is_descending_key = 0 then 'ASC' else 'DESC' end
from sys.indexes si
INNER JOIN sys.index_columns sic ON si.object_id = sic.object_id and si.index_id = sic.index_id
INNER JOIN sys.columns sc ON sc.object_id = sic.object_id and sc.column_id = sic.column_id
where si.object_id = @tableid
and si.index_id = @indexid
and sic.is_included_column = 0
order by sic.index_column_id
for XML PATH(''))
set @csv = substring(@csv, 2, len(@csv))
select @child = (
select ',' + QuoteName(sc.name) --+ ' ' + case when is_descending_key = 0 then 'ASC' else 'DESC' end
from sys.indexes si
INNER JOIN sys.index_columns sic ON si.object_id = sic.object_id and si.index_id = sic.index_id
INNER JOIN sys.columns sc ON sc.object_id = sic.object_id and sc.column_id = sic.column_id
where si.object_id = @tableid
and si.index_id = @indexid
and sic.is_included_column = 1
order by sic.index_column_id
for XML PATH(''))
set @child = IsNull(')' + @CRLF + ' INCLUDE (' + substring(@child,2,len(@child)), '')
set @cmd = replace(@cmd, 'INDEXCOLUMNLIST', @csv + @child)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 11, 2010 at 1:34 pm
Thank you all.
February 11, 2010 at 2:29 pm
Sounds like you are looking for more of a script that will generate all your tables. But, you can also script out the table with the idexes via SSMS if you change some of the settings under Tools/Options/Sql Server Object Explorer/Scripting specifially under the section Table and View Options.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply