Solved

Dynamically querying tables without using EXEC

Posted on 2013-12-09
6
228 Views
Last Modified: 2013-12-17
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_TABLE_USAGE
) A
) B
0
Comment
Question by:riffrack
6 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39706400
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39706976
>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
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39706996
OP already seems to be doing that: his column name is "LastModified".
0
 

Accepted Solution

by:
riffrack earned 0 total points
ID: 39713459
Hi, thanks for your input, however I was not able to implement the solution using an SP, as our DBA does not allow this. I have worked around it and thought I would share my solution. Any feedback welcome:

INSERT INTO      CachingTables (Table_Name, LastModified)
SELECT DISTINCT      Table_Name, '2000-01-01'
FROM                      INFORMATION_SCHEMA.VIEW_TABLE_USAGE

-- repeat this for all tables
UPDATE CT
SET CT.LastModified = TAB.LastModified
FROM CachingTables CT
INNER JOIN
(SELECT ISNULL(MAX(LastModified), '2000-01-01')      AS LastModified, 'Companies'                                          AS Table_Name  FROM Companies) TAB ON TAB.Table_Name = CT.Table_Name


-- Clear CachingViews table
DELETE FROM CachingViews

-- Insert Lastmodified Dates into views tables
;WITH ViewBaseTables (VIEW_NAME, TABLE_NAME, Level)
AS
(
-- Anchor member definition
      SELECT      VIEW_NAME, TABLE_NAME, 0 AS Level
      FROM      INFORMATION_SCHEMA.VIEW_TABLE_USAGE e
      UNION ALL
-- Recursive member definition
      SELECT      d.VIEW_NAME, e.TABLE_NAME, Level + 1
      FROM      INFORMATION_SCHEMA.VIEW_TABLE_USAGE e
      INNER JOIN ViewBaseTables AS d
            ON      d.TABLE_NAME = e.VIEW_NAME
      )
      INSERT INTO CachingViews
      -- Statement that executes the CTE
      SELECT      VIEW_NAME, MAX(LastModified) AS LastModified
      FROM      ViewBaseTables VBT
      INNER JOIN CachingTables CT
            ON      VBT.TABLE_NAME = CT.Table_Name
      WHERE      VBT.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS)
      GROUP BY VIEW_NAME
0
 

Author Closing Comment

by:riffrack
ID: 39723462
This solution works for us, hence I'm sharing this for others.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question