Get DB Table Details Quickly

/*********************************************************************************//*Procedure Name: sp_table_info**//*Author Name: Babou Srinivasan**//*Purpose: Getting Table Information from a Database**//*Written Date: 25-July-2002**//*Usage: Run this procedure in master database to 
  access from any other database
  EXEC sp_table_info
  EXEC sp_table_info 'authors'
  will give the table details like 
  no. of rows and space used by table**//*********************************************************************************/CREATE PROC sp_table_info ( @object_name sysname = NULL )
AS
BEGIN
SET NOCOUNT ON

/*Create temp tables before any DML to ensure dynamic
**  We need to create a temp table to do the calculation.
**  reserved: sum(reserved) where indid in (0, 1, 255)
**  data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
**  indexp: sum(used) where indid in (0, 1, 255) - data
**  unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/CREATE TABLE #spt_space
(
idINT NULL,
rowsINT NULL,
reservedDEC(15) NULL,
dataDEC(15) NULL,
indexpDEC(15) NULL,
unusedDEC(15) NULL
)

IF @object_name IS NULL
/*
**  Now calculate the summary data.
**  reserved: sum(reserved) where indid in (0, 1, 255)
*/INSERT INTO #spt_space (id,reserved)
SELECTo.id , SUM(i.reserved)
FROM SYSINDEXES i (NOLOCK) , SYSOBJECTS o (NOLOCK)
WHERE indid in (0, 1, 255)
AND i.id = o.id
ANDo.type= 'U'
GROUP BY o.id
ELSE
INSERT INTO #spt_space (id,reserved)
SELECTi.id , SUM(i.reserved)
FROM SYSINDEXES i (NOLOCK)
WHERE indid in (0, 1, 255)
AND i.id = OBJECT_ID(@object_name)
GROUP BY i.id

/*
** data: sum(dpages) where indid < 2
**+ sum(used) where indid = 255 (text)
*/update t1
set t1.data = ((SELECT SUM(t2.dpages)
FROM SYSINDEXES t2(NOLOCK)
WHERE t2.indid < 2
AND t2.id = t1.id
       ) +
      ( SELECT ISNULL(SUM(t3.used), 0)
FROM SYSINDEXES t3(NOLOCK)
WHERE t3.indid = 255
AND t3.id  = t1.id
      ))
FROM #spt_space t1


/* index: sum(used) where indid in (0, 1, 255) - data */UPDATE t1
SET t1.indexp = (SELECT SUM(t2.used)
FROM SYSINDEXES t2 (NOLOCK)
WHERE t2.indid IN (0, 1, 255)
AND t2.id = t1.id
     )- data
FROM #spt_space t1

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */update t1
SET t1.unused = t1.reserved- (SELECT SUM(t2.used)
FROM SYSINDEXES  t2 (NOLOCK)
WHEREt2.indid IN (0, 1, 255)
AND t2.id = t1.id
  )
FROM #spt_space t1

UPDATE t1
SET t1.rows = i.rows
FROM #spt_space t1 , SYSINDEXES i (NOLOCK)
WHERE i.indid < 2
AND i.id = t1.id

SELECT name = SUBSTRING(OBJECT_NAME(t1.id), 1, 60),
rows = t1.rows,
reserved = LTRIM(STR(t1.reserved * d.low / 1024.,15,0) +' ' + 'KB'),
data = LTRIM(STR(t1.data * d.low / 1024.,15,0) +' ' + 'KB'),
index_size = LTRIM(STR(t1.indexp * d.low / 1024.,15,0) +' ' + 'KB'),
unused = LTRIM(STR(t1.unused * d.low / 1024.,15,0) +' ' + 'KB')
FROM #spt_space t1 , master.dbo.spt_values d
WHERE d.number = 1
AND d.type = 'E'
ORDER BY rows DESC

SET NOCOUNT OFF
END