Print table name and count the number of columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Print table name and count the number of columns

Author

Topic

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-05-18 : 03:05:24

I want to set up the loop which should print the
table name and the count of number of columns present in
that table

It should something like

for i in all tables
{
select i, no of fields
from i
}
in sql server 2005

And the output wanted is

Table1--3
Table2--4

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-18 : 03:27:08

[code]
select object_name(object_id), count(*)
from sys.columns
group by object_name(object_id)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-18 : 04:03:23

SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-05-18 : 04:47:52

The number of rows also wanted with all that stuff.

The final output wanted is

Table_Name--count_no_of_rows--count_no_of_cloumns

T1--200--12
T2--800--11

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-05-18 : 05:30:12

When using the table
sys.dm_db_partition_stats st

It gives an error, the error is
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-18 : 05:46:39

refer to BOL http://msdn.microsoft.com/en-us/library/ms187737.aspx

quote:
Requires VIEW DATABASE STATE permission to query the sys.dm_db_partition_stats dynamic management view

KH
[spoiler]Time is always against us[/spoiler]

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-05-18 : 06:07:31

Referred but didn't get the clear picture.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2012-05-18 : 06:46:59

I hope you want it like that.. and hope it will work

SELECT [Rows].TableName,
[Rows].NoOfRows,
Count([Columns].column_id) NoOfColumns
FROM sys.columns [Columns]
INNER JOIN (SELECT st.Name AS TableName,
Sum(CASE
WHEN ( p.index_id < 2 )
AND ( a.type = 1 ) THEN p.rows
ELSE 0
END) AS NoOfRows
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
INNER JOIN sys.tables st
ON st.object_id = p.Object_ID
INNER JOIN sys.schemas sch
ON sch.schema_id = st.schema_id
GROUP BY st.name) [Rows]
ON Object_name([Columns].object_id) = [Rows].TableName
GROUP BY [Rows].TableName,
[Rows].NoOfRows

------------------------------------------------
The answer is always no till than you don't ask.

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-05-18 : 06:47:21

Is their any different solution we can have

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2012-05-18 : 06:52:56

Is this solution having some issues..??

------------------------------------------------
The answer is always no till than you don't ask.

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-05-18 : 06:53:05

Thanks ashishashish..
It really works