How To Get/Retrieve Size Of Tables In SQL Server Databases

How To Get/Retrieve Size Of Tables In SQL Server Databases

Sometime we wonder why an application database grew so heavy during the short period of development and testing process. Here most of us wanted to know what are the actual stats of used space by each table and if we need this information without the use of Management Studio's table properties dialog, then follow this...

In fact SQL Server gives us everything we need with a Stored Procedure that is called sp_spaceused. But this SP does not support iterating over the tables in some database, so we needed to leverage another Stored Procedure sp_msForEachTable, but this is undocumented.

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- Get Total DB size.

EXEC sp_spaceused

-- Table row counts and their sizes.

CREATE TABLE #tempTBL

(

[nameOfTable] NVARCHAR(128),

[rowsInTable] CHAR(11),

reserved_size VARCHAR(18),

data_size VARCHAR(18),

index_size VARCHAR(18),

unused_size VARCHAR(18)

)

-- Insert into temp table

INSERT #tempTBL EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

-- Show it to user

SELECT * FROM #tempTBL

-- Number of rows

SELECT SUM(CAST([rowsInTable] AS int)) AS [rowsInTable] FROM #tempTBL

-- Drop the temp table

DROP TABLE #tempTBL

.

Update Statement in SQL

This is used when you want to update records in database table.

This will update column1 and column2 in a table for all rows/records.

Update Table_Name set column1 = value1, column2 = value2


This will update column1 and column2 in a table for the records that are filtered by using the criteria in WHERE clause.


Update Table_Name set column1 = value1, column2 = value2 Where columnX = valueX

Insert Into statement in SQL

This is used when you want to insert new record into database table.

You may write this statement in two different styles.

INSERT INTO my_table VALUES (value1, value2,...)
This is used when you don't want to specify column names during insert operation. so you have to specify the values for columns in same sequence as they appear in database table.


INSERT INTO my_table(column1,column4) VALUES (value1, value4)
This is used when you exactly want to specify column names for which values will be inserted during insert operation.

Order By Keywork in SQL

The keyword "order by" is used to sort the result set.

This sorts a specified column and by default it sorts in ascending order but you may specify the word "Desc" to get result set in descending order.

For ascending sort:

Select column(s) From Table_Name order by column1

Select column(s) From Table_Name order by column1 asc

For descending sort:

Select column(s) From Table_Name order by column1 desc

AND & OR Operators in SQL

In where clause you may filter records but if you want to filter on the basis of more than one condition then you may use "AND" or "OR" operators.

The AND & OR Operators

The AND operator displays a record only when the first condition and the second condition both are true
The OR operator displays a record only if the first condition or the second condition is true

Select column(s) From Table_Name where column1 = value1 AND column2 = value2

Select column(s) From Table_Name where column1 = value1 OR column2 = value2

You may also use the combination of both operators.

Select column(s) From Table_Name where (column1 = value1) AND (column2 = value2 OR column3 = value3)

Where clause in SQL

This "where" clause is used to filter results.

When you want to select, update or delete records, you may use where clause to filter records on which operation will be performed. It is to filter result set on the basis of some criteria, or you may say that it will fetch only those records that fulfill criteria.

Select column(s) From Table_Name 

     Where column = value

"=" can be replaced with any of the following operators.

=	Equal
<> or !=	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	If you know the exact value you want to return for at least one of the columns

Select statement in SQL

Select statement is used in SQL to select data from database objects. You will get a result table on the execution of select statement, which is called result-set.

Select column(s) From Table_Name

or 

Select * From Table_Name

First will return only specified columns and second will return all columns in a table. 

select and SELECT are same as SQL is not case sensitive.

Select Distinct: 

--------------

Some columns may have duplicate data, select statement returns all occurrences of data whereas select distinct is used when you want to avoid duplicate records/data in result set.

This is applied on column for which you want to filter duplicate records.

Select Distinct column(s) From Table_Name