Looking what tables have data

Dear all,

This issue to encompass eitherSql2k or Sql25k.

I am looking for any script which might be enough smart to know which tables have data and which doesn’t and therefore show the info

I imagine any cursor with SELECT COUNT(*) or something like that.

Something like 'sp_tables' system stored procedure but along with the info the rows

Thanks in advance,

Enric

Moving to a more suitable group, this is not a SSIS question.

Plenty of ways to do this, but here is a simple script I keep in my "toolbox"-

IF OBJECT_ID('tempdb..#Table_SpaceUsed') IS NOT NULL DROP TABLE #Table_SpaceUsed

CREATE TABLE #Table_SpaceUsed

(

name sysname, rows int,

reserved varchar(20),

data varchar(20),

index_size varchar(20),

unused varchar(20)

)

DECLARE @.table sysname

DECLARE tables_cursor CURSOR FOR

SELECT name from sysobjects where type = 'U'

OPEN tables_cursor

FETCH NEXT FROM tables_cursor

INTO @.table

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT #Table_SpaceUsed

EXEC sp_spaceused @.table

FETCH NEXT FROM tables_cursor

INTO @.table

END

CLOSE tables_cursor

DEALLOCATE tables_cursor

GO

ALTER TABLE #Table_SpaceUsed ADD reserved_mb int

ALTER TABLE #Table_SpaceUsed ADD data_mb int

ALTER TABLE #Table_SpaceUsed ADD data_kb int

ALTER TABLE #Table_SpaceUsed ADD index_size_mb int

ALTER TABLE #Table_SpaceUsed ADD index_size_kb int

ALTER TABLE #Table_SpaceUsed ADD unused_mb int

GO

UPDATE #Table_SpaceUsed

SET reserved_mb = CASE

WHEN RIGHT(reserved, 3) = ' MB' THEN CAST(REPLACE(reserved, ' MB', '') AS int)

WHEN RIGHT(reserved, 3) = ' KB' THEN ROUND(CAST(REPLACE(reserved, ' KB', '') AS numeric(18,2)) / 1024.00, 0) END,

data_mb = CASE

WHEN RIGHT(data, 3) = ' MB' THEN CAST(REPLACE(data, ' MB', '') AS int)

WHEN RIGHT(data, 3) = ' KB' THEN ROUND(CAST(REPLACE(data, ' KB', '') AS numeric(18,2)) / 1024.00, 0) END,

data_kb = CASE

WHEN RIGHT(data, 3) = ' MB' THEN ROUND(CAST(REPLACE(data, ' MB', '') AS numeric(18,2)) / 1024.00, 0)

WHEN RIGHT(data, 3) = ' KB' THEN CAST(REPLACE(data, ' KB', '') AS int) END,

index_size_mb = CASE

WHEN RIGHT(index_size, 3) = ' MB' THEN CAST(REPLACE(index_size, ' MB', '') AS int)

WHEN RIGHT(index_size, 3) = ' KB' THEN ROUND(CAST(REPLACE(index_size, ' KB', '') AS numeric(18,2)) / 1024.00, 0) END,

index_size_kb = CASE

WHEN RIGHT(index_size, 3) = ' MB' THEN ROUND(CAST(REPLACE(index_size, ' MB', '') AS numeric(18,2)) / 1024.00, 0)

WHEN RIGHT(index_size, 3) = ' KB' THEN CAST(REPLACE(index_size, ' KB', '') AS int) END,

unused_mb = CASE

WHEN RIGHT(unused, 3) = ' MB' THEN CAST(REPLACE(unused, ' MB', '') AS int)

WHEN RIGHT(unused, 3) = ' KB' THEN ROUND(CAST(REPLACE(unused, ' KB', '') AS numeric(18,2)) / 1024.00, 0) END

SELECT name, rows, CASE WHEN rows > 0 THEN CAST(ROUND(CAST((data_kb+index_size_kb)AS numeric(38,6)) / CAST(rows AS numeric(38,6)), 2) AS numeric(10,2)) ELSE 0 END AS rows_size_kb, reserved_mb, data_mb, index_size_mb, unused_mb

FROM #Table_SpaceUsed ORDER BY reserved_mb DESC