Fix Index Naming

• Ligtorn

  Ten Centuries

  Points: 1027

  More actions

  October 11, 2010 at 12:22 am

  #225005

  Comments posted to this topic are about the item Fix Index Naming

• Michael Dillon

  SSC Rookie

  Points: 46

  More actions

  October 11, 2010 at 3:00 am

  #1234833

  I think some of the functions used within the script are missing from the script. Hopefully these can be linked too.

• Ligtorn

  Ten Centuries

  Points: 1027

  More actions

  October 11, 2010 at 3:06 am

  #1234836

  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

  SSC Rookie

  Points: 46

  More actions

  October 11, 2010 at 3:10 am

  #1234837

  Thanks, I had copied the SQL from the webpage and I didn't see those links earlier, thanks 🙂

• johnzabroski

  SSCrazy

  Points: 2355

  More actions

  April 18, 2014 at 1:43 pm

  #1707178

  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!