Post reply

Index Related Question

  • January 15, 2017 at 3:21 pm

    #325466

    Hi,

    I have a table as below
    Create Table Tables(Id int identity(1,1),TableName varchar(50),SchemaName nvarchar(20))

    This table contains tablename where i need to generate Create index script(non clustered). Assume i have 5 table name in my above table and  those 5 tables already in my database with proper schema. i need to generate the "Create non clustered index script ". i googled through and got the below query
     
    SELECT ' CREATE ' +
      CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END + 
      I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' + 
      I.name + ' ON ' + 
      Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +
      KeyColumns + ' ) ' +
      ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +
      ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH ( ' +
      CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' +
      'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' +
      -- default value
      'SORT_IN_TEMPDB = OFF ' + ',' +
      CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ',' +
      CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ',' +
      -- default value 
      ' DROP_EXISTING = ON ' + ',' +
      -- default value 
      ' ONLINE = OFF ' + ',' +
     CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ',' +
     CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END + ' ) ON [' +
     DS.name + ' ] ' [CreateIndexScript]
    FROM sys.indexes I 
    JOIN sys.tables T ON T.Object_id = I.Object_id 
    JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid 
    JOIN (SELECT * FROM ( 
      SELECT IC2.object_id , IC2.index_id , 
       STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END
      FROM sys.index_columns IC1 
      JOIN Sys.columns C 
       ON C.object_id = IC1.object_id 
       AND C.column_id = IC1.column_id 
       AND IC1.is_included_column = 0 
      WHERE IC1.object_id = IC2.object_id 
       AND IC1.index_id = IC2.index_id 
      GROUP BY IC1.object_id,C.name,index_id 
      ORDER BY MAX(IC1.key_ordinal) 
       FOR XML PATH('')), 1, 2, '') KeyColumns 
      FROM sys.index_columns IC2 
      --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables 
      GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4 
    ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id 
    JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id 
    JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id 
    JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id 
    LEFT JOIN (SELECT * FROM ( 
      SELECT IC2.object_id , IC2.index_id , 
       STUFF((SELECT ' , ' + C.name
      FROM sys.index_columns IC1 
      JOIN Sys.columns C 
       ON C.object_id = IC1.object_id 
       AND C.column_id = IC1.column_id 
       AND IC1.is_included_column = 1 
      WHERE IC1.object_id = IC2.object_id 
       AND IC1.index_id = IC2.index_id 
      GROUP BY IC1.object_id,C.name,index_id 
       FOR XML PATH('')), 1, 2, '') IncludedColumns 
     FROM sys.index_columns IC2 
     --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables 
     GROUP BY IC2.object_id ,IC2.index_id) tmp1 
     WHERE IncludedColumns IS NOT NULL ) tmp2 
    ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id 
    WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0
    --AND I.Object_id = object_id('Person.Address') --Comment for all tables
    --AND I.name = 'IX_Address_PostalCode' --comment for all indexes

    This gives all the non clustered index of my datababse. But how can i get just the tablename in the table "Tables"

    In the above query i can see 

     --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables  
    --AND I.Object_id = object_id('Person.Address') --Comment for all tables 


    i tried to uncomment those lines and to test just gave one table name. But it doesn't generate. Can anyone please  give me working script to generate create index script for specific tables. Please note i need to use sql query not using IDE generate script option

  • January 16, 2017 at 2:51 am

    #1922914

    The script you posted works.

    Check the name you're putting in the WHERE clause, and check if your DB is case sensitive, if it is the case will matter too.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 16, 2017 at 5:38 am

    #1922950

    GilaMonster - Monday, January 16, 2017 2:51 AM

    The script you posted works.

    Check the name you're putting in the WHERE clause, and check if your DB is case sensitive, if it is the case will matter too.

    Hi Hilary,

    thank you for your reply and if i don't put anything in the where condition this works. pulling all the tables script. But if i put condition it's not doing. below the sample you can play with. Please use your tablename in the table variable insert.


    declare @mytables table(tablename nvarchar(50),schemaname nvarchar(20));
    insert into @mytables(tablename,schemaname)
    select 'student','dbo';

    SELECT ' CREATE ' +
    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END +
    I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +
    I.name + ' ON ' +
    Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +
    KeyColumns + ' ) ' +
    ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +
    ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH ( ' +
    CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' +
    'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' +
    -- default value
    'SORT_IN_TEMPDB = OFF ' + ',' +
    CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ',' +
    CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ',' +
    -- default value
    ' DROP_EXISTING = ON ' + ',' +
    -- default value
    ' ONLINE = OFF ' + ',' +
    CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ',' +
    CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END + ' ) ON [' +
    DS.name + ' ] ' [CreateIndexScript]
    FROM sys.indexes I
    JOIN sys.tables T ON T.Object_id = I.Object_id 
    JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid
    JOIN (SELECT * FROM (
    SELECT IC2.object_id , IC2.index_id ,
      STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END
    FROM sys.index_columns IC1
    JOIN Sys.columns C
     ON C.object_id = IC1.object_id
     AND C.column_id = IC1.column_id
     AND IC1.is_included_column = 0
    WHERE IC1.object_id = IC2.object_id
     AND IC1.index_id = IC2.index_id
    GROUP BY IC1.object_id,C.name,index_id
    ORDER BY MAX(IC1.key_ordinal)
     FOR XML PATH('')), 1, 2, '') KeyColumns
    FROM sys.index_columns IC2
    join @mytables x on x.tablename=object_name(ic2.object_id )
    join sys.schemas ss on ss.name=x.schemaname
    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4
    ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id
    JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id
    JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id
    JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id
    LEFT JOIN (SELECT * FROM (
    SELECT IC2.object_id , IC2.index_id ,
      STUFF((SELECT ' , ' + C.name
    FROM sys.index_columns IC1
    JOIN Sys.columns C 
     ON C.object_id = IC1.object_id 
     AND C.column_id = IC1.column_id 
     AND IC1.is_included_column = 1
    WHERE IC1.object_id = IC2.object_id 
     AND IC1.index_id = IC2.index_id 
    GROUP BY IC1.object_id,C.name,index_id
     FOR XML PATH('')), 1, 2, '') IncludedColumns 
    FROM sys.index_columns IC2 
    GROUP BY IC2.object_id ,IC2.index_id) tmp1
    WHERE IncludedColumns IS NOT NULL ) tmp2 
    ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id
    join @mytables x on x.tablename=object_name(ic2.object_id )
    join sys.schemas ss on ss.name=x.schemaname
    WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0
    --AND I.Object_id = object_id('Person.Address') --Comment for all tables
    --AND I.name = 'IX_Address_PostalCode' --comment for all indexe

     

  • January 16, 2017 at 5:48 am

    #1922952

    That code throws an error.
    Msg 4104, Level 16, State 1, Line 66
    The multi-part identifier "ic2.object_id" could not be bound.

    Can you post the working code that you're using and is not returning anything?
    Why are you messing with a table variable with the names in?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 16, 2017 at 7:52 am

    #1922984

    GilaMonster - Monday, January 16, 2017 5:48 AM

    That code throws an error.
    Msg 4104, Level 16, State 1, Line 66
    The multi-part identifier "ic2.object_id" could not be bound.

    Can you post the working code that you're using and is not returning anything?
    Why are you messing with a table variable with the names in?

    finally this is working for me 


    SELECT ' CREATE ' +
    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END +
    I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +
    I.name + ' ON ' +
    Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +
    KeyColumns + ' ) ' +
    ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +
    ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH ( ' +
    CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' +
    'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' +
    -- default value
    'SORT_IN_TEMPDB = OFF ' + ',' +
    CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ',' +
    CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ',' +
    -- default value
    ' DROP_EXISTING = ON ' + ',' +
    -- default value
    ' ONLINE = OFF ' + ',' +
    CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ',' +
    CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END + ' ) ON [' +
    DS.name + ' ] ' [CreateIndexScript]
    FROM sys.indexes I
    JOIN sys.tables T ON T.Object_id = I.Object_id 
    JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid
    JOIN (SELECT * FROM (
    SELECT IC2.object_id , IC2.index_id ,
      STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END
    FROM sys.index_columns IC1
    JOIN Sys.columns C
     ON C.object_id = IC1.object_id
     AND C.column_id = IC1.column_id
     AND IC1.is_included_column = 0
    WHERE IC1.object_id = IC2.object_id
     AND IC1.index_id = IC2.index_id
    GROUP BY IC1.object_id,C.name,index_id
    ORDER BY MAX(IC1.key_ordinal)
     FOR XML PATH('')), 1, 2, '') KeyColumns
    FROM sys.index_columns IC2
    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4
    ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id
    JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id
    JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id
    JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id
    LEFT JOIN (SELECT * FROM (
    SELECT IC2.object_id , IC2.index_id ,
      STUFF((SELECT ' , ' + C.name
    FROM sys.index_columns IC1
    JOIN Sys.columns C 
     ON C.object_id = IC1.object_id 
     AND C.column_id = IC1.column_id 
     AND IC1.is_included_column = 1
    WHERE IC1.object_id = IC2.object_id 
     AND IC1.index_id = IC2.index_id 
    GROUP BY IC1.object_id,C.name,index_id
     FOR XML PATH('')), 1, 2, '') IncludedColumns 
    FROM sys.index_columns IC2 
    GROUP BY IC2.object_id ,IC2.index_id) tmp1
    WHERE IncludedColumns IS NOT NULL ) tmp2 
    ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id
    join @mytables x on x.tablename=object_name(I.Object_id )
    join sys.schemas ss on ss.name=x.schemaname
    WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply