master.dbo.spt_values means?????????

  • Hello friends!!

    Can anybody tell me 'The meaning of this output'

    I saw 6 columns , anybody tell me what they columns indicate

    SELECT *

    from master.dbo.spt_values

    T.I.A

    Shashank


    Regards,

    Papillon

  • This is an internal helper table used by SQL Server.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Care to enumerate what they all mean??

  • It's not worth talking about. This table is a design nightmare of a "super" lookup table.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Hence .

  • These values are your server run time configuration.  See sp_configure in books on line.  Secondly this information is pretty well documented in most sql admin books.  One hand book you might want to get is "SQL Server 2000 Administrator's Pocket Consultant". 

  • I absolutely agree with Frank. I had to write a script recently to collect information on all the properties of all the Linked Servers on a given server. To collect the information on the 'Server Options' tab, I had to go to this spt_values table to get the description. crazy table

  • Maybe we should make a little life story on this one, and add it up against the super lookup tables articles .

  • SELECT TOP 25 A.name,(SELECT rows FROM dbo.sysindexes s WITH (NOLOCK)

    WHERE s.indid < 2 AND s.id = A.ID )AS [Row count],SpaceUsedMB from

    (SELECT SO.NAME,SO.ID,

    CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low

    FROM master.dbo.spt_values WITH (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) SpaceUsedMB

    FROM dbo.sysindexes i WITH (NOLOCK) INNER JOIN dbo.sysobjects so WITH (NOLOCK)

    ON i.id = so.id AND so.type IN ('U')

    WHERE indid IN (0, 1, 255)

    GROUP BY SO.NAME,SO.ID)

    AS A

    ORDER BY SpaceUsedMB DESC

    Just one of the many uses of spt_values

  • Thanks, this is useful!

Viewing 10 posts - 1 through 10 (of 10 total)

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