Post reply

How to know the SQL objects allocations among the primary and secondary files of a particular database

  • July 10, 2012 at 1:20 pm

    #392713

    I have 7 files created with restricted growth for temp DB. Is there a way that I can see where all the sql objects like FUNCTION, INDEX, PACKAGE, PROCEDURE, SYNONYM, TABLE, TRIGGER are been arranged on these 7 files?

    Any script or anything? I just wanna see how this database is designed.

  • July 10, 2012 at 1:46 pm

    #1511184

    i think joining sys.partitions gives me which partitions the tables or the indexes reside;

    most of the other objects (procs, views, functions triggers are just compiled code, and their definitions exist in sys.objects( which must exist in the PRIMARY partition, right?) , but are not materialized into a partition.

    select objz.name as ObjectName,

    objz.type_desc as ObjectType,

    partition_number

    from sys.partitions partz

    inner join sys.objects objz on partz.object_id = objz.object_id

    SELECT idxz.name As IndexName,

    idxz.type_desc as ObjectType,

    partition_number

    from sys.partitions partz

    inner join sys.indexes idxz on partz.index_id = idxz.index_id

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 10, 2012 at 2:01 pm

    #1511192

    TempDB isn't likely to have functions, procedures or synonyms. As for tables and indexes, they'll be spread across all the files using proportional fill since TempDB can only ever gave one filegroup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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