About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Friday, April 8, 2011

CreateVwObjectFileGroup

This proc creates a server-wide view that shows the file group of a given object.  Note the ObjectID pertains to that particular database.  This can be useful for managing space and storage requirements.

Later this month I’ll post the code for a view (vwTable) which will use vwObjectFileGroup and other views previously posted to show a server-wide glimpse of tables with number of rows, bytes, file group, and number of partitions.


USE Admin
GO

IF OBJECT_ID('dbo.CreateVwObjectFileGroup') Is Not Null
      DROP PROC dbo.CreateVwObjectFileGroup
GO

CREATE PROC dbo.CreateVwObjectFileGroup
      @ShowSql bit = 0
AS
/*    DATE        AUTHOR            REMARKS
      8/12/10           PPaiva            Intial creation.
      9/1/10            PPaiva            Added column FileGroupID.

      DESCRIPTION
            Creates a view in the Admin database called vwObjectFileGroup.
            Coded to use Convert(varchar, ServerProperty('ServerName'))
                  rather than @@ServerName since the latter can be erroneous.

      USAGE
            Exec Admin..CreateVwObjectFileGroup
            Exec Admin..CreateVwObjectFileGroup 1

      DEBUG
            SELECT *
            FROM vwObjectFileGroup

*/
SET NOCOUNT ON

DECLARE @s varchar(max),
            @DB varchar(100),
            @MaxDB varchar(100),
            @i int,
            @sNow varchar(16)

SET @sNow = Convert(varchar(16), GetDate(), 120)

SET @s = 'IF Object_ID(''dbo.vwObjectFileGroup'') Is Not Null
      DROP VIEW dbo.vwObjectFileGroup'

IF @ShowSql = 1
      Print @s
Exec(@s)
     
Print ''


SET @s = 'CREATE VIEW dbo.vwObjectFileGroup
AS
/*    DATE        AUTHOR            REMARKS
      ' + Convert(varchar(10), GetDate(), 01) + '     PPaiva            Intial creation.

      DESCRIPTION
            Provides a list of file group per object_id
                  on this server.

      SELECT *
      FROM Admin..vwObjectFileGroup

*/
'

SELECT  name
INTO #FileGroupDBs
FROM sys.databases
WHERE state_desc = 'ONLINE'


SELECT  @MaxDB = Max(Name),
            @DB = '',
            @i = 0
FROM #FileGroupDBs


WHILE @DB < @MaxDB
      BEGIN
            SET @i = @i + 1

            SELECT @DB = Min(Name)
            FROM #FileGroupDBs
            WHERE Name > @DB


            SET @s = @s + '  
      SELECT  Convert(varchar, ServerProperty(''ServerName'')) as Server,
            ''' + @DB + ''' as DB,
            i.id ObjectID,
            fg.groupname COLLATE SQL_Latin1_General_CP1_CI_AS FileGroup,
            fg.GroupID FileGroupID,
      ''' + @sNow + ''' ViewCreateDate
      FROM [' + @DB + '].sys.sysfilegroups fg
      JOIN [' + @DB + '].sys.sysindexes i
            ON  i.groupid = fg.groupid
            AND i.indid < 2
'
            IF @DB <> @MaxDB
                  SET @s = @s + 'UNION ALL '


      END




IF @ShowSql = 1
      BEGIN
            SELECT *
            FROM #FileGroupDBs
            ORDER BY Name

            Print @s
      END

           
Exec (@s)


No comments:

Post a Comment