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
(SELECTSO.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
FROMdbo.sysindexes i WITH (NOLOCK) INNER JOIN dbo.sysobjects so WITH (NOLOCK)
ON i.id = so.id AND so.type IN ('U')
WHEREindid 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!
-
I was shocked to learn that this table still exists in SQL Server, and nobody is talking about it ^^.
It seems to be around since the 80s as it is also used is SAP ASE, former Sybase...
- Arthur Kirchner wrote:
I was shocked to learn that this table still exists in SQL Server, and nobody is talking about it ^^. It seems to be around since the 80s as it is also used is SAP ASE, former Sybase...
Heh... On that note, you'd probably also be shocked to learn that the job history tables in the MSDB database still use separate columns for Date and Time and that they're still integer based representations of the date and time.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply