SQL – List All Tables, Space Usage Info & Other Details

One of the OP requested in the forum to find all table details of any database. The details which includes Number of Rows,table size, index size etc:-. Even I frequently writing queries to list all such details.

I’ve used SQLCMD mode to connect to the remote server and database to fetch all the required details.

SQLCMD can be enabled in Query Editor.

Go to SSMS Menu -> Query ->Select SQLCMD.

 Download the script :- SQL-List-All-Tables-Information

Chane the Servername and Databasename in the below script

:CONNECT <SERVERNAME> 
:SETVAR DB <DATABASENAME> 
 
USE $(DB) 
 
create table #TableSize ( 
    Name varchar(255), 
    [rowsint, 
    reserved varchar(255), 
    data varchar(255), 
    index_size varchar(255), 
    unused varchar(255)) 
create table #ConvertedSizes ( 
    Name varchar(255), 
    [rowsint, 
    reservedKb int, 
    dataKb int, 
    reservedIndexSize int, 
    reservedUnused int) 
 
EXEC sp_MSforeachtable @command1="insert into #TableSize 
EXEC sp_spaceused '?'insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused) 
select name, [rows],  
SUBSTRING(reserved0LEN(reserved)-2),  
SUBSTRING(data0LEN(data)-2),  
SUBSTRING(index_size0LEN(index_size)-2),  
SUBSTRING(unused0LEN(unused)-2from #TableSize 
 
select * from #ConvertedSizes 
order by reservedKb desc 
 
drop table #TableSize 
drop table #ConvertedSizes
Output:-

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in Uncategorized and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s