Dynamically querying tables without using EXEC
Posted on 2013-12-09
This is probably not possible, yet it would sometimes be amazingly useful. I want to query all tables used in all views to find out per view whether any data in the base tables have changed. I cannot query the views directly, as the required fields are not available in the views.
Anyway I came up with following idea, which of course doesn't work, because of the "FROM A.Table_Name" part. Is there anyway to work around this without used the ugly EXEC (@sql) method?
SELECT B.view_name, MAX(B.LastModPerTable) AS LastModPerView
SELECT A.view_name, A.Table_Name, (SELECT MAX(LastModified) FROM A.Table_Name) AS LastModPerTable
SELECT view_name, Table_Name