Кто поможет с запросом T_SQL?

Question

[P_Zeger]

Буду рад помощи с запросом.
Необходимо написать процедуру, которая находит удаляет таблицы,для которых был выполнен последний раз запрос до данной даты (input).
Процедура, которую я написал не выдает результат - ошибка происходит во второй петле while.
Сервер просто выдает сообщение,что команда выполнена успешно...

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

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

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

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 ... оставляю на твоей совести - там тоже можно существенно упростить