Count of Objects by Database on a Server

• jkennedy 33173

  SSC Rookie

  Points: 32

  More actions

  July 25, 2012 at 3:56 pm

  #261618

  Comments posted to this topic are about the item Count of Objects by Database on a Server

• Abhilash T

  Mr or Mrs. 500

  Points: 516

  More actions

  August 1, 2012 at 5:24 am

  #1520185

  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

  August 7, 2012 at 9:06 am

  #1522662

  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

  August 7, 2012 at 9:21 am

  #1522673

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

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 10, 2016 at 6:45 am

  #1877739

  Thanks for the script.