Number Of Columns Per Table
-
Happy Friday Everyone
I am playing around with some code, and trying to learn some things. If you execute this query against any database, it will return the Rows Counts per table, and the Number of columns in said table. I am trying to code this to return only the MAX number, or the highest number of the Number Of Columns.
SELECT
SCHEMA_NAME(t.schema_id) AS schema_name
, t.name AS TableName
, i.rows
, c.column_id AS NumberOfColumns
FROM
sys.tables t
JOIN
sys.sysindexes i
ON
t.object_id
= i.id AND i.indid < 2
JOIN
sys.columns c
ON
t.object_id
= c.object_id
ORDER BY
i.rows ASC
Results for only one of the tables,
TableName rowsNumberOfColumns
q_tx_adh_stat01
q_tx_adh_stat02
q_tx_adh_stat03
q_tx_adh_stat04
q_tx_adh_stat05
q_tx_adh_stat06
q_tx_adh_stat07
q_tx_adh_stat08
q_tx_adh_stat09
q_tx_adh_stat010
I am trying to write the query that will return only this:
TableName rowsNumberOfColumns
q_tx_adh_stat010
I have tried a few different things, like using a CTE, using a HAVING clause, MAX() and some other ways, but I am obviously doing something incorrectly. I would like the MAX number of columns listed for each table that the query returns.
Thank you in advance for all your assistance, comments and suggestions
Andrew SQLDBA
-
SELECT TOP 1...
ORDER BY c.column_id DESC
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I thought of that, but it will not work because that query returns all tables in the database.
I am looking for a way to return the max Column number for each table listed. I did not list every table in my example, but I did explain that is what would be returned if you execute that query.
Thanks
Andrew SQLDBA
-
What about using ROW_NUMBER?
WITH CTE AS(SELECT
SCHEMA_NAME(t.schema_id) AS schema_name
, t.name AS TableName
, i.rows
, c.column_id AS NumberOfColumns
, ROW_NUMBER() OVER( PARTITION BY t.name ORDER BY c.column_id DESC) rn
FROM
sys.tables t
JOIN
sys.sysindexes i
ON
t.object_id
= i.id AND i.indid < 2
JOIN
sys.columns c
ON
t.object_id
= c.object_id
)
SELECT *
FROM CTE
WHERE rn = 1
ORDER BY
rows ASC
-
Why not just count them??
SELECT Schema_name(t.schema_id) AS schema_name,
t.name AS TableName,
i.rows,
COUNT(c.column_id) AS NumberOfColumns
FROM sys.tables t
JOIN sys.sysindexes i
ON t.object_id = i.id
AND i.indid < 2
JOIN sys.columns c
ON t.object_id = c.object_id
GROUP BY Schema_name(t.schema_id),
t.name,
i.rows
ORDER BY i.rows ASC
_______________________________________________________________
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/ -
Thanks Sean
That is perfect and just what I was after.
Thank You
Andrew SQLDBA
-
Luis
Thank you also. The query that you suggested worked also. I did not mean to lave you out of my last post.
Thank you
Andrew SQLDBA
-
You're welcome. Glad that worked for you.
_______________________________________________________________
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/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply