I Have 10000 Tables I Want Check The Table Is Having The Data How To Check All The Tables

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

See we have around 10000 tables are there . For one table we can the data from "select * from table " if there are 10000 table then how to check it

Posted 30-Apr-14 20:26pm

sonuods

Add a Solution

Comments

Kornfeld Eliyahu Peter 1-May-14 2:31am

One-by-one...

[no name] 1-May-14 2:33am

not one by one if we have 100000 tables then what u will do

[no name] 1-May-14 2:31am

can any body answer this

[no name] 1-May-14 2:33am

not one by one if we have 100000 tables then what u will do

Thanks7872 1-May-14 3:00am

Why such requirement at first place? I mean if you are intended to get some data then you should be aware of where it is stored. As others commented, there is no easy way to get data from such a huge no. of tables.

[no name] 1-May-14 4:20am

see in the company we have 100000 table and we can not see each and every table one by one and every time .
Now u tell me what should we do

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-05-01T08:26:00

Solution 3

Sorry, but your question has non-sense.

There is no need to get data from 10 thousand tables, because of several reasons. The most important are:
1) their structure could be differ,
2) application can hang-on when you're trying to get a lots of data (stays in non-response mode).

Re-think the database design!

Posted 1-May-14 8:26am

Maciej Los

Comments

Matt T Heffron 1-May-14 14:30pm

+5Even if all of the tables are "guaranteed" to have the same structure, 10000 tables is a pretty strong hint that the database design is flawed.

Maciej Los 1-May-14 14:33pm

Thank you, Matt ;)

CHill60 1-May-14 14:37pm

To be fair, about 18 months ago I had to write some scripts to examine an entire database and determine which of the c 2,500 tables actually contained data. It was a 3rd party application that was being retired with the data being passed to another supplier - I had to make sure we hadn't "missed" anything ... one of those "we don't know exactly what we want but if you don't give us something we needed then it's your fault" situations! :-)
I agree though, 10k tables is a bit OTT

Maciej Los 1-May-14 14:47pm

2.5K tables in a single database? WOW!
Can you explain why to check if tables contain data? Even if there is a need to upgrade or move database, there is no need to check the count of records in each of them. Update and insert statements return the information about the count of records affected.

CHill60 1-May-14 16:26pm

It was one of those "one database fits all possible application types" so many of the tables weren't relevant to the "flavour" of the app we were using. Plus over the years it had been over-configured and extended. We weren't upgrading or moving databases - extracting data to flat files for transfer to another supplier (database schema was proprietry and couldn't be shared). Only had a couple of days to produce a report of "things (data items) that hadn't already been provided, but were definitely "used" in the old system (i.e. had to map back onto the business objects). I just took a brute force approach of ignoring any tables that had no data and narrowed it down from there. Suffice to say it's not the way we would have chosen to do it!!
Edit - should have said I was only interested in the "count" if it was zero :-)

Maciej Los 1-May-14 16:59pm

I'd say "One database for many purposes" is the first step to the catastrophe...
Thank you for explanation.

RahulMGunjal · Answer 2 · 2014-04-30T20:54:00

Solution 1

You need to write a program to check this. As far as I know there isn't any magic code to find this. You can follow these steps. (I hope you are using SQL Server)

Find all table in a database using

SELECT * FROM information_schema.tables

Now write a while loop and check all the tables for the data one by one might be fetching Top 1 rows or count(*).

Posted 30-Apr-14 20:54pm

RahulMGunjal

Comments

[no name] 1-May-14 3:47am

I want to check that 10000 tables are having any record or not

[no name] 1-May-14 3:49am

these are giving me the table information only not the records are available or not

RahulMGunjal 1-May-14 4:30am

As I already said, you need to check all tables one by one whether record is present or not. Write some code for that.

[no name] 1-May-14 4:48am

ohh ok

[no name] 1-May-14 4:48am

i want to know that code

CHill60 · Answer 3 · 2014-05-01T03:45:00

Further to solution 1 you can join that table with the partitions table to get the row count for each table in a single query

SQL

SELECT *
FROM (
    SELECT
        TableName   = t.TABLE_SCHEMA + '.' + t.TABLE_NAME
        ,[RowCount] = SUM(sp.[ROWS])
        FROM INFORMATION_SCHEMA.TABLES t
        JOIN sys.partitions sp ON sp.object_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
    WHERE TABLE_TYPE = 'BASE TABLE'
    GROUP BY t.TABLE_SCHEMA + '.' + t.TABLE_NAME
) A
ORDER BY TableName

This solution was adapted from this post at sqlserverplanet[^]

If you decide to loop through the tables using a CURSOR[^] then don't use SELECT *.

You only want to know if any records exist, not view all of the data so use something like

SELECT TOP 1 FROM [tablename]

or

SELECT COUNT(*) FROM [tablename]

I Have 10000 Tables I Want Check The Table Is Having The Data How To Check All The Tables

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us