我想对系统表进行查询,以查找哪些用户表为空。我们正在使用MS SQL 2008 R2。
感谢您的帮助。
#1 楼
利用sys.tables
和sys.partitions
:select
t.name table_name,
s.name schema_name,
sum(p.rows) total_rows
from
sys.tables t
join sys.schemas s on (t.schema_id = s.schema_id)
join sys.partitions p on (t.object_id = p.object_id)
where p.index_id in (0,1)
group by t.name,s.name
having sum(p.rows) = 0;
使用行总和只是为了确保您不会与分区表混淆。 Index_ID为0或1表示您只查看堆或聚集索引的行数。
#2 楼
正如Mike Fal和Kin都指出的那样,系统表是您的朋友。对于更完整的代码版本,我提出了以下内容,可让您查看总数据数据库中每个表使用的空间。
USE master;
CREATE DATABASE TestDB;
GO
USE tempdb;
ALTER DATABASE TestDB SET RECOVERY SIMPLE;
GO
USE TestDB;
CREATE TABLE Test1 (
Test1ID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, TestData nvarchar(255) CONSTRAINT DF_Test1_TestData DEFAULT (NEWID())
);
GO
TRUNCATE TABLE Test1;
SELECT s.name + '.' + t.name AS TableName,
sum(p.rows) AS TotalRows,
SUM(au.data_pages) AS DataPagesUsed
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.partitions p ON t.object_id = p.object_id
INNER JOIN sys.allocation_units au ON p.hobt_id = au.container_id
WHERE au.type = 1 or au.type = 3
AND t.is_ms_shipped = 0
GROUP BY s.name, t.name
ORDER BY SUM(au.data_pages) DESC;
INSERT INTO Test1 DEFAULT VALUES;
SELECT s.name + '.' + t.name AS TableName,
sum(p.rows) AS TotalRows,
SUM(au.data_pages) AS DataPagesUsed
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.partitions p ON t.object_id = p.object_id
INNER JOIN sys.allocation_units au ON p.hobt_id = au.container_id
WHERE au.type = 1 or au.type = 3
AND t.is_ms_shipped = 0
GROUP BY s.name, t.name
ORDER BY SUM(au.data_pages) DESC;
最后3条语句的结果:
#3 楼
这是PowerShell版本:使用SQL Server管理对象(SMO)
function Find-EmptyTables ($server,$database)
{
# Load SMO assembly
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$s = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $server
$db = $s.Databases.Item($database)
$db.Tables | Where-Object { $_.RowCount -eq 0 } | Select Schema, Name, RowCount
}
取决于数据库的数量,您可以使用上面的函数处理填充在变量中的每个数据库名称的列表,如果与一台服务器一起处理,则同时输出所有数据库名称:
$DBList = 'MyDatabase1','MyDatabase2'
foreach ($d in $DBList) {
Find-EmptyTables -server MyServer -database $d |
Select @{Label="Database";Expression={$d}}, Schema, Name, RowCount
}
#4 楼
此处的其他响应都很好,但是为了完整起见:SQL Server Management Studio>右键单击DB>报告>标准报告>按表的磁盘使用情况#5 楼
我通常只创建一个查询,该查询创建所需的查询,然后手动执行该查询,但是如果您想一次执行所有操作,则只需...declare @sql nvarchar(max) ;
set @sql = ';with cte as (' + (select
(
SELECT case when row_number()
over (order by table_schema, table_name) = 1 then ' '
else ' union ' end +
'select count(*) rws, ''[' +
t.TABLE_SCHEMA +'].[' + t.table_name +
']'' tbl from ' + '['+
t.TABLE_SCHEMA + '].[' + TABLE_NAME + ']' +
CHAR(10) AS [data()]
FROM INFORMATION_SCHEMA.TABLES t
FOR XML PATH ('')
)) + ') select * from cte where rws = 0;'
execute sp_executesql @sql;
#6 楼
另外一个答案是,未记录的系统存储过程sp_MSforeachtable
在这里很有用。CREATE TABLE #CountRows ( TableName nvarchar(260), NumRows int) ;
GO
EXEC sp_MSforeachtable 'insert into #CountRows select ''?'', count(*) from ?' ;
SELECT * FROM #CountRows WHERE NumRows = 0 ORDER BY TableName ;
DROP TABLE #CountRows ;
适用于未记录功能的常规警告。
如果您好奇或希望确定它没有讨厌的副作用,则可以在master中查看该过程的源代码。它使用动态SQL来构建游标,这会降低性能(cursor = slow!),因此仅将此过程用于一次性任务。
此外,
sp_MSforeachtable
在Azure数据库中不可用。#7 楼
DECLARE @toCheck INT;
DECLARE @countoftables INT;
DECLARE @Qry NVARCHAR(100);
DECLARE @name VARCHAR(100);
BEGIN
IF object_id('TEMPDB.DBO.#temp') IS NOT NULL drop table #temp;
SELECT ROW_NUMBER() OVER(ORDER BY name) AS ROW,CountStatement = 'SELECT @toCheck = COUNT(*) FROM ' + name,name INTO #temp FROM SYS.TABLES WITH (NOLOCK)
--SELECT * FROM #temp ORDER BY ROW
SET @countoftables =(SELECT COUNT(*) FROM #temp)
WHILE (@countoftables > 0)
BEGIN
SET @Qry = (SELECT CountStatement FROM #temp WITH (NOLOCK) WHERE ROW = @countoftables);
SET @name = (SELECT name FROM #temp WITH (NOLOCK) WHERE ROW = @countoftables);
EXEC SP_EXECUTESQL @qry,N'@toCheck INT OUTPUT',@toCheck OUTPUT;
IF(@toCheck=0)
BEGIN
PRINT 'Table: ' + @name + ', count: ' + convert(varchar(10),@toCheck);
END
--ELSE
-- BEGIN
-- PRINT 'Table: ' + @name + ', count: ' + convert(varchar(10),@toCheck);
-- END
SET @countoftables = @countoftables -1;
END
END
#8 楼
SELECT T.name [Table Name],i.Rows [Number Of Rows]
FROM sys.tables T
JOIN sys.sysindexes I ON T.OBJECT_ID = I.ID
WHERE indid IN (0,1) AND i.Rows<1
ORDER BY i.Rows DESC,T.name
评论
我认为这不会改善现有的答案
–詹姆斯·安德森(James Anderson)
16年7月11日在7:34
评论
但是,这将返回所有表,而不仅仅是返回空表。我认为您不能对这些报告应用过滤器。
–user507
13年8月8日在2:08
那是真实的。垫垫垫
–onupdatecascade
13年8月13日在2:34
但这是一个很好的答案。只需2次点击,比以往更容易。
–玛丽安
13年8月13日在20:43
但是,您可以按记录数排序列表
–罗伯特·迈克斯(Robert Mikes)
16-11-27在23:11