Cannot schema bind view ‘ViewName’ because name ‘TableName’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Are you trying to create a view with SCHEMABINDING Option and getting error like “Cannot schema bind view ‘ViewName’ because name ‘TableName’ is invalid for schema binding……”

When we create view with SCHEMABINDING it is necessary to specify the schema name in objects name of SELECT statement otherwise it throw errors. For Example :

1
2
3
4
5
6
7
8
ALTER VIEW [UsersRolesView] 
	WITH SCHEMABINDING
AS
 
	SELECT R.RoleName,COUNT(U.RoleID) ActiveUserCount FROM tblRoles R 
	LEFT JOIN (SELECT UserID,RoleID FROM dbo.tblUsers WHERE IsActive=1) U ON U.RoleID=R.RoleID
	GROUP BY R.RoleID,R.RoleName
GO

A very simple solution to this is that “the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database”

For Example :

1
2
3
4
5
6
7
8
ALTER VIEW [UsersRolesView] 
	WITH SCHEMABINDING
AS
 
	SELECT R.RoleName,COUNT(U.RoleID) ActiveUserCount FROM dbo.tblRoles R 
	LEFT JOIN (SELECT UserID,RoleID FROM dbo.tblUsers WHERE IsActive=1) U ON U.RoleID=R.RoleID
	GROUP BY R.RoleID,R.RoleName
GO

Rahul is a Data Geek, technology enthusiast, a passionate writer, thinker with passion for computer programming.  He loves to explore technology and finds ultimate joy when writing about trending technology, geek stuff and web development.