DECLARE @DBNAME VARCHAR(250)
DECLARE @CMD VARCHAR(MAX);
CREATE TABLE #OBJECTCOUNTDETAILS
(
INSTANCENAME VARCHAR(50),
DBNAME VARCHAR(50),
OBJECTTYPE VARCHAR(50),
CNT INT
)
DECLARE DBNAMES CURSOR FOR
SELECT NAME FROM SYS.DATABASES
OPEN DBNAMES
FETCH NEXT FROM DBNAMES INTO @DBNAME
WHILE @@FETCH_STATUS=0
BEGIN
SET @CMD='USE '+@DBNAME+CHAR(13)+ 'SELECT @@SERVERNAME,DB_NAME(),TYPE_DESC,COUNT(TYPE_DESC)
FROM SYS.OBJECTS
WHERE IS_MS_SHIPPED=0 GROUP BY TYPE_DESC'
-- PRINT @CMD
INSERT INTO #OBJECTCOUNTDETAILS EXEC(@CMD)
FETCH NEXT FROM DBNAMES INTO @DBNAME
END
CLOSE DBNAMES
DEALLOCATE DBNAMES
SELECT * FROM #OBJECTCOUNTDETAILS
DROP TABLE #OBJECTCOUNTDETAILS
