Solved

Dynamically querying tables without using EXEC

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 14 40
Trigger C# code inside the SQL Server 6 36
Access #Deleted data 20 45
Can a Trigger trigger a Trigger? 4 25
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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

696 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