Retrieve Table Information in MS SQL Server

Quite often there is a need to check number of records in the table. It can be done with ease with the following SQL statement

Select Count(*) from TableName

However, it would have been better if we can retrieve number of records from the table with other information related to that table. SQL Server provides this store procedure to accomplish this task

sp_spaceused 'Table_Name'

more detail inside this book

The statement above shall return result in the table format. This SQL statement only works with one table in the chosen database. If we want to loop through all tables in the database to display table information, we can perform this step.

• First, create the temporary table to store result from store procedure
• Second, run the store procedure
• Last, use the select statement to display the result

Use the below SQL statement to create the temporary table in SQL Server

CREATE TABLE #tableSize(
TAB_NAME NVARCHAR(128),
RowsNO VARCHAR(30),
sizereserved VARCHAR(30),
sizedata VARCHAR(30),
index_size VARCHAR(30),
unused VARCHAR(30) )

Then, execute the store procedure below

INSERT #tableSize EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

After the the procedure finish running, simply run the select statement.

SELECT * FROM #tableSize order by TAB_NAME

Find more books related to MS SQL Server