count from multiple tables for same column exists in database

Question

count from multiple tables for same column exists in database

navie22

SSC-Addicted

Points: 494
More actions
May 19, 2014 at 8:49 am

#307476

i have database which has 25 tables. all tables have productid column. i need to find total records for product id = 20003 from all the tables in database.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

navie22 (5/19/2014)
i have database which has 25 tables. all tables have productid column. i need to find total records for product id = 20003 from all the tables in database.

Hi and welcome to the forums!!! Rather sparse on details here. Is this a one time thing or something you need to do repeatedly. Do you have a fixed number of tables to search? What do you want to return? The total number of rows found in all these tables? The number of tables? etc...

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

navie22 SSC-Addicted Points: 494 More actions · Answer 2

i have to create a job. this query, gives me table names in database with column name with where condition. now, from here, i also need to get count for certain records.

SELECT

@@SERVERNAME as ServerName

,DB_NAME() as DatabaseName

,t.name AS TableName

,c.name AS ColName

FROM sys.columns c

JOIN sys.tables t ON c.object_id = t.object_id

WHERE c.name LIKE '%RDSOURCENUMID%'

group by c.name, t.name, scale

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

navie22 (5/19/2014)
i have to create a job. this query, gives me table names in database with column name with where condition. now, from here, i also need to get count for certain records.
SELECT
@@SERVERNAME as ServerName
,DB_NAME() as DatabaseName
,t.name AS TableName
,c.name AS ColName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%RDSOURCENUMID%'
group by c.name, t.name, scale

So this query gives you the name of the columns you want to check? And you don't know the name of the columns?

I would like to help you but you have to provide me some details.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

navie22 SSC-Addicted Points: 494 More actions · Answer 4

yes, this gives me column name, but i also wants to get total record for RDSOURCENUMID= 20003, 20004,20005

navie22 SSC-Addicted Points: 494 More actions · Answer 5

in the result i want to see like this:

servername, databasename, tablename, columnname, recordcount for id 20003,20004 and 20005

thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

navie22 (5/19/2014)
in the result i want to see like this:
servername, databasename, tablename, columnname, recordcount for id 20003,20004 and 20005
thanks

Can you post ddl, sample data and the actual desired output based on the sample data? I am still not really getting what you want to see here.

Please take a few minutes and read the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

navie22 SSC-Addicted Points: 494 More actions · Answer 7

in results i like to see for recordcount_ resourcenumid = 20003

table name record_count

AR_STAR_CUSTTRXTYPE 213

AR_STAR_FACTS 10814294

AR_STAR_FACTSDIST_EBS 13773751

AR_STAR_FACTSEXT_EBS 10814294

my current query gives me only table names.

thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

navie22 (5/19/2014)
in results i like to see for recordcount_ resourcenumid = 20003
table name record_count
AR_STAR_CUSTTRXTYPE 213
AR_STAR_FACTS 10814294
AR_STAR_FACTSDIST_EBS 13773751
AR_STAR_FACTSEXT_EBS 10814294
my current query gives me only table names.
thanks

Obviously you did not even look at the article I suggested. Please remember that I can't see your screen, I have no idea what your tables are like, I have no idea what you are trying to do. The only details I have are what you have posted, which at this point is not detailed in the least.

Help me out here and post some ddl and sample for one of these tables so I can help you with the code that you need.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

navie22 SSC-Addicted Points: 494 More actions · Answer 9

Yes, i read article. lets try this. here is script which helps me to get column name 'rdsourceidnum from all tables in database it is exits.

use RD_CMMart

go

DECLARE @dtNow datetime = GETDATE()

SELECT

@@SERVERNAME as ServerName

,DB_NAME() as DatabaseName

,t.name AS TableName

,c.name AS ColName

,@dtNow as CaptureDateTime

FROM sys.columns c

JOIN sys.tables t ON c.object_id = t.object_id

WHERE c.name LIKE '%RDSOURCENUMID%'

group by c.name, t.name

result: heading (servername, databasename,tablename,columnname,capturedate):

servername, databasename, table1, RDSOURCENUMID, date

servername, databasename, table2, RDSOURCENUMID, date

servername, databasename, table3, RDSOURCENUMID, date

servername, databasename, table4, RDSOURCENUMID, date

so far so good.

now, in the result all tables listed, i need to get record count for specific redsourcenumid. which are 6-7 like 20003,20004 or so on from all the tables.

so, my result should look ike this:

Heading(servername,databasename,tablename, rdsourcenumid_num,record_count,capturedate):

servername, databasename, table1, 20003, 5000754, date

servername, databasename, table2, 20004, 7852456,date

servername, databasename, table3, 20004, 540,date

servername, databasename, table4, 20003, 5400,date

sorry, wasn't clear before. my problem is to getting rdsourcenumid_num,record_count in the results.

thanks,

Sukhi

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

Create a temp table to hold results in a way you need.

Then decalre a cursor over your query which lists db names, table names and column names

Then you will need to build dynamic sql, something like

SET @sql = 'INSERT INTO #results (dbname, tablename, columnname, rec_count)

SELECT ''' + @dbname + ''',''' + @tablename + ''',''' + @columname + ''', (

SELECT COUNT(*) FROM ' + @dbname + '.dbo.' + @tablename + ' WHERE ' + @columname + ' [whatever criteria you need, LIKE or = ' + @value + ')'

then execute the above using sp_executesql or just EXEC (@sql)

Please note: the above, is very simplisting idea. You can definitely write much more elegant code, but use of cursor here is justified and dosent' need to be avoided.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

navie22 SSC-Addicted Points: 494 More actions · Answer 11

thank you!

i will try this. This will help me.

thanks,