October 16, 2003 at 3:58 pm
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?
October 16, 2003 at 7:19 pm
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.
October 17, 2003 at 4:10 am
Seems like it should be possible to make sp_MSForEachTable work. Not that there is anything wrong with what Gary suggests.
Andy
October 17, 2003 at 9:42 am
Thanks for the suggestion. Not all tables have the LastUpdated column so I will add error code to skip them.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply