sql: 无用表清理

本文主要是介绍sql: 无用表清理，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

SELECT
s.Name AS SchemaName,​
t.Name AS TableName,​
p.rows AS RowCounts,​
DT.UID_DialogTable,​
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,​
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,​
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB​,
'exec sp_rename '''+t.name+''',''TASK_'+t.name+''';' rename_SQL,
'exec sp_rename ''TASK_'+t.name+''','''+t.name+''';' rollback_SQL,
'drop table TASK_'+t.name+';' dropSQL
--'exec sp_rename '''+t.name+''','''+substring(t.name,10,100)+''';' rollback_SQL,
--'drop table '+t.name+';' dropSQL
FROM sys.tables t​
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id​
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id​
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id​
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id​
left Join DialogTable as DT on DT.TableName = t.Name​​
where DT.TableName is null​
and t.name not in (
-- can not delete
'QBMDBQueueOverview_fix','QBMDBQueueSlot_fix','QBMDBQueueTaskPerf_fix', 

--high risk
'_dbqueueprocessingstatus','CCC_MKO_LatencyHistory','DeletedMailboxUpdates','MR_AS_Test_AccProductTS','MR_DialogHlpCollAfterDirectOBJ','MR_DialogHlpCollAfterIndOBJ','MR_DialogHlpCollBeforeTotalOBJ','MR_DialogHlpDBSchedulerParam','MRPermissionSPforCS'
)  
GROUP BY t.Name, s.Name, p.Rows, DT.UID_DialogTable​
ORDER BY s.Name, t.Name;

这篇关于sql: 无用表清理的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！