Is there a query to find the list of tables that has schema binding views associated with it?

• metoseeu

  SSChasing Mays

  Points: 630

  More actions

  April 6, 2009 at 1:55 am

  #207981

  Hi

  Is there a query to find the list of tables that has schema binding views associated with it also

  When we enter ALT + F1 on a table table we get these information as the last one as

  "Table is referenced by views" .

  does schema binding views have any connection with "Table is referenced by views" .

• harshalmistry

  Ten Centuries

  Points: 1184

  More actions

  April 6, 2009 at 3:38 am

  #972378

  use the following query to find if the table is being referenced by a schema binding view:

  DECLARE @objid int

  SELECT @objid = 1333579789

  select distinct 'Table is referred by schema bound views' = obj.name from sys.objects obj, sysdepends deps

  where obj.type ='V' and obj.object_id = deps.id and deps.depid = @objid

  and deps.deptype = 1 group by obj.name

  here objid is the objectID of the table from sysobjects table.

  And yes "'Table is referenced by views'" shows only those views which are schema bound.

  Hope this helps.

  -Harshal

  Harshal

• metoseeu

  SSChasing Mays

  Points: 630

  More actions

  April 6, 2009 at 4:36 am

  #972403

  Hi

  Thanks harshalmistry

  This is the exact one which I was looking for.

  Just for the Info - when we enter ALT + F1 in a table

  'Table is referenced by views' -- Views are listed if there are only schema bound views

  'Table is referenced by views' -- Just displayed but no values will be present if the table has any CHECK constraint.

  'Table is referenced by views' -- Will not be even displayed if above two are not there.

  thanks

  metoseeu