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:
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:
Either one works for me!