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

Rate

Share

Share

Rate