SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database

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

Reference: Pinal Dave (http://www.SQLAuthority.com)

SQL Scripts, SQL Stored Procedure
Previous Post
SQL SERVER – Simple Example of Cursor
Next Post
SQL SERVER – Query Analyzer Shortcuts

Related Posts

277 Comments. Leave new

  • Why do these queries on the same table yield different results:

    –This statement runs very fast
    SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘mytable’) AND indid < 2

    –This one takes a while to execute
    select count(*) from mytable

    Mytable contains over 3millions records.

    Reply
  • I am writing a join. The join is producing more records than desired.

    Is there any way I can see the number of columns of the query returned when I say ‘select * from…’

    Number of columns of the query that was just executed, not of some table that is already there.

    Reply
  • Nice Link

    Reply
  • Hi All,

    How to find the particular row in table?(means suppose one table contain 10 rows , in these 10 rows how to find the particular row(example in 10 rows i want 5 row how)?

    Reply
    • There is no easy way to identify the row number until you order it by a specific column. You can use row_number() function Refer this link

      Reply
  • hi……

    select COUNT(*) from information_schema.columns
    where table_name = ‘Your_Table_Name

    its not working in oracle…………

    Reply
  • Hi, Ravi you can use following code to get the list of tables having 0 records.

    ———————————

    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
    WHERE a.row_count=0
    GROUP BY a.table_name, a.row_count, a.data_size
    DROP TABLE #temp
    —————————-

    Reply
  • Hi Dave,

    I had a requirement like, I want to show the no of rows(count) as column name in temp table.

    Thanks,
    Sathish.

    Reply
  • Hi Sathish,

    if your are using SQL SERVER 2005, then you can use this following syntax

    SELECT *,
    COUNT(*) OVER(PARTITION BY ””) AS TotalRows
    FROM TbaleName

    Thanks,

    Tejas

    Reply
  • Hi
    Pinal

    Thank you Very Much

    Reply
  • Your code to insert multiple rows with one sql statement was useful…

    Reply
  • is there any query to get the column count from a table using sql command

    Reply
  • Thanks Tejas Shah.

    Reply
  • Thanks! This is exactly what I needed!

    Reply
  • hi guys,

    i don’t usually use sql in my job…. but for this one purpose i’ve to use sqlite…

    so can somebody just tell me a simple query to count just the number of columns in a table…

    please hurry.. my time is running out…

    PS. this sql is not that bad as it seems :)

    Reply
  • @Sid

    There are many ways to get this done, here are some of these.

    select Count(*) ‘No Of columns’ from syscolumns where id = object_id(‘example’)

    or

    select count(*) ‘No Of columns’ from information_schema.columns where table_name = ‘example’

    Or

    select Count(*) ‘No Of columns’ from sys.columns where [object_id] = object_id(‘example’)

    Hope this helps,
    IM.

    Reply
  • hi all
    thanks for ur comments….

    Reply
  • Hi Guys
    New to SQL coding so can someone please help? I monitoring our replication and would like to take a row count of a table ‘table1’ and match it to the replication table on our tier 2 and 3 environment .
    SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
    SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
    SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
    SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
    SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table

    But I don’t want to receive and email everytime indication everything is ok. Ideally I would like to receive and email\ alert when the row counts do not match.

    The one table I am looking at sits on 5 different servers. Sorry bit stuck !!

    Also If I wanted to do row counts for mutiple tables accross the servers i.e table1 on all five servers, table 2, table 3 etc.

    How could like be done?

    Thanks in advance for your help!!

    Reply
  • Marek ÅšliwiÅ„ski
    March 4, 2009 3:38 pm

    thx Pinal :) It helps me today to detect that tbXml table in Wsus 3.0 with 160.000 records has 1GB size :OOO

    Makers of WSUS are crazy ;)))

    Thx anyway for simple and handy snippet.

    Regards

    Reply
  • Thanks!! great tip saved me a lot of time

    Reply
  • Hi Pinal,

    Consider there are 5 databases in a sql server.Each database contains n number of tables. A column say ‘EMP id’ is repeated in different tables across the databases.

    My requirement is to write a single query which should return the count of that column in all the databases.

    Can u help me with this

    Reply

Leave a Reply