(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) 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; *****************************************************