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)

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

Syntax of SQL

Syntax of SQL statement is as follows...

SELECT * FROM TABLE_NAME

where select is representing the action and * is the required data for which action will be performed.

TABLE_NAME is the object on which action will be performed. It can be a table name, view, stored procedure etc.

SQL statement is not case sensitive.

Some databases has restriction to apply semicolon at the end of each statement, but microsoft access and SQL server do not apply this restriction.

SQL statements can be divided into two parts.

Data Manipulation Language (DML)

- Select

- Update

- Delete

- Insert Into

Data Definition Language (DDL)

- Create Database

- Alter Database

- Create Table

- Alter Table

- Drop Table

- Create Index

- Drop Index

Meanings of SQL

What is SQL ?

- SQL is the abbreviation of structured query language

- SQL is used to access and control/manage databases

- SQL is a standard called ANSI (American National Standards Institute)

What actions SQL may perform in a database?

- SQL may create new tables

- SQL may create stored procedures

- SQL may create views

- SQL may execute query

- SQL may fetch data

- SQL may insert records

- SQL may update records

- SQL may delete records

- SQL may create new databases

- SQL may set permissions on tables, procedures, and views

The base of SQL is RDBMS, which is the abbreviation of "Relational Database Management System" and this is also a base for all modern database systems like MS SQL, MySQL, IBM DB2, Oracle and Microsoft Access.

In RDBMS the storage of data is in database objects that are called tables.

A table in this case is a collections that has related data entries and its structure is made of rows and columns.

Location of Data Files in SQL Server

You may get the physical location of MDF and LDF files by using following query.

select name,physical_name from sys.master_files

You may see other properties of databases i.e. size, maximum size, growth etc by using "select *" on sys.master_files.

SQL Server Assignment Operator (=)

There is only one assignment operator and that is "=". This is used to assign a value to some expression.

DECLARE @AnExpression INT; SET @AnExpression = 87;

SQL Server Arithmetic Operators (%)

(%) modulo operator is used to get the remainder of one number.

Select 47 / 7 AS IntValue, 47 % 7 AS RemValue;

Result: 6, 5

SQL Server Arithmetic Operators (/)

(/) division operator is used to divide two expressions.

Select id, name, someColumn / 10

SQL Server Arithmetic Operators (*)

(*) multiplication operator is used to multiply two operators.

Select 10 * 7

Result = 70

SQL Server Arithmetic Operators (-)

(-) subtraction operator is used to subtract two numbers, also subtracts a number from date as number of days.

DECLARE @someNumber int; SET @someNumber = 191;
SELECT '200' - @someNumber;

Result = 9 

DECLARE @initialdate datetime, @someNumber int;
SET @initialdate = ''June 26, 1982 07:33 AM';
SET @someNumber = 3;
SELECT @initialdate - @someNumber AS 'Final Date';
Result = 1982-06-23 07:33:00.000

SQL Server Arithmetic Operators (+)

(+) addition arithmetic operator is used to add two numbers, also adds a number either in days or in a date.

DECLARE @someNumber int; SET @someNumber = 191;
SELECT '200' + @someNumber;

Result = 391 

DECLARE @initialdate datetime, @someNumber int;
SET @initialdate = ''June 26, 1982 07:33 AM';
SET @someNumber = 3;
SELECT @initialdate + @someNumber AS 'Final Date';
Result = 1982-06-29 07:33:00.000