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)