CODEWITHSUNDEEP
sqlsql-servert-sql
Keith Adler
Keith Adler

Reputation: 21178

Create dynamic column in SQL as VARCHAR MAX

I am using a standard SQL query to determine where indexes are needed however one of the columns which gives you the TSQL to build the index is getting cutoff because there are so many fields in the index. The columns themselves also appear cutoff. How do I modify it so CreateIndexStatement is a VARCHAR MAX which doesn't get cutoff:

SELECT  sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
,  'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL 
                THEN ''  
    ELSE CASE WHEN mid.equality_columns IS NULL 
                    THEN ''  
        ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL 
                THEN ''  
    ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns 
    FROM sys.dm_db_missing_index_group_stats AS migs 
            INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
            INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID() 
            INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID 
    WHERE     (migs.group_handle IN 
        ( 
        SELECT     TOP (500) group_handle 
            FROM          sys.dm_db_missing_index_group_stats WITH (nolock) 
            ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))  
        AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1 
    ORDER BY 2 DESC , 3 DESC

Upvotes: 0

Views: 205

Answers (1)

SWeko
SWeko

Reputation: 30882

Just cast the first column in the list as a varchar(max)

SELECT cast(ColumnA as varchar(max))
      + ColumnB + ColumnC + .... + ColumnZ AS SomeColumn 
FROM SomeTable

Upvotes: 2

Related Questions