query to get the list tables in a database with total number of records and record size

Question

query to get the list tables in a database with total number of records and record size

Joy Smith San

SSC-Insane

Points: 24877
More actions
June 7, 2010 at 4:43 am

#143569

Dear All,
Can anybody help me with a query to get the list tables in a database with total number of records and record size pls?
Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

This code is not mine, not sure where it came from but I used it to do the same thing.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllTableSizes]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[GetAllTableSizes]

GO

CREATE PROCEDURE [dbo].[GetAllTableSizes]

AS

/*

Obtains spaced used data for ALL user tables in the database

*/

DECLARE @TableName VARCHAR(100) --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing

DECLARE tableCursor CURSOR

FOR

select [name]

from dbo.sysobjects

where OBJECTPROPERTY(id, N'IsUserTable') = 1

FOR READ ONLY

--A procedure level temp table to store the results

CREATE TABLE #TempTable

(

tableName varchar(100),

numberofRows varchar(100),

reservedSize varchar(50),

dataSize varchar(50),

indexSize varchar(50),

unusedSize varchar(50)

)

--Open the cursor

OPEN tableCursor

--Get the first table name from the cursor

FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch

WHILE (@@Fetch_Status >= 0)

BEGIN

--Dump the results of the sp_spaceused query to the temp table

INSERT #TempTable

EXEC sp_spaceused @TableName

--Get the next table name

FETCH NEXT FROM tableCursor INTO @TableName

END

--Get rid of the cursor

CLOSE tableCursor

DEALLOCATE tableCursor

--Select all records so we can use the reults

SELECT *

FROM #TempTable

--Final cleanup!

DROP TABLE #TempTable

GO

EXEC GetAllTableSizes

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

CREATE TABLE #TableSpaceUsed

(Name nvarchar(128),

Rows char(11),

reserved varchar(18),

data varchar(18),

index_Size varchar(18),

unused varchar(18))

insert into #TableSpaceUsed

exec sp_msforeachtable 'sp_spaceused [?]'

select * from #TableSpaceUsed

order by name

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

I show a couple methods to do this in a set based fashion on my blog.

You can read about them here.

http://jasonbrimhall.info/2010/05/25/space-used/

From this article there are links to other articles. Check them out.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events