How to find size of all tables in a given database
In this we are going to return size of each table of a given database, the below sql script determines to check the object exists or not if object exists it drops and recreate with the name: usp_table_size, creating a procedure, using system stored procedure.
After the User stored procedure is created to find size of all tables in a given database we should execute the usp_table_size for this we will create a temporary table and insert values into temporary table and return the output as tabular format.
Step 1: Open SSMS
Step 2: Download the given SQL scripts/code below from links below and paste
/*-----------------------------------------------------------------------------------------------------------------*/
/* Q : How to retrieve the size of all tables in given database */
/* Copyright C - SQLHASH - sqlhash.blogspot.in */
/* Do not change or edit or copy the code */
/*-----------------------------------------------------------------------------------------------------------------*/
USE Master
GO
IF Object_ID('usp_table_size') is not null Print 'usp_table_size Exist -- Dropping the Procedure and created new one'
Else
Print 'usp_table_size Doesn''t Exist -- Created the Procedure'
Go
IF Exists (Select name from sys.objects where name='usp_table_size' and type = 'p')
Drop Procedure usp_table_size
Go
Create Proc usp_table_size @dbname sysname = null
As
Begin
--Set nocount on
Declare @tablesize varchar(255)
IF @dbname IS NULL Select @dbname = DB_NAME()
SELECT @tablesize = 'EXEC ' + @dbname + '..sp_MSforeachtable "sp_spaceused ''?''"'
Exec(@tablesize)
End
/* ------------------------- Execute the procedure usp_table_size ------------------------- */
Drop Table #tablesize
Create table #tablesize
(
name varchar(50),
rows int,
reserved varchar(30),
data varchar(30),
index_size varchar(30),
unused varchar(30)
)
Insert into #tablesize Exec master..usp_table_size
Go
Select name,rows,reserved,data,index_size,unused from #tablesize
(or)
Exec master..usp_table_size <Your DB Name>
/* ------------------------- Execute the procedure usp_table_size ------------------------- */
Step 3: Output: How to find size of all tables in a given database
Step 4: Download the code here
Download text file: How to find size of all tables in a given database
Download SQL file: How to find size of all tables in a given database
In this we are going to return size of each table of a given database, the below sql script determines to check the object exists or not if object exists it drops and recreate with the name: usp_table_size, creating a procedure, using system stored procedure.
After the User stored procedure is created to find size of all tables in a given database we should execute the usp_table_size for this we will create a temporary table and insert values into temporary table and return the output as tabular format.
Step 1: Open SSMS
Step 2: Download the given SQL scripts/code below from links below and paste
/*-----------------------------------------------------------------------------------------------------------------*/
/* Q : How to retrieve the size of all tables in given database */
/* Copyright C - SQLHASH - sqlhash.blogspot.in */
/* Do not change or edit or copy the code */
/*-----------------------------------------------------------------------------------------------------------------*/
USE Master
GO
IF Object_ID('usp_table_size') is not null Print 'usp_table_size Exist -- Dropping the Procedure and created new one'
Else
Print 'usp_table_size Doesn''t Exist -- Created the Procedure'
Go
IF Exists (Select name from sys.objects where name='usp_table_size' and type = 'p')
Drop Procedure usp_table_size
Go
Create Proc usp_table_size @dbname sysname = null
As
Begin
--Set nocount on
Declare @tablesize varchar(255)
IF @dbname IS NULL Select @dbname = DB_NAME()
SELECT @tablesize = 'EXEC ' + @dbname + '..sp_MSforeachtable "sp_spaceused ''?''"'
Exec(@tablesize)
End
/* ------------------------- Execute the procedure usp_table_size ------------------------- */
Drop Table #tablesize
Create table #tablesize
(
name varchar(50),
rows int,
reserved varchar(30),
data varchar(30),
index_size varchar(30),
unused varchar(30)
)
Insert into #tablesize Exec master..usp_table_size
Go
Select name,rows,reserved,data,index_size,unused from #tablesize
(or)
Exec master..usp_table_size <Your DB Name>
/* ------------------------- Execute the procedure usp_table_size ------------------------- */
Step 3: Output: How to find size of all tables in a given database
Step 4: Download the code here
Download text file: How to find size of all tables in a given database
Download SQL file: How to find size of all tables in a given database
This is dummy text. It is not meant to be read. Accordingly, it is difficult to figure out when to end it. But then, this is dummy text. It is not meant to be read. Period.
ConversionConversion EmoticonEmoticon