SQL query for multiple tables with one SELECT statement using variables

Question

I'm pretty new to SQL, but given the below example is there a way to condense the query? From my understanding SQL does not have arrays, but is it possible to store the tables somehow and do one SELECT statement, as maybe a variable (Select Count(*) From Table_Name) As Table_Name)?

SELECT
(SELECT COUNT(*) FROM ActivityLog) AS ActivityLog,
(SELECT COUNT(*) FROM ActivityLogin) AS ActivityLogin,
(SELECT COUNT(*) FROM ActivityPlayContent) AS ActivityPlayContent,
(SELECT COUNT(*) FROM ChangeLog) AS ChangeLog,
(SELECT COUNT(*) FROM ContentApprovalLog) AS ContentApprovalLog,
(SELECT COUNT(*) FROM ExceptionLog) AS ExceptionLog,
(SELECT COUNT(*) FROM State) AS State

You could define a stored procedure which dynamically constructs and then executes a SQL string query like the one you have above, but this is pretty much how SQL works. Dynamically constructing it as an SP is something akin to programming (with a language well not built for it) — Michael Berkowski
– Michael Berkowski, Commented Jun 9, 2014 at 15:38
Check this link, there are different ways to generate count for tables, you can pick one and modify it and write a function/stored procedure to work for the tables you want counts for. — rs.
– rs., Commented Jun 9, 2014 at 15:46

Azure · Accepted Answer · 2014-06-09 16:01:28Z

2

If you need this query often and want to have it simpler, you can create a VIEW. You then have to query the view like you query a table.

SELECT * FROM MyView

Views are especially helpful when you have multiple complex queries and you want to reuse them in other queries (f.e. joining two views together or subquerying a view).

Create the view for your query as follows

CREATE VIEW MyView AS
SELECT
(SELECT COUNT(*) FROM ActivityLog) AS ActivityLog,
(SELECT COUNT(*) FROM ActivityLogin) AS ActivityLogin,
(SELECT COUNT(*) FROM ActivityPlayContent) AS ActivityPlayContent,
(SELECT COUNT(*) FROM ChangeLog) AS ChangeLog,
(SELECT COUNT(*) FROM ContentApprovalLog) AS ContentApprovalLog,
(SELECT COUNT(*) FROM ExceptionLog) AS ExceptionLog,
(SELECT COUNT(*) FROM State) AS State

edited Jun 9, 2014 at 16:01

answered Jun 9, 2014 at 15:38

Azure

4093 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

StevieG Over a year ago

A good point, well made, but it doesn't really answer the question?

Azure Over a year ago

I agree that stored procedures would address this more directly, but I don't want to teach a SQL beginner to put everything in SP as this wouldn't be good. Given that his intention is to make the query more condensed, a view solves this problem in a smarter way.

StevieG Over a year ago

The OP's question is asking how to write this without having to supply specific table names. In other words, he probably wants to write something dynamic based on sys.objects - as highlighted in the link in the comment from @rs.

Andrew L. Over a year ago

I started researching into stored procedures and it seems like it would be similar to a C# method that can be called with parameters. I have to dive in a bit more.

Andrew L. Over a year ago

Ok, I setup both a view and stored procedure. The stored procedure was a little tricky and it's not 100 percent yet, but I think I got it. Thanks!

Collectives™ on Stack Overflow

SQL query for multiple tables with one SELECT statement using variables

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related