Who can help with a T_SQL query?

Question

SQL

I would be happy to help you with your request.
It is necessary to write a procedure that finds and deletes tables for which the query was last executed before the given date (input).
The procedure I wrote doesn't produce a result - the error occurs in the second while loop.
The server just gives a message that the command completed successfully...

CREATE PROCEDURE usp_DropNonUsedTables @LastDateUse DATE
AS
BEGIN

  SET NOCOUNT ON

  DECLARE @TablesWithLastSeek AS TABLE (TableName VARCHAR(100),
                      LastAccessDate DATE);

  WITH LastDates AS 
  (SELECT SCHEMA_NAME(T.schema_id) + '.' + OBJECT_NAME(T.object_id) AS TableName,
   (SELECT MAX(last_user_date) FROM (VALUES (last_user_seek),(last_user_scan),(last_user_lookup)) AS AllValues(last_user_date)) AS AccessDate FROM sys.dm_db_index_usage_stats IUS
    RIGHT OUTER JOIN sys.tables T 
    ON IUS.object_id = T.object_id)
    
  INSERT INTO @TablesWithLastSeek
  SELECT TableName,MAX(AccessDate)
  FROM LastDates 
  GROUP BY TableName 

  DECLARE @Counter INT
  SET @Counter = (SELECT COUNT(*) FROM @TablesWithLastSeek)

  --PRINT @Counter
  --SELECT * FROM @TablesWithLastSeek

  DECLARE @TableName VARCHAR(100)
  DECLARE @LastAccessDate DATE
  DECLARE @Command VARCHAR(1000)

  WHILE @Counter > 0
  BEGIN
    
    SELECT TOP (1) @TableName = TableName,@LastAccessDate = LastAccessDate
    FROM @TablesWithLastSeek

    --PRINT @TableName
    --PRINT @LastAccessDate

    WHILE (SELECT LastAccessDate FROM @TablesWithLastSeek WHERE TableName = @TableName) <= @LastDateUse
    BEGIN
      SELECT @TableName = TableName
      FROM @TablesWithLastSeek
      WHERE LastAccessDate = @LastAccessDate
    END

    IF EXISTS (SELECT name FROM sys.all_objects WHERE name = @TableName)
      BEGIN
        SET @Command = 'DROP TABLE ' + @TableName + ';'
        --PRINT @Command
        EXECUTE (@Command)
      END

    SET @Counter = @Counter - 1
    
  END

END;
GO

Answer 1

It's all wrong there.

CREATE PROCEDURE usp_DropNonUsedTables @LastDateUse date
AS
BEGIN

    SET NOCOUNT ON

    DECLARE @TablesWithLastSeek AS TABLE (
        TableName varchar(100),
        LastAccessDate date
    );

    WITH LastDates
    AS (SELECT
        SCHEMA_NAME(T.schema_id) + '.' + OBJECT_NAME(T.object_id) AS TableName,
        (SELECT
            MAX(last_user_date)
        FROM (VALUES (last_user_seek), (last_user_scan), (last_user_lookup)) AS AllValues (last_user_date))
        AS AccessDate
    FROM sys.dm_db_index_usage_stats IUS
    RIGHT OUTER JOIN sys.tables T
        ON IUS.object_id = T.object_id)

    INSERT INTO @TablesWithLastSeek
        SELECT
            TableName,
            MAX(AccessDate)
        FROM LastDates
        GROUP BY TableName
        HAVING MAX(AccessDate) <= @LastDateUse

    DECLARE @TableName varchar(100)
    DECLARE @LastAccessDate date
    DECLARE @Command varchar(1000)

    DECLARE C CURSOR LOCAL FOR
    SELECT
        *
    FROM @TablesWithLastSeek

    OPEN C

    WHILE 1 = 1
    BEGIN
        FETCH C INTO @TableName, @LastAccessDate
        IF @@FETCH_STATUS != 0
            BREAK

        SET @Command = 'DROP TABLE ' + @TableName + ';'
        EXECUTE (@Command)
    END

    CLOSE C
  
END;
GO

Didn't check.
WITH ... INSERT ... I leave it to your conscience - there, too, you can significantly simplify