SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fix Index Naming


Fix Index Naming

Author
Message
Ligtorn
Ligtorn
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 489
Comments posted to this topic are about the item Fix Index Naming
Michael Dillon
Michael Dillon
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 24
I think some of the functions used within the script are missing from the script. Hopefully these can be linked too.
Ligtorn
Ligtorn
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 489
Did you download the install script from below. or did you copy the sql code. I tried both install scripts on a server, and none of them failed with missing table value functions

-- -- # IMPORTANT!!!
-- #
-- # Because of the rather lengthy install script, I am only showing the
-- # script for display. It won't install, because of missing table value
-- # functions. The version I have shown is the 2008 version
-- #
-- # You can download a complete install script here.
-- # 2005 version - http://files.soendergaard.info/Install_spFixIndexNaming_V2005.sql
-- # 2008 version - http://files.soendergaard.info/Install_spFixIndexNaming_V2008.sql
Michael Dillon
Michael Dillon
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 24
Thanks, I had copied the SQL from the webpage and I didn't see those links earlier, thanks :-)
johnzabroski
johnzabroski
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 191
Hi Michael,

These scripts have been invaluable to me in the past. I am trying to use them for the first time in a couple of years, and I am running into an odd error when it tries to rename :

Object '[dbo].[dtproperties].[pk_dtproperties]' does not exist or is not a valid object for this operation.



I think the problem is that it is trying to rename the PK for dtproperties, which is otherwise considered a system table despite being in the dbo schema:

EXECUTE sp_rename '[dbo].[dtproperties].[pk_dtproperties]','PK_dtproperties_a', 'index' 



Have idea?

I tried to see if it was a system-level data modification that was being blocked, as well, by modifying this config setting:

sp_configure 'allow updates', 1

RECONFIGURE WITH OVERRIDE;



... which didn't work.

Also, would be for taking patches?

I think in this case there is a bug, where you should exclude tables matching the following queries:


select * from sys.tables t where t.is_ms_shipped = 1

select * from sys.key_constraints kc where kc.is_ms_shipped = 1



What I would really love is two features:

  • An Exclude FilterExpression to complement the (Include) FilterExpression

  • Ability to resolve name clashes using uniqueidentifier


  • For the latter example, I imagine the code looking like this internally:


    DECLARE @UniqueID uniqueidentifier = NEWID();
    DECLARE @TempRenameCommand nvarchar(max);
    DECLARE @FinalRenameCommand nvarchar(max);

    SET @TempRenameCommand = 'sp_rename ''[dbo].[dtproperties].[pk_dtproperties]'',''PK_dtproperties_' + CAST(@UniqueID as nvarchar(50)) + ''', ''index''';
    --EXECUTE @TempRenameCommand
    SET @FinalRenameCommand = 'sp_rename ''[dbo].[dtproperties].PK_dtproperties_' + CAST(@UniqueID as nvarchar(50)) + ''',''PK_dtproperties'', ''index''';
    --EXECUTE @FinalRenameCommand
    --EXECUTE sp_rename '[dbo].[dtproperties].[pk_dtproperties]','PK_dtproperties', 'index'
    print @TempRenameCommand;
    print @FinalRenameCommand;



    I'll make two offers here:

  • Permission to fork your code and re-license it and put it on GitHub for others

  • Buy you a beer via PayPal and have you make these changes for me


  • Either one works for me!
    johnzabroski
    johnzabroski
    SSC-Addicted
    SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

    Group: General Forum Members
    Points: 431 Visits: 191
    Here are my patches:

  • If there are multiple covering indices on the same table column, then multiple runs of spFixIndexNaming will spuriously flip back and forth between the two.

  •               Uniquifier = ROW_NUMBER() OVER (PARTITION BY NewName ORDER BY SchemaName, OldName, NewName)


    Note I added OldName, in order to prevent spurious flipping back and forth.
    Test parameter:
       @IndexConvention = 'IX_%INDEX_TYPE%_%UNIQUE_INDEX%%FILTERED_INDEX%_%TABLE_NAME%_%INDEX_COLUMNS%'



  • Ignore any IsMsShipped tables: OBJECTPROPERTY(object_id, 'IsMSShipped') = 1

  •                         FROM  
    sys.indexes i
    JOIN sys.tables t ON
    i.object_id = t.object_id AND
    i.type_desc IN ('CLUSTERED', 'NONCLUSTERED', 'XML', 'SPATIAL')
    AND t.is_ms_shipped = 0


    Note I added AND t.is_ms_shipped = 0 to exclude IsMsShipped objects.
    Iwas Bornready
    Iwas Bornready
    SSC-Insane
    SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

    Group: General Forum Members
    Points: 22744 Visits: 885
    Thanks for another good script.
    Santhoshkumar KB
    Santhoshkumar KB
    SSCommitted
    SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

    Group: General Forum Members
    Points: 1692 Visits: 1076
    What would be the possible naming convention for a Filtered Index?
    FNCI !!

    Thanks,
    Santhosh

    Human Knowledge Belongs To The World !!
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search