Query to find empty tables

[dprotonentis]

I have the daunting task of converting an existing database to a new database structure. A conversion from one system to another. The database contains more than 500 tables and in excess of half a million records.

However, several of the tables in the database are empty. I'm trying to extract the data structure of the existing database while only extracting thoses tables that actually have data in them.

I am using the following SQL query:

SELECT o.name AS TableName, c.name AS ColumName
FROM syscolumns c JOIN sysobjects o
ON c.id = o.id
WHERE o.type = 'u'AND o.name NOT LIKE 'dt%'
ORDER BY o.name, c.name

This grabs all the tables and lists all their column names, excluding the dtproperties table. How can I modify this query to display only the table names and column names of tables with data?

 

[WhiteKnight57t]

This will give you the start you need

SELECT sysindexes.rows, sysindexes.name FROM sysindexes, sysobjects WHERE sysindexes.id = OBJECT_ID(sysobjects.name) and sysobjects.type = 'U' and sysindexes.indid < 2 order by sysobjects.name

 

[yksvaan]

First thought is to make a WHILE loop and build for EXECUTE a string which consist of the select count(*) statement. But I bet you are gonna get much better suggestions soon.

Cheers

 

[mrdenny]

You'll need to query each table and see if data is returned. The easiest way to do this will be with a fetch cursor, logging tables with data into a temp table. Something along these lines.

create table #Tables
(TableName varchar(1000))

declare @TableName varchar(1000)
declare @CMD varchar(8000)
declare cur CURSOR select table_name from information_schema.tables where table_name <> 'dtproperties'
open cur
fetch next from cur into @TableName
while @@fetch_status = 0
BEGIN
   set @CMD = 'if exists (select top 1 * from ' + @TableName + ') insert into #Tables (TableName) values (''' + @TableName + ''')'
  exec (@CMD)
   fetch next from cur into @TableName
END
close cur
deallocate cur
select table_name, column_name
from information_schema.columns
where table_name in (select TableName from #Tables)

Let me know if you have any questions.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[JamesLean]

No need to use a cursor here. There is an undocumented procedure called sp_msforeachtable that will execute a given query for all the tables in the database. This makes this job a lot easier:

--temp table to hold list of tables
CREATE TABLE #tablelist (tablename sysname, rows int)

--get list of tables with rowcounts
INSERT #tablelist
EXEC sp_msforeachtable 'SELECT ''?'', COUNT(*) FROM ?'

--strip off owner and identifiers from tablename
UPDATE #tablelist
SET tablename = REPLACE(SUBSTRING(tablename, LEN(tablename) - CHARINDEX('[', REVERSE(tablename)) + 2, LEN(tablename)), ']', '')

--(check list)
SELECT * FROM #tablelist
ORDER BY rows

--get columns for tables which have data
SELECT c.table_name, c.column_name
FROM information_schema.columns c JOIN #tablelist t ON c.table_name = t.tablename
WHERE t.rows > 0
ORDER BY c.table_name, c.ordinal_position

--James

 

[dprotonentis]

Mr. Denny & Mr. Lean

Both your querys did exactly what I needed. Thank you ever so much for the assistence.

Dirk