Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 606
  • Last Modified:

SQL Server: need a query that returns list of tables

Hi, I need a query that returns list of all not empty data tables in the database;
 where count(*) > 0

thanx
0
quasar_ee
Asked:
quasar_ee
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
This gives you the list of tables.  With rowcounts is also possible but I don't have that SQL handy, will dig..
SELECT name FROM sys.tables ORDER BY name

Open in new window

0
 
dbaSQLCommented:
This will work:

     CREATE TABLE #TableCounts (TableName VARCHAR(500), RecordCount INT  )
     INSERT #TableCounts
     EXEC sp_msForEachTable  'SELECT PARSENAME(''?'', 1), COUNT(*) FROM ? WITH (NOLOCK)' ;

     SELECT TableName,RecordCount
     FROM #TableCounts
     WHERE RecordCount > 0
     ORDER BY TableName
0
 
Scott PletcherSenior DBACommented:
sys.dm_db_partition_stats view can give you that instantly, although it's not guaranteed to be 100% accurate if there are in-flight INSERTs/DELETEs to the table at that moment.

SELECT OBJECT_NAME(object_id) AS table_name
FROM sys.dm_db_partition_stats
WHERE index_id IN (0, 1) AND OBJECTPROPERTYEX(object_id, 'IsUserTable') = 1
GROUP BY object_id
HAVING SUM(row_count) >= 1
ORDER BY table_name

Of you can do a:
IF EXISTS(SELECT TOP (1) * FROM dbo.<table_name>)
for every table to test for a row.  Don't count every row, that could be a HUGE waste of resources since you don't need an actual count.
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hi ,

You can throw this simple SQL code for the same

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1)
and i.rows > 0
order by TableName;

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now