Alex A
asked on
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
where count(*) > 0
thanx
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
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
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.
SELECT OBJECT_NAME(object_id) AS table_name
FROM sys.dm_db_partition_stats
WHERE index_id IN (0, 1) AND OBJECTPROPERTYEX(object_id
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window