Check for Index on table

IF EXISTS (SELECT *  FROM sys.indexes  WHERE name='Index_Name' 
    AND object_id = OBJECT_ID('[SchmaName].[TableName]'))
  begin
    DROP INDEX [Index_Name] ON [SchmaName].[TableName];
  end

Perform Mass Table Drops

You can build up a string using the catalog views, e.g.:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
DROP TABLE ' 
    + QUOTENAME(s.name)
    + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id] 
    WHERE t.name LIKE 'LG_001%';

PRINT @sql;
-- EXEC sp_executesql @sql;

To just get the list of tables, use:

SELECT s.name, t.name 
  FROM sys.tables AS t 
  INNER JOIN sys.schemas AS s 
  ON t.[schema_id] = s.[schema_id] 
  WHERE t.name LIKE 'LG_001%';

Compare two table definitions

 SELECT c.column_id
--, b.name as 'schema_name'
, a.name as 'Table_Name'
, c.name as 'Column_Name'
--, a.object_id
, c.max_length, c.precision, c.scale
--, a.schema_id, a.parent_object_id, a.type, a.type_desc, a.create_date, a.modify_date
into #temp1
 FROM sys.columns c INNER JOIN (sys.objects a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id) ON c.object_ID = a.object_id
 WHERE a.object_id IN (select object_ID from sys.columns where name like '%snapshot%')
  and b.name = 'dbo'
  and a.name = 'Flatfile_snapshot'
 ORDER BY a.[type], b.name, a.name

 SELECT c.column_id
--, b.name as 'schema_name'
, a.name as 'Table_Name'
, c.name as 'Column_Name'
--, a.object_id
, c.max_length, c.precision, c.scale
--, a.schema_id, a.parent_object_id, a.type, a.type_desc, a.create_date, a.modify_date
into #temp2
 FROM sys.columns c INNER JOIN (sys.objects a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id) ON c.object_ID = a.object_id
 WHERE 
--a.object_id IN (select object_ID from sys.columns where name like '%snapshot%')
b.name = 'dbo'
  and a.name = 'FlatFile_snapshot_MonthlyHC'
 ORDER BY a.[type], b.name, a.name

 --select * from #temp1
 --select * from #temp2

 SELECT a.column_id, a.column_name, a.max_length, a.precision, a.scale
, b.column_id, b.column_name, b.max_length, b.precision, b.scale
, a.max_length - b.max_length
 FROM #temp1 a INNER JOIN #temp2 b ON a.column_name = b.column_name
 --HAVING a.max_length - b.max_length <> 0
 ORDER BY a.column_id

 drop table #temp1
 drop table #temp2

The transaction log for database is full due to 'LOG_BACKUP'

There are only 2 ways the Transaction Log will truncate itself to reuse the internal space (Virtual log files) , 1 is through checkpoint process, on the simple recovery model when the log gets 70% full. The other, is after every Log Backup under Full recovery model. Maybe something is going on with these log backups. Perhaps they are completing in SQL Agent, but not doing anything at all? Try checking the backup history table in MSDB to make sure the log backups are happening and their physical file exist
SELECT TOP (10) a.database_name[DB],a.server_name[SQL INST],b.physical_device_name,a.backup_finish_date [BKP END DATE],CASE a.type
    WHEN 'D' THEN 'FULL'
    WHEN 'L' THEN 'LOG'
    END AS 'Backup Type'
FROM dbo.backupset a
JOIN dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE database_name IN ('Your Database Here') AND type = 'L'
ORDER BY backup_finish_date DESC
Thanks