DECLARE @DatabaseObjects TABLE
(
DatabaseName
sysname,
ObjectType sysname,
ObjectName sysname
);
DECLARE
@DatabaseName sysname,
@SQLStatement
varchar(max),
@ExecuteStatement varchar(max),
@Columns
varchar(max);
SET NOCOUNT ON;
SET @SQLStatement = 'SELECT
''{DatabaseName}'' AS [DatabaseName], [type_desc] AS [ObjectType], [name] AS
[ObjectName] FROM
[{DatabaseName}].[sys].[objects];';
DECLARE
DatabaseCrawler
CURSOR
FOR SELECT Name FROM sys.Databases ORDER BY Name ASC;
OPEN
DatabaseCrawler;
FETCH NEXT FROM DatabaseCrawler INTO @DatabaseName;
WHILE
@@FETCH_STATUS = 0
BEGIN
SET @ExecuteStatement = REPLACE(@SQLStatement,
'{DatabaseName}', @DatabaseName);
INSERT INTO @DatabaseObjects
EXEC
(@ExecuteStatement);
FETCH NEXT FROM DatabaseCrawler INTO
@DatabaseName;
END;
CLOSE DatabaseCrawler;
DEALLOCATE
DatabaseCrawler;
SET NOCOUNT OFF;
SELECT * INTO #DatabaseObjects FROM
@DatabaseObjects
SELECT @Columns =
ISNULL(@Columns, '') + '[' + [Column] + '],' FROM (SELECT DISTINCT ObjectType AS
[Column] FROM @DatabaseObjects) Columns
SET @Columns = LEFT(@Columns,
LEN(@Columns) - 1)
SET
@SQLStatement = 'SELECT DatabaseName, {Columns} FROM #DatabaseObjects PIVOT
(Count(ObjectName) FOR ObjectType IN ({Columns})) AS pvt'
SET
@ExecuteStatement = REPLACE(@SQLStatement, '{Columns}',
@Columns);
EXEC (@ExecuteStatement)
DROP TABLE
#DatabaseObjects