Posted by Erland Sommarskog on 09/26/00 11:33

Steve (sjc4914@yahoo.com) writes:
> The double-bad news is that table are created or dropped frequently, at
> least during the initial deployment

Congratulations. :-(

> Can you point me to a good tutorial or book on creating dynamic views?
> That would solve another data access problem that is looming on the
> horizon -- I can feel it coming. The view could be regenerated daily
> during off-hours, and that would be sufficient.

"Dynamic views" are not dynamic in the true sense, they are just dynamically
generated. All you need is means to identify the tables in question. For
instance say all these tables opens with the string 'lb1table'. Then you
would set up a cursor over

SELECT name FROM sysobjects WHERE name LIKE 'lbltable%'

And then foreach row add to the view definition

SELECT @viewdef = @viewdef +
'SELECT tblname = ''' + @name + ''', * FROM ' + @name +
' UNION ALL'

At the end of the loop you need get rid of that last UNION ALL. Eventually
you would say EXEC(@viewdef).

Here I used T-SQL, but you could just as well write a program in VB, C,
Perl whatever that did the same thing. The problem with doing this in
T-SQL, is that you get problems if the view definition does not fit into
a varchar(8000).

The tricky part may be to identify which tables to include. Possibly you
will need to query other system tables, for instance syscolumns.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]