Senstar
Safeguarding people, places and property
Contact       Xnet Login       English
Knowledge Base Article
Article Type: How To
Product: Symphony
Product Version: 7.0
Component: Symphony Server
Device Brands:
Created: 23-Feb-2018 10:28:33 AM
Last Updated:

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:
  4. 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.

Average rating:
Please log in to rate.
Rated by 0, Viewed by 803