How to count empty tables in database?
Is there any way to count tables with no rows in my database with using T-SQL
st开发者_如何学Goatement?
There you go... using a derived table.
SELECT * FROM
(
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
) sub
WHERE sub.[RowCount] = 0
I use the following:
SELECT t.NAME AS TableName, sum(p.rows) as RowCounts
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
WHERE
i.name IS NULL AND i.index_id <= 1
GROUP BY
t.NAME, i.object_id, i.index_id, i.name
HAVING SUM(p.rows) = 0
from khtan @ SQL Server Forums, this is used to drop all empty tables, maybe you could adapt it to output a count?
declare @name varchar(128), @sql nvarchar(2000), @i int
select @name = ''
while @name < (select max(name) from sysobjects where xtype = 'U')
begin
select @name = min(name) from sysobjects where xtype = 'U' and name > @name
select @sql = 'select @i = count(*) from [' + @name + ']'
exec sp_executesql @sql, N'@i int out', @i out
if @i = 0
begin
select @sql = 'drop table [' + @name + ']'
print @sql
-- unmask next to drop the table
-- exec (@sql)
end
end
I don't have SQLServer here but I could take a stab at it if you like.
精彩评论