Use the command line to get SQL database table size and row count

You can use the osql or sqlcmd utilities to determine a database table size and row count.

1. Open a command prompt as an administrator.
2. Use one of the following commands:
   • To connect to the osql Utility using the current user, type
     osql-S servernameorip\instancename -E
   • To connect to the osql Utility using another user, type
     osql -S servernameorip\instancename -U username -P password
   • To connect to the osql Utility using the current user, type
     sqlcmd -S servernameorip\instancename -E
   • To connect to the osql Utility using another user, type
     sqlcmd -S servernameorip\instancename -U username -P password
   Where servernameorip is the database server name or IP address, instancename is the name of the SQL instance, username is the name of the SQL user, and password is the password for the SQL user.
3. Type the following script:
USE databasename
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

Where databasename is the name of the database.