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

  • Thanks !! It’s greate……………

    Reply
  • Hi All,

    Guys i need to create a reports in SSRs which gives me the comparsion on rowcount of table between 2 different database.

    2 different database has the same number of tables and with the same name.

    How can i get the rowcount of all the tables in both the database.

    Thank in advance
    Lalitha

    Reply
  • Hi Laitha,

    You can write Procedure to get the number of rows from Different Database in same SQL server instance.

    For procedure please go :

    Thanks,

    Tejas

    Reply
  • salam

    if any one help me in topic how we join the different column in same tabel

    Reply
  • How would you do a query that returns the list of all of the column names, data types of each column and max size of each column (ie. varchar(50), varchar(100), etc…)

    Been stuck on this with no success ….

    Thank you !

    Reply
  • SELECT crime.fpo,crime.cdd,crime.cdn,crime.cfn,crime.filedate
    get_field(crime.fpo,crime.cdd,crime.cdn,crime.cfn,crime.filedate) col
    FROM (SELECT DISTINCT fpo,cdd,cdn,cfn,filedate
    FROM crime) crime where id=’MARSCARLCR952059′

    this query doesnt return the output,,plz correct it

    Reply
  • SELECT DISTINCT O.Name, I.Rows
    FROM SysObjects O WITH (NOLOCK) JOIN SysIndexes I WITH (NOLOCK) ON (O.ID = I.ID)
    WHERE O.xType = ‘U’
    AND I.Rows = (SELECT MAX(II.Rows)
    FROM SysObjects OO WITH (NOLOCK) JOIN SysIndexes II WITH (NOLOCK) ON (OO.ID = II.ID)
    WHERE OO.xType = ‘U’)

    Reply
  • Hi,

    I’m a novice to sql scripting. Am trying to figure out a design problem, involving some arithmatic computation.

    I have two tables temp1 and temp2 with two feilds account no., ( Common in both tables ) balance ( float data type ).

    1. I want to compare the balance feilds in temp1 and temp2. print out the no. of accounts and percentage of match and mismatch.
    2. output the a/c nos. whose balances dont match into a seperate table..

    another question, that I have is, how does sql handle the computation, if a value in a particular field is divided by 0 ?

    any help, will get me started.

    thanks

    Reply
  • Moid Muhammad
    July 23, 2009 11:22 pm

    Nice Article:

    >> Garima Wrote

    hi……

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

    its not working in oracle…………

    >> Reply to Garima

    I think dba_tab_columns will help you in achieving what you need.

    Assuming you are using 10g,
    sqlplus / as sysdba
    select count(*) from dba_tab_columns where table_name= ‘YOUR_TABLE_NAME’;

    For example:
    SQL> show user;
    USER is “SYS”
    SQL> select count(*) from dba_tab_columns where table_name=’EMP’ and owner=’SCOTT’;

    COUNT(*)
    ———-
    8

    SQL> select column_name from dba_tab_columns where table_name=’EMP’ and owner=’SCOTT’;

    COLUMN_NAME
    ——————————
    EMPNO
    ENAME
    JOB
    MGR
    HIREDATE
    SAL
    COMM
    DEPTNO

    8 rows selected.

    SQL>

    Reply
  • Hi,

    Can any one help me in writing an SQL Query,

    in a database I having number of tables and out of them I want to have count of the tables that has ’empno’ as one of their column.

    Please reply.
    Thanks & Regards,

    Reply
  • Brian Tkatch
    July 24, 2009 4:58 pm

    @Rao

    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ’empno’;

    Reply
  • Guys ..

    Used the below query …

    Sp_spaceused

    Reply
  • What if I have tables that are the same name but just different schemas. For instance in the database there was schemaA.TableA and schemaB.TableA. So TableA is listed two times in the same database but under different schemas. The spaceused for that query at the top wont work. Is there another query that will get all the tables and schemas space used?

    Reply
  • dear sir pinaldave.
    i have one doubt i have created a table dynamically using my .net project and it has not primary key what i did is i have altered the table i add a IndexID using

    alter table dbo._COMPANYDETAILS ADD IndexID int;

    this query
    but now what i need is the table has 5000 records i have to number it in the IndexID column from 1 to 5000 what is the query for it please reply me as soon as possible to my mail(ksnavinkumar@live.com) please sir…

    Reply
  • @NavinKumar

    ALTER TABLE dbo._COMPANYDETAILS
    ADD IndexID int Identity

    Thats it. this should do the work.

    ~ IM.

    Reply
  • Hi,
    Thanks for such nice query.
    It is very nice idea to check entire database.

    Reply
  • I find the question from OW very relevant and important. Matching tables by their names duplicate the records from tables with same names in different schemas. Can anybody please shed a light to this point?

    Reply
  • Here is my solution using catalog views, which are more flexible than using stored procedures, and considers tables with same name in different schemas:

    USE my_DB
    SELECT s.[name] [schema]
    , t.[name] [table]
    , t.create_date
    , t.modify_date
    , p.rows
    , SUM(a.used_pages) * 8 data_KB
    FROM sys.schemas s
    JOIN sys.tables t
    ON s.[schema_id] = t.[schema_id]
    JOIN sys.partitions p
    ON t.[object_id] = p.[object_id]
    JOIN sys.allocation_units a
    ON a.container_id =p.partition_id
    GROUP BY
    s.[name]
    , t.[name]
    , t.create_date
    , t.modify_date
    , p.rows
    ORDER BY SUM(a.used_pages) DESC
    GO

    Note that the stored procedure sp_spaceused uses dynamic management view (sys.dm_db_partition_stats) instead of catalog view.

    Reply
  • How do you get increment rowcount total in a while loop fowhen you are deleting records from a one table and in the end you want to print the total number of records deleted.

    Reply
  • @Joel

    See if @ROWCOUNT works in your situation.

    Reply

Leave a Reply