7/16/12

Ok, just a sample PIVOT

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

No comments:

Post a Comment