While rebuilding all indexes on a CRM database, one of my friend reported below error.

Msg 2547, Level 16, State 1, Line 15
Unable to process object ID 2007014231 (object "AttributeTypes") because it is a synonym. If the object referenced by the synonym is a table or view, retry the operation using the base object that the synonym references."

First let’s understand the meaning of the error. The error message clearly says that error is due to the fact that we are running DBCC command for a synonym which is not possible. we need to run that on base object. Here is the simple repro of the error.

use tempdb
go
if object_id('TestTable') is not null
drop table TestTable
go
if object_id('TestTableSyn') is not null
drop synonym TestTableSyn
go
create table TestTable (i int)
go
CREATE SYNONYM [dbo].[TestTableSyn] FOR [dbo].TestTable
GO
dbcc DBREINDEX('TestTableSyn')
go
--dbcc CHECKTABLE('TestTableSyn')
--go

You would get below error in the result.

Msg 2547, Level 16, State 1, Line 15

Unable to process object ID 2007014231 (object "AttributeTypes") because it is a synonym. If the object referenced by the synonym is a table or view, retry the operation using the base object that the synonym references."

Let’s have a look at script what he was using.

USE DBName   --Enter the name of the database you want to reindex 

DECLARE @TableName varchar(255) 

DECLARE TableCursor CURSOR FOR 
SELECT table_name FROM information_schema.tables 
WHERE table_type = 'base table' 

OPEN TableCursor 

FETCH NEXT FROM TableCursor INTO @TableName 
WHILE @@FETCH_STATUS = 0 
BEGIN 
PRINT 'Reindexing ' + @TableName 
DBCC DBREINDEX(@TableName,' ',90) 
FETCH NEXT FROM TableCursor INTO @TableName 
END 
CLOSE TableCursor 

Do you see any problem? It looks good but notice that schema name is not considered in the script. I executed below and that would explain the cause.

Select * from sys.objects where name = 'AttributeTypes'

Looking at output, we can tell that there are two objects with same name in two different schemas (1 and 5). Schema 1 would be dbo and 5 was Metadata. In the script which he was using, the schema name was not considered. Due to this DBCC DBREINDEX is running for dbo.AttributeTypes which is synonym (2007014231)and due to that we are seeing error.

I provided below modified script.

DECLARE @TableName SYSNAME
        ,@schema_name SYSNAME
        ,@fullname VARCHAR(8000)
DECLARE TableCursor CURSOR
FOR
SELECT  TABLE_NAME
        ,TABLE_SCHEMA
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName ,@schema_name
WHILE @@FETCH_STATUS = 0
BEGIN
        SET @fullname = @schema_name + '.' + @TableName
        PRINT @fullname
        DBCC DBREINDEX (@fullname,' ',90)
        FETCH NEXT FROM TableCursor INTO @TableName ,@schema_name

END
CLOSE TableCursor
DEALLOCATE TableCursor
go

I tested the script and it worked fine for CRM database. Same issue was blogged by my colleague John over here and it has some more suggestions to try.

On a side note, ideally you should use Maintenance plan available with SQL Server to rebuild the indexes.

Hope this helps!