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

USE $(DB) 
create table #TableSize ( 
    Name varchar(255), 
    reserved varchar(255), 
    data varchar(255), 
    index_size varchar(255), 
    unused varchar(255)) 
create table #ConvertedSizes ( 
    Name varchar(255), 
    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(unused0LEN(unused)-2from #TableSize 
select * from #ConvertedSizes 
order by reservedKb desc 
drop table #TableSize 
drop table #ConvertedSizes


About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ http://codingsight.com/author/prashanthjayaram/ https://www.red-gate.com/simple-talk/author/prashanthjayaram/ http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ Connect Me: Twitter @prashantjayaram GMAIL powershellsql@gmail.com The articles are published in: 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 )

Facebook photo

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

Connecting to %s