Hi,
The following query ran in SSMS works perfectly. It takes every table name from a database and looks up the most recent value from the last_update_stamp column of each table.
My query:
drop table if exists ##productUpdate
drop table if exists ##temp
CREATE TABLE ##productUpdate([table_name] varchar(30),[last_update_stamp] datetime)
SELECT [table_name], ROW_NUMBER() OVER (Order by [table_name] asc) as rowid
INTO ##temp
FROM (
select distinct [name] as [table_name]
from sys.Objects
) as a
declare @count int, @rowMax int, @date datetime, @sql nvarchar(max);
set @count = 1
set @rowMax =(SELECT Max(rowid) FROM ##temp);
WHILE @count <= @rowMax
BEGIN
set @sql = 'SELECT @x = MAX(last_update_stamp) FROM '+(SELECT [table_name] FROM ##temp WHERE rowid=@count)+';'
exec sp_executesql @sql, N'@x datetime out', @date out
INSERT INTO ##productUpdate SELECT (SELECT [table_name] FROM ##temp WHERE rowid=@count), @date
set @count=@count+1
END
SELECT * FROM ##productUpdate
I get the following error message: Details: "Microsoft SQL: Incorrect syntax near the keyword 'user'."
I made sure to use Import connection with my SSMS db.
Any way to solve this ?
