--This query will show the tables and their insert/update records for current date but only for those tables who have ModifiedDate Column in the table
CREATE TABLE #TEMPRECORD
(
ID INT IDENTITY,
TABLENAME VARCHAR(200)
)
DECLARE @I INT =1,
@TOTAL INT =0,
@SQL VARCHAR(500),
@TNAME VARCHAR(200),
@COLUMNNAME VARCHAR(100)
SELECT @COLUMNNAME = 'MODIFIEDDATE'
INSERT #TEMPRECORD (TABLENAME)
SELECT DISTINCT O.NAME FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C
ON O.OBJECT_ID = C.OBJECT_ID
WHERE O.TYPE ='U'
AND C.NAME = @COLUMNNAME
SELECT @TOTAL = COUNT(*) FROM #TEMPRECORD
WHILE @I <= @TOTAL
BEGIN
SELECT @TNAME = NAME FROM SYS.OBJECTS WHERE NAME = (SELECT TABLENAME FROM #TEMPRECORD WHERE ID =@I )
SELECT @SQL ='SELECT * FROM '+@TNAME +' WHERE CONVERT(VARCHAR(12),'+@COLUMNNAME+',101) = CONVERT(VARCHAR(12),GETDATE(),101)'
SELECT @TNAME AS [TABLE NAME]
EXEC (@SQL)
SET @I=@I+1
END
GO
DROP TABLE #TEMPRECORD
GO
