riffrack
asked on
Dynamically querying tables without using EXEC
Hi experts
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
FROM
(
SELECT A.view_name, A.Table_Name, (SELECT MAX(LastModified) FROM A.Table_Name) AS LastModPerTable
FROM
(
SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TA
) A
) B
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
FROM
(
SELECT A.view_name, A.Table_Name, (SELECT MAX(LastModified) FROM A.Table_Name) AS LastModPerTable
FROM
(
SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TA
) A
) B
>query all tables used in all views to find out per view whether any data in the base tables have changed.
I'm not aware of any automated auditing of 'whether any data..has changed', unless a developer created this Change Data Capture (CDC) by table triggers that write all 'old' rows to an auditing table.
Most developers I know that have these needs would add four columns to every table:created_by varchar(15), created_dt datetime, last_updated_by varchar(15), and last_updated_dt datetime. Then, you could run a query(ies) that go something like this...
SELECT *
FROM YourTable
WHERE last_updated_dt > @some_date
I'm not aware of any automated auditing of 'whether any data..has changed', unless a developer created this Change Data Capture (CDC) by table triggers that write all 'old' rows to an auditing table.
Most developers I know that have these needs would add four columns to every table:created_by varchar(15), created_dt datetime, last_updated_by varchar(15), and last_updated_dt datetime. Then, you could run a query(ies) that go something like this...
SELECT *
FROM YourTable
WHERE last_updated_dt > @some_date
OP already seems to be doing that: his column name is "LastModified".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This solution works for us, hence I'm sharing this for others.
http://weblogs.asp.net/nunogomes/archive/2008/08/19/sql-server-undocumented-stored-procedure-sp-msforeachtable.aspx