CODEWITHSUNDEEP
sql-servernested
Alex
Alex

Reputation: 433

Looping through a table in a nested query in SQL Server 2005

I have a task where I have to identify the company database tables last_user_update and then store that in a separate table where reports for each tables last update can be monitored. What I'm aiming to do is have my query loop through a list of table names and so far I have the following:

  1. Identify the all tables in the database:

      INSERT INTO [CorpDB1].[dbo].[tblTableNames]
   ([name])
   SELECT *
   FROM sys.Tables
   ORDER by name

The result is a table containing a 984 rows of table names for all tables in the DB.

Next I have the following:

  1. Query to return and insert last_user_update into a new table called DatabaseTableHistory:

     INSERT INTO [CorpDB1].[dbo].[DatabaseTableHistory]
       ([DatabaseName]
       ,[last_user_update]
       ,[database_id]
       ,[object_id]
       ,[index_id]
       ,[user_seeks]
       ,[user_scans]
       ,[user_lookups]
       ,[user_updates]
       ,[last_user_seek])

 SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,    
 database_id, object_id, index_id, user_seeks, user_scans, user_lookups, 
 user_updates, last_user_seek
 FROM sys.dm_db_index_usage_stats
 WHERE database_id = DB_ID( 'CorpDB1')
 AND OBJECT_ID=OBJECT_ID('tblStatesList')  AND last_user_update <     
 '20150430'

This query works as designed. What I'm trying to do is have the last query loop through the table containing the list of table names inserting it where OBJECT_ID=OBJECT_ID('tbleStatesList') is so I don't have to manually run the query by typing each table name in by hand. Any suggestions would be appreciated.

Upvotes: 1

Views: 222

Answers (2)

Mark Sinkinson
Mark Sinkinson

Reputation: 976

First things first, apart from being wrong (SELECT * will return much more than just name), your first query is completely unnecessary. There's no need to select your table names into a cache table.

You can get the list of tables you need by using an INNER JOIN between sys.dm_db_index_usage_stats and sys.tables on the object_id.

I'm assuming you want all the indexes on CorpDB1 tables that have a last_user_update prior to 30th April 2015.

USE CorpDB1;
GO

INSERT INTO [CorpDB1].[dbo].[DatabaseTableHistory]
       ([DatabaseName]
       ,[last_user_update]
       ,[database_id]
       ,[object_id]
       ,[index_id]
       ,[user_seeks]
       ,[user_scans]
       ,[user_lookups]
       ,[user_updates]
       ,[last_user_seek])

 SELECT 
  DB_NAME(ddius.database_id) AS DatabaseName, -- Fix this on your query
  ddius.last_user_update,    
  ddius.database_id, 
  ddius.object_id, 
  ddius.index_id, 
  ddius.user_seeks, 
  ddius.user_scans, 
  ddius.user_lookups, 
  ddius.user_updates, 
  ddius.last_user_seek
 FROM sys.dm_db_index_usage_stats ddius
 INNER JOIN sys.tables AS t
  ON t.object_id = ddius.object_id
 WHERE database_id = DB_ID( 'CorpDB1')
   AND last_user_update < '20150430';

Upvotes: 2

Phil Cazella
Phil Cazella

Reputation: 854

If you are working with MSSQL, I would suggest creating a stored procedure to loop through the first dataset using a cursor.

See the link below for a short tutorial on how to create and user cursors for this purpose.

http://stevestedman.com/2013/04/t-sql-a-simple-example-using-a-cursor/

Upvotes: -1

Related Questions