SQL SERVER – FIX: sp_rename error Msg 15225 – No item by the name of ‘%s’ could be found in the current database

Few days back I have written blog containing script for removing space in column name using sp_rename. In case you missed, I am listing them below for quick reference.

SQL SERVER – Script: Remove Spaces in Column Name in All Tables

SQL SERVER – How to Rename a Column Name or Table Name

While playing with sp_rename, I came across an interesting error message and I looked into the code of sp_rename to understand the issue. Here is the error message which I received.

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 387
No item by the name of ‘TableTwo’ could be found in the current database ‘SQLAuthority’, given that @itemtype was input as ‘(null)’.

For simplification, I have made up below script to explain the error.

CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
CREATE SCHEMA HR
GO
CREATE TABLE dbo.TableOne (ID INT PRIMARY KEY)
GO
CREATE TABLE HR.TableTwo (ID INT, FName VARCHAR(100))
GO
sp_rename 'TableOne', 'TableOne_renamed' -- This works
GO
sp_rename 'TableTwo','TableTwo_renamed' -- This fails

If we look at sp_helptext sp_rename we would see below at line 387 (as shown in error)

-- was the original name valid given this type?
IF (@objtype IN ('object','userdatatype') AND @CountNumNodes > 3)
BEGIN
COMMIT TRANSACTION
RAISERROR(15225,-1,-1,@objname, @CurrentDb, @objtypeIN)
RETURN 1
END

I looked at beginning of sp_rename the stored procedure and found some documentation there, there were some good rules listed.

• To rename a table, the @objname (meaning OldName) parm can be passed in totally unqualified or fully qualified.
• The SA or DBO can rename objects owned by lesser users, without the need for SetUser.
• The Owner portion of a qualified name can usually be passed in in the omitted form (as in MyDb..MyTab or MyTab). The typical exception is when the SA/DBO is trying to rename a table where the @objname is present twice in sysobjects as a table owned only by two different lesser users; requiring an explicit owner qualifier in @objname.
• An unspecified Owner qualifier will default to the current user if doing so will either resolve what would otherwise be an ambiguity within @objtype, or will result in exactly one match.
• If Database is part of the qualified @objname, then it must match the current database. The @newname parm can never be qualified.
• Here are the valid @objtype values. They correspond to system tables which track each type: ‘column’  ‘database’  ‘index’  ‘object’  ‘userdatatype’  ‘statistics’
• The @objtype parm is sometimes required. It is always required for databases.  It is required whenever ambiguities would otherwise exist.  Explicit use of @objtype is always encouraged.

Parms can use quoted_identifiers.  For example: Execute sp_rename ‘amy.”his table”‘,'”her table”‘,’object’

So, we are getting error because our table TableTwo is not in the default schema which is dbo. The solution is very simple: we need to qualify the name of the table with the schema name as shown below.

sp_rename 'HR.TableTwo','TableTwo_renamed' -- Now This would work

While reading the ruled listed in stored procedure, I realized that same error can also come if there is a dot in table name. we need to use [] around such tables. Here is an example

USE SQLAuthority
GO
CREATE TABLE [Name.With.dot] ( i INT)
GO
sp_rename 'Name.With.dot', 'New_Name.With.dot' -- would fail
GO
sp_rename '[Name.With.dot]', 'New_Name.With.dot' -- would work
GO

Hope this would help. Do let me know if you have used something like this before in your environments. Feel free to share via comments below.

Reference: Pinal Dave (https://blog.sqlauthority.com)