How to find binding errors to views

22 Jul 2005 2:40 AM

Hassan

I have a view with no table present such as

Create table T1
(Col1 int)
go
Create view V1
as
select * from T1
go

drop table T1
go

select * from V1

I want to be able to run a query against a database that would give me all
the views that are inconsistent as above

I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..

22 Jul 2005 4:24 AM

Anith Sen

I don't think there is a single way to get the list of all the views with
binding errors. Perhaps, one option is to execute sp_refreshview in a
cursor/loop which will error out.

If this is something you'd want to preserve for any future views, perhaps
you should consider using explicit column names in SELECT clauses, two part
naming in FROM clauses and WITH SCHEMABINDING option while creating the
view.

--
Anith

22 Jul 2005 4:41 AM

Hassan

I tried the cursor approach but the cursor aborts after it finds the first
violation. How can I let it continue ?

Show quote

"Anith Sen" <an***@bizdatasolutions.com> wrote in message
news:eHi%23IVnjFHA.2444@TK2MSFTNGP10.phx.gbl...
> I don't think there is a single way to get the list of all the views with
> binding errors. Perhaps, one option is to execute sp_refreshview in a
> cursor/loop which will error out.
>
> If this is something you'd want to preserve for any future views, perhaps
> you should consider using explicit column names in SELECT clauses, two
part
> naming in FROM clauses and WITH SCHEMABINDING option while creating the
> view.
>
> --
> Anith
>
>

22 Jul 2005 6:45 AM

mark baekdal

I do a build of the database using the source code using DB Ghost Database
Builder. That way all errors are quickly reported and can therefore be fixed.
The builder builds objects at around 1000/minute making this a very quick and
extremely thorough process.

regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server

Show quote

"Hassan" wrote:

> I have a view with no table present such as
>
> Create table T1
> (Col1 int)
> go
> Create view V1
> as
> select * from T1
> go
>
> drop table T1
> go
>
> select * from V1
>
> I want to be able to run a query against a database that would give me all
> the views that are inconsistent as above
>
> I tried dbcc checkdb and dbcc checktable and it doesnt seem to work..
>
>
>
>