7 Replies - 666 Views - Last Post: 12 February 2015 - 02:30 PM

#1 alapee   User is offline

  • Stressed Out Programmer

Reputation: 239
  • View blog
  • Posts: 2,536
  • Joined: 24-October 13

Counting Tables that have Data

Posted 12 February 2015 - 01:39 PM

Ok, I have ran into a bit of trouble. I am using SQL Server 2014 Express and I need to get a count of all the tables in the TargetX database that have data, I need to return a simple integer.
This is what I have so far :
DECLARE tables CURSOR FOR SELECT DISTINCT(TABLE_NAME)  FROM TargetX.INFORMATION_SCHEMA.COLUMNS

BEGIN
	declare @Count int, @TableName varchar(128), @Used int
	OPEN tables
	FETCH NEXT FROM tables INTO @TableName
	WHILE @@FETCH_STATUS = 0
        BEGIN
		@used = (CASE WHEN exists (SELECT TOP 1 FROM @TableName) 
			THEN  @used = @used +1)
		END
		FETCH NEXT FROM tables INTO @TableName
	END
END
	CLOSE tables
	DEALLOCATE table



OF course this code is very incomplete, I might be brain fried at this point understanding the general concepts of it. Can some one please straighten me out on this? Is there a simpler way? Or am I completely off base?

Is This A Good Question/Topic? 0
  • +

Replies To: Counting Tables that have Data

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,344
  • Joined: 12-December 12

Re: Counting Tables that have Data

Posted 12 February 2015 - 01:47 PM

I believe you should be looking at
SELECT * FROM TargetX.information_schema.tables

not columns. I think it could be SELECT TABLE_NAME FROM.. (I haven't tested myself).

This will include views as well, so you could exclude them with either WHERE TABLE_TYPE='BASE TABLE' or WHERE TABLE_TYPE != 'VIEW'.

This post has been edited by andrewsw: 12 February 2015 - 01:48 PM

Was This Post Helpful? 0
  • +
  • -

#3 alapee   User is offline

  • Stressed Out Programmer

Reputation: 239
  • View blog
  • Posts: 2,536
  • Joined: 24-October 13

Re: Counting Tables that have Data

Posted 12 February 2015 - 01:55 PM

I tried something like that

SELECT Count(Distinct(TABLE_NAME)) FROM TargetX.information_schema.tables



But it returns all the tables, unfortunately I just need the tables that have data in it. I know that some of them are tables that are setup and just don't have any data in them...............Thanks for getting back so fast.
Was This Post Helpful? 0
  • +
  • -

#4 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: Counting Tables that have Data

Posted 12 February 2015 - 02:06 PM

Look at the SYS.DM_DB_PARTITION_STATS table for row_count. Join that to sys.tables and you should be able to get what you want.

SELECT t.[name], SUM(row_Count) 
FROM SYS.DM_DB_PARTITION_STATS
INNER JOIN SYS.[tables] [T] 
    ON [dm_db_partition_stats].[object_id] = [T].[object_id] 
GROUP BY t.[name]
HAVING SUM(row_Count) > 0



Run that against the database. That should give you what you want.

This post has been edited by rgfirefly24: 12 February 2015 - 02:08 PM

Was This Post Helpful? 2
  • +
  • -

#5 alapee   User is offline

  • Stressed Out Programmer

Reputation: 239
  • View blog
  • Posts: 2,536
  • Joined: 24-October 13

Re: Counting Tables that have Data

Posted 12 February 2015 - 02:17 PM

I wish I thought of that [and seen that post] earlier, Firefly. I did some more digging into the SYS information and came up with this
SELECT  Distinct(o.name) , SUM(p.row_count) as [Rows]
FROM    TargetX.SYS.TABLES as o, TargetX.SYS.DM_DB_PARTITION_STATS as p
WHERE  p.[object_ID] = o.[object_id]
AND  p.Row_Count > 0
GROUP BY o.name
ORDER BY 2 ASC




I Ran it by Rows ASC to check for 0 values indicating no table information.

Thanks everyone for you help.

This post has been edited by alapee: 12 February 2015 - 02:29 PM

Was This Post Helpful? 0
  • +
  • -

#6 CasiOo   User is offline

  • D.I.C Lover
  • member icon

Reputation: 1578
  • View blog
  • Posts: 3,551
  • Joined: 05-April 11

Re: Counting Tables that have Data

Posted 12 February 2015 - 02:23 PM

I do not usually code for MS SQL, but with the table names you have given above, I would probably write it something like
SELECT count(*)
  FROM TargetX.information_schema.tables AS target
 WHERE EXISTS(SELECT TOP 1 FROM target.table_name);



Edit:
Oh you guys found a table containing the information, great! :)

This post has been edited by CasiOo: 12 February 2015 - 02:27 PM

Was This Post Helpful? 0
  • +
  • -

#7 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: Counting Tables that have Data

Posted 12 February 2015 - 02:29 PM

A note though, is if this is a production database, and the database is hit often, I would add a WITH (NOLOCK) hint to your tables so that there is no way that your query would cause any sort of blocking issues.

EX:
SELECT
    *
FROM Table1 WITH (NOLOCK)


Was This Post Helpful? 0
  • +
  • -

#8 alapee   User is offline

  • Stressed Out Programmer

Reputation: 239
  • View blog
  • Posts: 2,536
  • Joined: 24-October 13

Re: Counting Tables that have Data

Posted 12 February 2015 - 02:30 PM

I originally had a C# program that did if for me but the username I was using didn't have access to the Database in question (or the next one it looks like) So I had to improvise
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1