Posted by Steve on 09/29/04 11:33

Erland Sommarskog wrote:

> 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.
>
>
This looks somewhat promising. I'll give it a try over the weekend and let
you know how if it works out. I tried to move from a stored procedure to a
stored function yesterday and ended up frustrated. I could almost get it to
work, but I couldn't come up with a way to return the scalar value from the
EXECUTE @sql. There is still a lot I have to learn. Old dog, new tricks :)

Steve Caldwell

[Back to original message]