Schemabinding view

• aprasy

  Mr or Mrs. 500

  Points: 511

  More actions

  July 14, 2009 at 1:33 am

  #132045

  Hi,

  How to use system tables in view with schema binding?

  I tried to create the view with the following view definition. Its throws an error like

  "Msg 2720, Level 16, State 1, Procedure test1vw, Line 3

  Cannot schema bind view 'dbo.test1vw' because it references system object 'dbo.sysobjects'."

  It would be appreciated if anyone helps. I am struggling with this issue.

  create VIEW [dbo].[test1vw] with SCHEMABINDING

  AS

  SELECT

  A.Log_ID,

  B.[name] table_name,

  CASE A.audit_action_id

  WHEN 2

  THEN 'Insert'

  WHEN 1

  THEN 'Update'

  WHEN 3

  THEN 'Delete'

  END 'Action' ,

  FROM dbo.test1 A

  INNER JOIN

  dbo.sysobjects B

  ON

  A.[sysobj_id] = B.id

  go

  Thanks

  Pras

• Paul White

  SSC Guru

  Points: 150457

  More actions

  July 14, 2009 at 2:38 am

  #1024275

  You can't use WITH SCHEMABINDING on a view that references system objects, as you have discovered.

  There is often a way around this though. In your case, all you need is the OBJECT_NAME() function.

  If you replace the join with that function, you will be able to bind the view.

  If there is more to your real requirement than you posted in your example, please let us know.

  Paul

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi