Андрей Ласточкин

Там совсем все неправильно

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

Не проверял.

WITH ... INSERT ... оставляю на твоей совести - там тоже можно существенно упростить