Getting A Value From All User Tables

Question

Post reply

Getting A Value From All User Tables

recycled

Mr or Mrs. 500

Points: 529
More actions
October 16, 2003 at 3:58 pm

#81317

I am trying to loop thru all the tables in the database to get the lastupdated field's value into a variable so that I can perform an INSERT into an audit table. I can't get the MAX(LastUpdated) value into a variable while using a cursor or sp_MSForEachTable. Any suggestions?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

gljjr SSCrazy Eights Points: 9970 More actions · Answer 1

I think what I would do is create a temp table and do an insert of the table name and date.

IE:



DECLARE @SQL nvarchar(4000)

    , @tblName sysname

    , @cTbls cursor



IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id('tempdb..#Audit'))

    DROP TABLE #Audit

CREATE TABLE #Audit

    (

    tblName sysname

    , LastUpdated datetime

    )



SET @cTbls = cursor for

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

FOR READ ONLY



OPEN @cTbls

FETCH @cTbls INTO @tblName

WHILE @@FETCH_STATUS = 0

    BEGIN

        SET @SQL = 'insert into #Audit SELECT ''' + @tblName + ''', MAX(LastUpdated) FROM ' + @tblName 

        EXEC sp_executesql @SQL



        FETCH @cTbls INTO @tblName

    END

SELECT * FROM #Audit

Note that you will want to make sure that the table actually has the LastUpdated column.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.