page contents

About the Post

Author Information

PowerShell and TSQL code to list database information

Let’s say you want to get a list of all the tables in your database, along with their row counts and data space used. This would be really nice to know if you could not get it from SQL Server Management Studio. (SSMS) Well, today you are in luck, because I am going to show you how in both Microsoft SQL and PowerShell. (I love this picture below)

First, you will need to open Powershell for SQL and the sqlps utility that is used to run Windows PowerShell sessions that include the SQL Server snap-ins. (http://msdn.microsoft.com/en-us/library/cc281962.aspx)

After you open it, you will then navigate down to your database tables (as seen below) and once you are in the tables directory of the database that you want to gather this information, then type this command listed below in bold :

Dir | FT Name, RowCount, DataSpaceUsed, IndexSpaceUsed 

If you do not want to read it in the command prompt, then do output it to a text files, like I did here:

Here are the results

You can also get this information from an undocumented stored procedure (Thanks to that tip from the good folks at Ask SQLserverCentral: http://ask.sqlservercentral.com)

EXEC sp_MSforeachtable ‘EXEC sp_spaceused ”?”’

Here are the results:

Enjoy 🙂

Tags: , ,

Comments are closed.

Copy Protected by Chetan's WP-Copyprotect.