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

  • how to get number of row intable and use it in gui application

    Reply
  • All,
    Please can anyone help me out for the way out for writing a procedure for searching numbers in all tables in a database having multiple schemas.
    Hoping that you all champs can help me out as I am new bee.

    Rgds,
    BR..

    Reply
    • Follow this post. It will help you in getting what you want. But you need to change the code little bit. Let me know if you find any difficulties

      Reply
  • Hi All,

    In a table A, there are 3 columns and in 500 records like below

    Table A
    a1 a2 a3
    1 4 6
    2 5 7
    7 3 9
    etc……

    My Question is:
    How to write select statment do display 2,3,9 numbers records present in a table.

    Reply
  • Hi Bala,

    Could you please explain, what is the logic behind it?

    Tejas

    Reply
  • Hi guys,

    I have read thru this whole thread and all the solutions are great when it comes to reading table properties from the system.

    I still do not see a solution on how to get the information from a table generated from a select statement.

    Example:

    select * from tbl_1 join tbl_1 and tbl_2
    (two tables, one result set)

    I need the number of columns and all properties for this result set. remember this is a on the fly table not in the information_schema.

    Can it be done without converting it to a temp table?

    Reply
    • @Dragonvic

      Instead of using *, specify the COLUMN-list. If two COLUMNs have the same name, give them unique names manually.

      Reply
  • Thank you Brian,

    but I think you misunderstood my question. I am trying to display property information on a result set like it is done on an existing table:

    select *
    from information_schema.columns
    where table_catalog = ‘your_table’

    Reply
    • @Dragonvic

      What do you mean by “property information”?

      Also, please given an example of the problem and the way you want it to look. I do not understand the issue.

      Reply
      • I did give an example…

        select *
        from information_schema.columns
        where table_catalog = ‘your_table’

        where it says ‘your_table’ enter any one of your tables.

        Tha query will give you properties for that table (columns, keys, values, etc.)

        I want to pull the same properties from a result set that is not already a table.

        example:

        select col1, col2, col3 …
        from tbl1 join tbl1 on tbl2.c1 = tbl1.c1

      • @Dragonvic

        I don’t understand what you want to see. If the query result is a pseudo-table for this example, the properties of the returned COLUMNs would be same as the COLUMNs in the original TABLEs. Hence, INFORMATION_SCHEMA has it. IOW, select *
        from information_schema.columns
        where table_catalog IN (‘tbl1’, ‘tbl2’)

      • Without using a temporary table it is not possible
        But Brian already told you the effecient method

  • That helps a bit, however, the actual tables (tbl1 and tbl2) are not native to sql, they are being imported using OPENROWSET, coming from a DBF and from an XLS. I need the ability in SQL to let me know how many columns these imports have so I can properly append to the correct local tables.

    Reply
    • @Dragonvic

      So, you want to know if there is a way to extract some schema information via OPENROWSET?

      I have no idea.

      Reply
  • No, that’s NOT what I was asking. I wnat to get the info from temp tables. But I think I know how, openrowset saves the data in a temp table.

    I know that you can pull information_schema from tempdb. The new challenge would be to hit the right table because tempdb saves the table name diferently from what you would name it.

    Reply
    • @dragonvic

      See, i knew i was confused. I didn’t get that from your original question. Unfortunately, i am no expert, and i do not know the answer to your question.

      Reply
  • Hi Pinal

    how are you

    in my website i want to shoot auto mail after every day which

    content the total records of the table which enter in privious

    privious day can you please tell me how can i do this

    waiting to your reply its urgent

    Reply
  • and also please tell me how can i find previous day date to current date so that make a serch

    Reply
    • where
      date_col>=dateadd(day,datediff(day,0,getdate())-1,0) and
      date_col<dateadd(day,datediff(day,0,getdate()),0)

      Reply
  • Hello Dev,

    You can get the date of previous day by subtracting 1 from GETDATE() as below:
    SELECT GETDATE()-1

    You can write the WHERE clause as below:

    WHERE datecolumn >= CONVERT(VARCHAR(10), GETDATE()-1) AND datecolumn < CONVERT(VARCHAR(10), GETDATE())

    Regards,
    Pinal Dave

    Reply
  • hi Pinal,

    I am writing to the query given below

    select * from tblqcontacts
    WHERE txtDate = CONVERT(VARCHAR(10), GETDATE()-1)

    to find the previous date all records in a table

    but there is an error occurs which is

    “Server: Msg 242, Level 16, State 3, Line 1
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.”

    what i can do to find all record of previous date of current date

    waiting to your reply

    Reply
    • @dev1

      Why are you CONVERTing to VARCHAR? Remove the CONVERT and there is no error.

      To change a data, perhaps DATEADD() would work better for you.

      Reply
    • The effecient method is

      select * from tblqcontacts
      WHERE
      txtDate >=dateadd(day,datediff(day,0,getdate())-1,0) and
      txtDate <dateadd(day,datediff(day,0,getdate()),0)

      Reply
  • Hi pinal

    Txtdate is a datetime column in querry

    also please tell me how can i find total records of a table through sql querry

    Reply
  • Hello Dev,

    The updated query is following:

    select * from tblqcontacts
    WHERE txtDate >= CONVERT(VARCHAR(10), GETDATE()-1,101) AND txtDate < CONVERT(VARCHAR(10), GETDATE(),101)

    Regards,
    Pinal Dave

    Reply
  • I have 10 rows with 3 columns in a table. i have to retrive the field values when column1 is x. In column1 many fields has value as x. Help me to form the SQL query

    Reply
  • can anybody solve this query, i have an employee table and i need to show all the data of those Employees who are Emp_Status ‘P’ (PERMANENT) and’B’ (PROBATIONARY)

    Reply
  • Hi Pinal

    i want to shoot a mail once in a day only through asp.net and

    sql can you please tell me whats the query for this

    waiting to your reply

    Dev

    Reply
  • Hi,

    Why don’t you configure mail in SQL SERVER and develop a Stored Procedure which will be schedule to run at once a day.

    This will reduce effort to maintain your task.

    To configure mail in SQL, please refer:

    Thanks,

    Tejas

    Reply
  • Hi i dont know how can configure mail server in sql server
    can u please tell me

    Dev1

    Reply
  • i am using sql server 2000

    Reply

Leave a Reply