Data base testing sql query

Question

Data base testing sql query

anusuyashivhaani

SSC Enthusiast

Points: 147
More actions
October 6, 2015 at 7:38 pm

#399146

HI
I am a part of database testing team. I have imported a text file (for example -tables.csv file) which contains two fields 1. table name- all the tables of a database 2. Primary column- primary column name of the respective tables.
I am trying to write a sql query for getting the table name, primary column ,count (primary column) from table name group by primary column ;
The challenge is i need to make a loop ,so that each time the table name and primary column has to be fetched from the tables.csv file.
So ,My final output has to be something like as below. The idea is to get the below value for all table names and its corresponding primary column as per tables.csv.
table name, primary column, count of primary column
Please help.
Thanks
Anu

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

could you post the structure of the table and some sample data? Should be fairly easy. You might have to do a summary query to do the counts and then join back to the same table to get the keys.

anusuyashivhaani SSC Enthusiast Points: 147 More actions · Answer 2

Hi ,

Thanks for the response. Have attached a sample excel which contains the structure of csv file that I will import , and two other tables. I have given the expected output table as well.

I have more than 100 tables with me and each table has different primary column. My requirement is to get the count of all primary columns of all the tables.

Instead writing the count query 100 times, I am trying to achieve that in single query.

Hope it clarifies.

Thanks again

pietlinden SSC Guru Points: 63428 More actions · Answer 3

Just wondering, who is putting you up to something like this? I can see this going sideways very fast. Without understanding what you're really trying to achieve, I'm hesitant to offer advice (not that mine is worth any more than you pay for it, i.e., nothing.)

Tons of tables? For a newbie? Scary thought. You can probably get all this information from the schema without much work at all.

Read this link. Pinal's website is great... sometimes you gotta dig around, but learning is like that... you gotta earn it.

http://blog.sqlauthority.com/2008/08/06/sql-server-query-to-find-column-from-all-tables-of-database/

http://blog.sqlauthority.com/2009/07/17/sql-server-two-methods-to-retrieve-list-of-primary-keys-and-foreign-keys-of-database/

anusuyashivhaani SSC Enthusiast Points: 147 More actions · Answer 4

HI

I have already visited that website and queried from information schema to get the list of all table names and column names. but that is not the requirement.

Being a freebie, total no of tables doesn't matter. I just need the workable logic to query regardless of number of tables.

Thanks for your response anyways.