Solved

Dynamically querying tables without using EXEC

Posted on 2013-12-09
6
225 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:ScottPletcher
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

706 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

13 Experts available now in Live!

Get 1:1 Help Now