Post reply

DataFiles

  • March 17, 2004 at 4:42 am

    #84535

    Hi

    I have a database with two datafiles, located on PRIMARY file group.

    The .mdf has 20gb, and .ndf has 5gb.

    How can i determine which tables are created on .mdf and which on .ndf data file

    thanks

    alex

  • March 17, 2004 at 5:58 am

    #499097

    Hi Alex,

    the sysindexes, sysfiles and sysfilegroups system tables can be queried to get this info...

    SET NOCOUNT ON

    Declare @TabID int

    Declare @TabName sysname

    Create table #TabLocations(TableName sysname,FileGroupName sysname,LogicalFileName nchar(128),FilePath nchar(260))

    Declare TabCUR CURSOR FOR

     Select id,name from sysobjects where xtype = 'u'

    FOR READ ONLY

    OPEN TabCUR

    FETCH NEXT FROM TabCUR INTO @TabID,@TabName

    WHILE @@FETCH_STATUS = 0

    BEGIN

     INSERT #TabLocations(TableName,FileGroupName,LogicalFileName,FilePath)

     select @TabName,sfg.groupname,sf.name,sf.filename

     from sysfilegroups sfg INNER JOIN sysindexes si

     ON

     ( si.id = @TabID and

     si.indid < 2 and

     si.groupid = sfg.groupid)

     INNER JOIN sysfiles sf ON

     ( sf.groupid = sfg.groupid)

     FETCH NEXT FROM TabCUR INTO @TabID,@TabName

    END

    CLOSE TabCUR

    DEALLOCATE TabCUR

    SELECT * FROM #TabLocations

    Drop table #TabLocations

    SET NOCOUNT OFF

    standard disclaimer : since system tables can change between releases and patches its not a good idea to use any queries based on them in production systems....

    HTH

  • March 18, 2004 at 3:30 am

    #499284

    If both your data files are in the same filegroup, then any database object created in that file group may have extents in both files.  Even if you find which file contains the root IAM for an object, there is no reason why any further extent for the object should be in the same file.

    If you want to separate objects into separate files, then the files must be in separate filegroups.

     

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • March 18, 2004 at 6:07 am

    #499296

    thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply