4 Replies - 1997 Views - Last Post: 21 November 2012 - 09:29 AM

#1 TheAKB   User is offline

  • D.I.C Head
  • member icon

Reputation: 2
  • View blog
  • Posts: 63
  • Joined: 11-November 08

How clever can SQL be

Posted 21 November 2012 - 03:46 AM

I only know the very basic stuff in SQL and i was wondering if SQL was clever enough to do what i'm trying to get too.

I currently have a statement that looks a bit like this:

SELECT TABLE_NAME FROM LIBR.SYSTABLES WHERE TABLE_SCHEMA LIKE 'v0604td'



It goes on for a bit more with AND's ect.

So what's happening here is it's selecting the table name from my DB with a parameter (being TABLE_SCHEMA) and this returns thousands of table's of which most are empty tables. The user which is using my .net screen then has to select/deselect the tables they want. What would be great is reduce the list by removing empty tables.

Is there a way to do this within the SQL statement or would i have to create a new sql statement which removes empty tables?

Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: How clever can SQL be

#2 rnty   User is offline

  • D.I.C Regular

Reputation: 19
  • View blog
  • Posts: 283
  • Joined: 14-August 12

Re: How clever can SQL be

Posted 21 November 2012 - 04:51 AM

SELECT TABLE_NAME FROM LIBR.SYSTABLES WHERE TABLE_SCHEMA LIKE 'v0604td' AND 1>(select count(*) from TABLE_NAME)
I am not sure but please try this and post back.
Sorry if it does not work.
Was This Post Helpful? 0
  • +
  • -

#3 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7506
  • View blog
  • Posts: 15,556
  • Joined: 16-October 07

Re: How clever can SQL be

Posted 21 November 2012 - 05:31 AM

No, you don't get to have field name as an object in a SQL statement. Many have tried, all have failed. However, the idea is correct.

To really, really, know if a table is empty, you kind of have to look at it. You'd execute some dynamic count for a check. You could even make this a function. It will, however, take forever. This is why databases generate various statistics and store them.

One table that holds statistics is sysindexes. Since every table has an index, primary or otherwise, this should be pretty reliable.

Perhaps:
SELECT a.name
	FROM sys.tables a
		INNER JOIN (select distinct id as object_id from sys.sysindexes where indid < 2 and rows>0) b
			on a.object_id=b.object_id



Note, "systables" is ancient history. Chucked as of 2005. However, in old MS SQL, you should be able to find sysindexes and go from there.
Was This Post Helpful? 1
  • +
  • -

#4 TheAKB   User is offline

  • D.I.C Head
  • member icon

Reputation: 2
  • View blog
  • Posts: 63
  • Joined: 11-November 08

Re: How clever can SQL be

Posted 21 November 2012 - 08:56 AM

Thanks for your reply's

rnty: I have already tried that, i thought that would work but i geuss as the sql statement see that as data and not table could be the reason why it fails


baavgai: I don't quite understand what you are saying which i geuss is me not understanding SQL that well.

i currently have a vb.net program that runs this SQL Command:

 
SELECT TABLE_NAME FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA LIKE 'V0604TD' AND TABLE_TYPE = 'P' ORDER BY TABLE_NAME 



and this returns thousands of tables of which many are empty. I understand why select count doesn't work and what i keep hearing is stored procedure though im not sure how i would do that. If you could explain in a little bit more detail i would be very grateful.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7506
  • View blog
  • Posts: 15,556
  • Joined: 16-October 07

Re: How clever can SQL be

Posted 21 November 2012 - 09:29 AM

I'm saying that systables is old and I don't have an SQL database that old to test. Unless this isn't SQL Server, in which case there is a different problem.

I'm also saying that a MS SQL database with systables should probably have sysindexes. The sysindexes table has a rows column; the count of rows that are in the index. It also has a reference to the table it belongs to. By joining the list of indexes that have a row count > 0 with the list of tables, you get a list of tables than have rows.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1