Get DB Table Details Quickly
Get All the Table Details in a Database in a Second By Running sp_table_info. This procedure will give the No. of row , reserved space , data space , index space and Unused space. Compile the Procedure in Master Database and access it from any other database. We can use it for a single table also by supplying table name as parameter.
sp_table_info 'authors'
/*********************************************************************************/
/* 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
(
id INT NULL,
rows INT NULL,
reserved DEC(15) NULL,
data DEC(15) NULL,
indexp DEC(15) NULL,
unused DEC(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)
SELECT o.id , SUM(i.reserved)
FROM SYSINDEXES i (NOLOCK) , SYSOBJECTS o (NOLOCK)
WHERE indid in (0, 1, 255)
AND i.id = o.id
AND o.type = 'U'
GROUP BY o.id
ELSE
INSERT INTO #spt_space (id,reserved)
SELECT i.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)
WHERE t2.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
