If you need to delete all the user creation tables in TSQL. You can easily perform the deleting using the while loop against the sysobjects table.

The idea of the deletion is by declaring a variable name that will hold the table name and then we going to perform a select top 1 from the table name and perform deletion in the while loop while setting back the name to the next deletion table name until the table name is no longer found or equals to null.

Here is the full TSQL query code.

DECLARE @tableName VARCHAR(128)
DECLARE @SQL VARCHAR(500)

SELECT @tableName = (
	SELECT TOP 1 [name] FROM sysobjects 
	WHERE [type] = 'U' 
	AND category = 0 
	ORDER BY [name]
)

WHILE @tableName IS NOT NULL
	BEGIN
		SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@tableName) +']'
		EXEC (@SQL)
		PRINT 'Deleted Table: ' + @tableName
		SELECT @tableName = (
			SELECT TOP 1 [name] 
			FROM sysobjects 
			WHERE [type] = 'U' 
			AND category = 0 
			AND [name] > @tableName 
			ORDER BY [name]
		)
	END
GO