I am looking for an sql statement that will retrieve all views in a databae,
but only if the view contains data. Here are some queries I am working
with:

select [name] from sysobjects where xtype='V' ---- This brings back a list
of all views, even if they are empty.


OK, the below query does not work (Error = 'Incrorect syntax...'), but it
does contains the logic I am trying to develop.

select [name], count(*) from dbo.sysobjects
where [name] in (select [name] from dbo.sysobjects where xtype = 'V') where
count (*) > 0


Gary

Hallo Gary,
in MSQl i will try it by this way

Declare #Table -- storage temporary
table for result
(ViewName nVarChar(100))

Declare @ViewName nVarchar(100) -- Save current viewname

Declare Cursor_Views Cursor local -- Cursor to walk trough the Views
For select S.Name sysobjects where xtype='V' .....

Open Cursor Cursor_Views -- open the cursor

Fetch Next From Cursor_Views into @ViewName -- get the first viewname

While @@Fetch_Status = 0 -- are the rows left?
begin
if Exists(Select * From @ViewName) -- has view records? (try this with
Execute, because SQL can not run variablen statemants)
begin
Insert into #Table -- yes .. insert the
view name into the temp table
(Name) (Select @ViewName)
end

Fetch Next From Cursor_Views into @ViewName -- get thenext viewname
end

Close Cursor_Views -- close cursor
Deallocate Cursor_Views

I have not testet it. !!!!!!!!!!!11
i hope it help you to find a way.

Erich

"Gary Rynearson" <grynearson@gfnet.com> schrieb im Newsbeitrag
news:ODUVeItaFHA.220@TK2MSFTNGP10.phx.gbl...
[quoted text, click to view]