Finding total size of the database in sql server

sp_spaceused Returns information about the total size of the current database.

Syntax:

Exec sp_spaceused

Finding total Size/Space of the Table in sql server

sp_spaceused 'MyTable' Returns information about the size of MyTable

Syntax:

Exec sp_spaceused 'TableName'

Query:

Exec sp_spaceused 'MyOrdersPrimary'

Finding Size of ALL TABLES in the Current Database

List the space used in all tables within the currently connected database.

It takes all the tables from the currently connected database and displays the size of the table in the result window.

exec sp_msforeachtable "sp_spaceused '?'"

Finding Data Size of column in Bytes in SQL SERVER

Returns the number of bytes used to represent any expression.

Here the “datalength “ funcion helps to find the size of the data in the column. For example data in the column is “ARUN” then it takes 4 bytes, because one charater takes one byte, likewise you can calculate for all datatypes.

-- Created table with Primary Key
CREATE TABLE MyOrdersPrimary
(
    OrderId int PRIMARY KEY NOT NULL,
    ProductName varchar(20)
);

-- Inserted some records
insert into MyOrdersPrimary values (1,'Samsung')
insert into MyOrdersPrimary values (2,'Nokia')
select datalength(ProductName) as Bytes, * from MyOrdersPrimary

Finding the Size of INDEX on Specific TABLE in SQL SERVER

use Northwind

SELECT object_name(object_id) as TableName, *

FROM sys.indexes

--Syntax

exec sp_spaceused 'TableName'

-- Check the size of index

exec sp_spaceused 'Employees'

Finding the Size of INDEX on ALL TABLE in the current Database in SQL SERVER

Gives the list of index in the specified database.

use Northwind

SELECT object_name(object_id) as TableName, *

FROM sys.indexes

-- Get the indexes from all the table in the current database

exec sp_msforeachtable "sp_spaceused '?'"

Getting Size of ALL Databases in the Current server

Use Master
Go
EXECUTE sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'