Solved

Dynamically querying tables without using EXEC

Posted on 2013-12-09
6
227 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now