Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Fix Index Naming Expand / Collapse
Author
Message
Posted Monday, October 11, 2010 12:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 1, 2014 3:35 AM
Points: 233, Visits: 463
Comments posted to this topic are about the item Fix Index Naming
Post #1001940
Posted Monday, October 11, 2010 3:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 19, 2011 6:39 AM
Points: 10, Visits: 24
I think some of the functions used within the script are missing from the script. Hopefully these can be linked too.
Post #1001989
Posted Monday, October 11, 2010 3:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 1, 2014 3:35 AM
Points: 233, Visits: 463
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
Post #1001992
Posted Monday, October 11, 2010 3:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 19, 2011 6:39 AM
Points: 10, Visits: 24
Thanks, I had copied the SQL from the webpage and I didn't see those links earlier, thanks
Post #1001994
Posted Friday, April 18, 2014 1:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 6, 2014 1:24 PM
Points: 49, Visits: 189
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!
    Post #1563161
    Posted Friday, April 18, 2014 2:28 PM
    SSC Rookie

    SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

    Group: General Forum Members
    Last Login: Friday, June 6, 2014 1:24 PM
    Points: 49, Visits: 189
    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.
    Post #1563173
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse