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)

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

Related Posts

277 Comments. Leave new

  • Hridballav Saha
    March 2, 2011 1:13 pm

    Hi Pinal,

    I am having a strange problem when assigning foreign key to a table.
    the error message no.1767: foreign key references invalid table table1.
    error.1750:Could not create constraint. See previous errors.

    although the table table1 is already created with the primary key which the foreign key is referencing.
    Please help me out.

    Reply
  • How to find number of columns without using count(*) ?
    Reply soon.

    Reply
  • in SSRS to count rownumber

    =Runningvalue(a.value, count, “Dataset1”)

    or

    =runningvalue(a.value, countdistrict, “Datasetname”)

    or

    =runningvalue(a.value, sum, “groupname”)

    Reply
  • Hi,
    How to get first 10 columns from a table without mentioning the column name in sql query

    Reply
    • You need to use dynamic sql
      This may help you

      Reply
      • —Try for Pubs database which has default schema name dbo or make a table with default schema else you need to add parameter for procedure for schema.

        CREATE procedure TopNcolumns
        (
        @tableName varchar(100),
        @n int

        )
        as
        Declare @s varchar(2000)
        set @s=”
        If @n>0
        Begin

        DECLARE @column_n varchar(30)
        declare @temp int
        set @temp=1
        DECLARE @getcolumn_n CURSOR
        SET @getcolumn_n = CURSOR FOR
        SELECT column_name
        FROM information_schema.columns where table_name=@tableName order by
        ordinal_position
        OPEN @getcolumn_n
        FETCH NEXT
        FROM @getcolumn_n INTO @column_n
        WHILE @temp<=@n
        BEGIN
        set @temp=@temp+1
        set @s=@s+','+ @column_n
        PRINT @column_n
        FETCH NEXT
        FROM @getcolumn_n INTO @column_n
        END
        CLOSE @getcolumn_n
        DEALLOCATE @getcolumn_n

        Set @s=substring(@s,2,len(@s)-1)
        Exec ('Select '+@s+' from '+@tablename)
        End
        Else
        Print ' 0 clumn passed as parameter'

        —-now try this…….
        exec TopNcolumns 'jobs' ,3

      • Please use for above code it changed to some other during copy paste into HTML i believe.
        Set @s= ” –single quote twice.

    • select top(10) column_name from information_schema.columns where table_name = ‘yourtablename’

      Reply
  • Hi all

    when i export to csv file from ssrs2008 one extra row is displaying header, how to hide that particular row ?????????

    Thank

    Reply
  • SELECT sysobjects.Name [TableName], sysindexes.Rows
    FROM sysobjects
    JOIN sysindexes ON sysobjects.id = sysindexes.id
    WHERE type = ‘U’ AND sysindexes.IndId < 2
    ORDER BY 1,2

    Reply
  • How to get number of columns from select query :
    Example:

    select xx, yy from trade where ….

    number of columns is 2 in this case, is there a way to find out dynamically?? Please help

    Reply
  • How can we count No of rows in table without using Count (*) or without Count keyword?

    Reply
  • Query to find table size for all tables of each database in a server:

    DECLARE @DatabaseName VARCHAR(100)
    DECLARE @SQL VARCHAR(max)

    –Main table to keep all data
    CREATE TABLE #TempTableMain
    (
    databaseName varchar(100),
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
    )

    –table to keep data per database and then truncate for the next database
    CREATE TABLE #TempTable
    (
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
    )

    –table to store tablenames in a particular database
    create table #tableName (name varchar(100))

    –Cursor for running query per database
    DECLARE databaseCursor CURSOR
    FOR select [name] from msdb.sys.databases
    where [name] not in (‘master’,’tempdb’,’model’,’msdb’)

    OPEN databaseCursor
    FETCH NEXT FROM databaseCursor INTO @DatabaseName
    WHILE (@@Fetch_Status >= 0)
    BEGIN
    –storing tableNames of a particuler database in a temp table
    SELECT @SQL = ‘use ‘+@DatabaseName+’
    insert into #tableName
    select [name] from sys.tables’
    exec(@SQL)

    –soring table information in temp table
    SELECT @SQL =
    COALESCE(@SQL + CHAR(13) + ‘ ‘ ,”) +

    use ‘+ @DatabaseName+’
    INSERT #TempTable
    EXEC sp_spaceused ‘+name
    from #tableName
    print @SQL
    exec (@SQL)

    –storing data for the current database in the main table
    INSERT #TempTableMain
    select @DatabaseName,* from #TempTable

    –truncating temp tables for next run
    truncate table #tableName
    truncate table #TempTable

    FETCH NEXT FROM databaseCursor INTO @DatabaseName
    END
    CLOSE databaseCursor
    DEALLOCATE databaseCursor

    –tables to get size in MB and GB
    –this part is optional and configurable as per user and usage
    CREATE TABLE #TempTableMainMB
    (
    databaseName varchar(100),
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSizeMB varchar(50),
    dataSizeMB varchar(50),
    indexSizeMB varchar(50),
    unusedSizeMB varchar(50)
    )

    CREATE TABLE #TempTableMainGB
    (
    databaseName varchar(100),
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSizeGB varchar(50),
    dataSizeGB varchar(50),
    indexSizeGB varchar(50),
    unusedSizeGB varchar(50)
    )

    insert #TempTableMainMB
    select * from #TempTableMain

    –Remove ‘KB’ from tables
    update #TempTableMainMB
    set reservedSizeMB=REPLACE(reservedSizeMB,’ KB’,”),
    dataSizeMB=REPLACE(dataSizeMB,’ KB’,”),
    indexSizeMB=REPLACE(indexSizeMB,’ KB’,”),
    unusedSizeMB=REPLACE(unusedSizeMB,’ KB’,”)

    update #TempTableMainMB
    set reservedSizeMB=cast(CAST(reservedSizeMB as INT)/1024 AS varchar(100)),
    dataSizeMB=cast(CAST(dataSizeMB as INT)/1024 AS varchar(100)),
    indexSizeMB=cast(CAST(indexSizeMB as INT)/1024 AS varchar(100)),
    unusedSizeMB=cast(CAST(unusedSizeMB as INT)/1024 AS varchar(100))

    insert #TempTableMainGB
    select * from #TempTableMainMB

    update #TempTableMainGB
    set reservedSizeGB=cast(CAST(reservedSizeGB as INT)/1024 AS varchar(100)),
    dataSizeGB=cast(CAST(dataSizeGB as INT)/1024 AS varchar(100)),
    indexSizeGB=cast(CAST(indexSizeGB as INT)/1024 AS varchar(100)),
    unusedSizeGB=cast(CAST(unusedSizeGB as INT)/1024 AS varchar(100))

    –display of results
    SELECT * FROM #TempTableMain order by 1
    SELECT * FROM #TempTableMainMB ORDER BY 1
    SELECT * FROM #TempTableMainGB ORDER BY 1

    –Final cleanup
    DROP TABLE #TempTable
    DROP TABLE #TempTableMain
    DROP TABLE #TempTableMainMB
    DROP TABLE #TempTableMainGB
    drop table #tableName

    Reply
  • Santosh S.Pawar
    March 3, 2012 11:48 am

    How to Display Table’s column as row
    Ex: I Have a Table Employee
    Emp_id Emp_Name
    101 Santosh
    102 Vijay
    103 Anilkumar

    Now i wanna to display all records of Emp_Name field as horizantally. Like:

    Santosh Vijay Anilkumar ………………

    Please Help me
    Thanks
    Santos S.Pawar

    Reply
  • You are the best . You save my life every day Thank you..

    Reply
  • the no of colunms in table are called?

    Reply
  • hi,

    how to find the table estimate size based on the number of rows as input. If I give no of rows per a table, it should give estimated size of that table.

    Reply
  • Deepak Kumar Jena
    November 1, 2012 3:23 pm

    No of columns in a table can be returned using the below query:
    Select count(*) from user_tab_columns where table_name = ‘tablename’;

    Reply
  • Awesomw sir…….

    Reply
  • Samir Kumar Ransingh
    March 22, 2013 9:23 am

    How can we know the maximum number of rows sql server 2005 can hold for any package as oracle.

    Reply
  • CREATE FUNCTION FN_GET_AGE_YMD
    (
    @IP_DOB DATETIME,
    @IP_TILLDATE DATETIME
    )
    RETURNS TABLE
    AS
    RETURN(
    SELECT YEARS,MONTHS,DAYS,DATEDIFF(HOUR,CURDATE,@IP_TILLDATE)-24*DAYS AS HOURS, 0 AS MINUTES, 0 AS SECONDS
    FROM(
    SELECT *,
    CASE WHEN DATEPART(HOUR,CURDATE)>DATEPART(HOUR,@IP_TILLDATE)
    THEN DATEDIFF(DAY,CURDATE,@IP_TILLDATE)-1
    ELSE DATEDIFF(DAY,CURDATE,@IP_TILLDATE) END DAYS
    FROM(
    SELECT *,DATEADD(MONTH,MONTHS,CURYEAR)CURDATE
    FROM(
    SELECT *,CASE WHEN DAY(@IP_DOB)>DAY(@IP_TILLDATE) OR
    (DAY(@IP_DOB)=DAY(@IP_TILLDATE)
    AND DATEPART(HOUR,@IP_DOB)>DATEPART(HOUR,@IP_TILLDATE))
    THEN DATEDIFF(MONTH,CURYEAR,@IP_TILLDATE)-1
    ELSE DATEDIFF(MONTH,CURYEAR,@IP_TILLDATE) END MONTHS
    FROM(
    SELECT *,DATEADD(YEAR,YEARS,@IP_DOB)CURYEAR
    FROM(
    SELECT CASE WHEN MONTH(@IP_DOB)>MONTH(@IP_TILLDATE) OR
    (MONTH(@IP_DOB)=MONTH(@IP_TILLDATE)
    AND DAY(@IP_DOB)>DAY(@IP_TILLDATE)) OR
    (MONTH(@IP_DOB)=MONTH(@IP_TILLDATE)
    AND DAY(@IP_DOB)=DAY(@IP_TILLDATE)
    AND DATEPART(HOUR,@IP_DOB)>DATEPART(HOUR,@IP_TILLDATE))
    THEN DATEDIFF(YEAR,@IP_DOB,@IP_TILLDATE)-1
    ELSE DATEDIFF(YEAR,@IP_DOB,@IP_TILLDATE) END YEARS
    )A)B)C)D)E)

    Reply
  • hi all,
    Can u pls tell me the query to get all the values from the table .. ie.. for example if i give select * from agent(table name)… it should return me all the values including the column header …it should return me as table to my front end.. the table name will be given by the user.. so there should not be any hardcode of values in the code… I wanna the query to be used in my coding

    Reply
  • Hi,

    This is also a very good article as usual we got every article here.
    As Discussion going here..
    my Query is something deeper in this.. I want size of all the tables as group of months..
    I want size of all table month wise..
    Like I want to know what was the size of my table in january and what was in July so i can compare the increasing rate of my tables..
    I want output like that contains month and size of table in that month and table name..

    Please Help me…

    Reply
  • Hi am amar i am a SQL developer today i got one of the requirement ,Soupose I have 100 tables for each table there is a column mame called ‘status ‘.i want to retrive status column of all the tables.Can any one help me .regarding this query.

    Reply

Leave a Reply

Menu