Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Dynamically querying tables without using EXEC

Posted on 2013-12-09
6
Medium Priority
?
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39706400
0
 
LVL 66

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 70

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

636 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