DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 158--------------------------------------------------
       Script to list all non-empty tables in a db
       By: Vinoth_K_exceldbp Date: May 18, 2015, 11:54 pm
       ---------------------------------------------------------
       Intro:
       Whenever a db developer enter an existing project, few of the
       first things he usually tries to know
       is
       i) the tables actually used in a db (i.e. non-empty tables),
       ii) the tables having large no of rows and
       iii) the schemas (apart from dbo).
       This cute script serves this purpose.
       Scripts:
       /* Version A: Lists tables in row-count order to identify tables
       with large no of rows */
       SELECT t.NAME AS TableName, s.NAME AS SchemaName, i.rows AS
       NoOfRows
       FROM sys.tables t
       INNER JOIN sysindexes i ON i.id = t.object_id
       INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
       WHERE i.indid IN (0, 1)
       AND i.rows > 0 -- uncomment this to take tables in use only.
       ORDER BY NoOfRows, TableName, SchemaName; -- uncomment this to
       identify large tables.
       
       /* Version B: Lists tables in A-Z order for reference */
       SELECT t.NAME AS TableName, s.NAME AS SchemaName, i.rows AS
       NoOfRows
       FROM sys.tables t
       INNER JOIN sysindexes i ON i.id = t.object_id
       INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
       WHERE i.indid IN (0, 1)
       AND i.rows > 0
       ORDER BY TableName, SchemaName, NoOfRows;
       *****************************************************