Tag: conteggio

Conteggio di tutte le tabelle

Ecco una query veloce per il conteggio di tutte le tabelle di un database, senza utilizzare cursori:

DECLARE @u TABLE (id INT, [schema] VARCHAR(200), name VARCHAR(1000))
INSERT INTO @u
SELECT ROW_NUMBER() OVER (ORDER BY s.name, t.name), s.name, t.name
FROM sys.tables t
	INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

DECLARE @t TABLE (name VARCHAR(1000), c INT)

WHILE (SELECT COUNT(*) FROM @u) > 0
BEGIN
	DECLARE @n VARCHAR(1000), @s VARCHAR(200)
	SELECT TOP 1 @s = QUOTENAME([schema]), @n = QUOTENAME(name) FROM @u
	INSERT INTO @t EXEC ('SELECT ''' + @s + '.' + @n + ''', COUNT(*) FROM ' + @s + '.' + @n)
	DELETE FROM @u WHERE id = (SELECT TOP 1 id FROM @u)
END
SELECT * FROM @t