May 13, 2014 at 3:38 pm
I have a SQL script that gives me all the existing nonclustered indices for a specific table. The script provides the following columns:
Schema Name, Object Name, IndexName, DropIndexStatement, CreateIndexStatement
Here is the SQL code:
SELECT SchemaName = SCHEMA_NAME (o.SCHEMA_ID)
,ObjectName = o.name
,IndexName = i.name
,DropIndexStatement = 'DROP INDEX [' + i.Name + '] ON [' + SCHEMA_NAME (o.SCHEMA_ID)
+ '].[' + o.name + ']'
,CreateIndexStatement = 'CREATE ' + (CASE i.is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END)
+ 'NONCLUSTERED INDEX ['
+ i.name + '] ON [' + SCHEMA_NAME (o.SCHEMA_ID) + '].[' + o.name + '] ( '
+ LEFT(list, ISNULL(splitter-1,LEN(list))) + ' ) '
+ ISNULL('INCLUDE ( ' + SUBSTRING(list, indCol.splitter +1, 100) + ' ) ','')
+ ISNULL('WHERE ' + i.filter_definition,'')
FROM sys.indexes i
JOIN sys.objects o ON i.OBJECT_ID = o.OBJECT_ID
CROSS APPLY
(SELECT splitter = NULLIF(CHARINDEX('|',indexCols.list),0)
, list
FROM (SELECT list=CAST((
SELECT CASE WHEN sc.is_included_column = 1 AND sc.ColPos = 1 THEN '|' ELSE '' END +
CASE WHEN sc.ColPos > 1 THEN ', ' ELSE '' END + name +
CASE WHEN sc.is_descending_key=1 THEN ' DESC' ELSE '' END
FROM (SELECT sc.is_descending_key
, sc.is_included_column
, index_column_id
, name = '[' + name + ']'
, ColPos = ROW_NUMBER() OVER (PARTITION BY sc.is_included_column
ORDER BY sc.index_column_id)
FROM sys.index_columns sc
JOIN sys.columns c ON sc.OBJECT_ID = c.OBJECT_ID
AND sc.column_id = c.column_id
WHERE sc.index_id = i.index_id
AND sc.OBJECT_ID = i.OBJECT_ID ) sc
ORDER BY sc.is_included_column, ColPos
FOR XML PATH (''), TYPE) AS VARCHAR(MAX))
)indexCols
) indCol
WHERE i.is_primary_key=0
AND i.is_unique_constraint=0
AND i.type_desc=N'NONCLUSTERED'
AND o.TYPE=N'U'
ORDER BY SchemaName, ObjectName, IndexName
Now, this ALMOST works perfectly, however out of the 21 indices that come back for my specific table, 3 of them appear to truncate the result for column [CreateIndexStatement].
Here is an example of what one of the [CreateIndexStatement] should look like:
CREATE NONCLUSTERED INDEX [_dta_index_APSB_FACT_12_1627152842__K21_15_61_62_63_64_65_66_67] ON [dbo].[APSB_FACT] ( [ACT_EXPLORE_TEST_DATE] ) INCLUDE ( [STUDENT_KEY], [ACT_EST_COMP_RANGE_HIGH], [ACT_EST_COMP_RANGE_LOW], [ACT_ENGLISH_TEST_SCORE], [ACT_MATH_TEST_SCORE], [ACT_READING_TEST_SCORE], [ACT_SCIENCE_TEST_SCORE],[ACT_COMPOSITE_TEST_SCORE] )
When I run the Script above, it returns this:
CREATE NONCLUSTERED INDEX [_dta_index_APSB_FACT_12_1627152842__K21_15_61_62_63_64_65_66_67] ON [dbo].[APSB_FACT] ( [ACT_EXPLORE_TEST_DATE] ) INCLUDE ( [STUDENT_KEY], [ACT_EST_COMP_RANGE_HIGH], [ACT_EST_COMP_RANGE_LOW], [ACT_ENGLISH_TEST_SCORE], [ACT_M )
Anyone have any idea why the script is causing this record to return truncated?
Please let me know.
Thanks
May 13, 2014 at 3:50 pm
Are you looking at these in an SMS grid window? If so check your settings as SMS default is to only display 255 characters so change these settings and you might see what you are after.
May 13, 2014 at 4:01 pm
I have other indices that are longer in length that return without truncation. I am using this script in an ETL package, btw.
May 14, 2014 at 12:14 am
hi,
change the below statement in your script
SUBSTRING(list, indCol.splitter +1, 100)
SUBSTRING(list, indCol.splitter +1, 250) --> increase this, your query will work with out errors
To be on safe you can increaase to 1000 also
May 14, 2014 at 6:50 am
That was definitely the problem. Thanks so much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply