Count of Objects by Database on a Server

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Abhilash T Mr or Mrs. 500 Points: 516 More actions · Answer 1

There should be little change in the script

DECLARE @databases as table (DbName nvarchar(100), DbSize bigint, Remarks nvarchar(100))

DECLARE @results as table (DbName nvarchar(100), DbSize bigint, ObjectCount bigint)

DECLARE @dbname as nvarchar(100),

@dbsize as bigint,

@remarks as nvarchar(100),

@sql as nvarchar(max)

INSERT INTO @databases EXEC sp_databases

SELECT TOP 1 @dbname = DBName, @dbsize = DBSize FROM @databases

WHILE EXISTS (SELECT TOP 1 * FROM @databases)

BEGIN

SET @sql =

' SELECT ''' + @dbname + ''', ''' +

CAST(@dbsize as nvarchar) + ''', COUNT(1) FROM [' + @dbname +

'].[sys].[objects]'

PRINT @sql

INSERT INTO @results EXEC(@SQL)

DELETE FROM @databases WHERE dbname = @dbname

SELECT TOP 1 @dbname = DBName, @dbsize = DBSize FROM @databases

END

SELECT * FROM @results ORDER BY ObjectCount desc

Thanks

Abhilash

david.ashby SSC Journeyman Points: 79 More actions · Answer 2

My first thought was to try to use sp_foreachdb

CREATE TABLE #databases(DbName SYSNAME, DbSize BIGINT, Remarks NVARCHAR(100))

INSERT INTO #databases EXEC SP_DATABASES

ALTER TABLE #databases ADD ObjectCount INT

EXEC SP_MSFOREACHDB

'USE [?] ;

UPDATE #databases SET ObjectCount = (SELECT COUNT(*) FROM [sys].[objects])

WHERE DBName = "?"'

SELECT * FROM #databases ORDER BY ObjectCount DESC

DROP TABLE #databases

Regards,

David

jkennedy 33173 SSC Rookie Points: 32 More actions · Answer 3

Thanks David! That serves the same purpose in a cleaner, more efficient manner.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

SSC Guru

Points: 76202

May 10, 2016 at 6:45 am

Thanks for the script.