Number Of Columns Per Table

Question

Post reply

Number Of Columns Per Table

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
September 20, 2013 at 7:39 am

#278211

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

Viewing 8 posts - 1 through 7 (of 7 total)

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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/

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

Thanks Sean

That is perfect and just what I was after.

Thank You

Andrew SQLDBA

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 6

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

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

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/