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
