"Saffron" wrote:

>
> I'm writing stored procedures to document tables. I want to be able to
> list all the views that reference a table.
>
> sp_help appears to provide this information but doesn't produce any
> results even for tables I know to be referenced. The sp_help code looks
> like this
>
>    select distinct 'Table is referenced by views' = obj.name
>    from sysobjects obj, sysdepends deps
>    where obj.xtype ='V'
>    and obj.id = deps.id
>    and deps.depid = @objid
>    and deps.deptype = 1 group by obj.name
>
> For this procedure, @objid is the id of the table for which we are
> getting the info.
>
> After taking this apart and looking at the results, I get the correct
> views as answers when I use the following where clause:
>
>    sysdepends.depid=@objid
>    and sysdepends.depid=sysobjects.id
>
> For the results, sysobjects.xtype is 'U' and not 'V' which the MSDN
> T-SQL reference would suggest is a user table (not a view), and the
> sysdepends.deptype value is 0 and not 1.
>
> Does anyone know of a reason why my views would have an xtype of 'U'
> and not 'V'?
> Why would the deptype value be not as the sp_help proc expects?
>
> Confused!
>
> -- Saffron
>
>

"Saffron" wrote:

>
> I'm writing stored procedures to document tables. I want to be able to
> list all the views that reference a table.
>
> sp_help appears to provide this information but doesn't produce any
> results even for tables I know to be referenced. The sp_help code looks
> like this
>
>    select distinct 'Table is referenced by views' = obj.name
>    from sysobjects obj, sysdepends deps
>    where obj.xtype ='V'
>    and obj.id = deps.id
>    and deps.depid = @objid
>    and deps.deptype = 1 group by obj.name
>
> For this procedure, @objid is the id of the table for which we are
> getting the info.
>
> After taking this apart and looking at the results, I get the correct
> views as answers when I use the following where clause:
>
>    sysdepends.depid=@objid
>    and sysdepends.depid=sysobjects.id
>
> For the results, sysobjects.xtype is 'U' and not 'V' which the MSDN
> T-SQL reference would suggest is a user table (not a view), and the
> sysdepends.deptype value is 0 and not 1.
>
> Does anyone know of a reason why my views would have an xtype of 'U'
> and not 'V'?
> Why would the deptype value be not as the sp_help proc expects?
>
> Confused!
>
> -- Saffron
>
>