| Author |
 Topic  |
|
|
goodman2253
Yak Posting Veteran
83 Posts |
 Posted - 05/18/2012 : 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)
Singapore
16745 Posts |
Posted - 05/18/2012 : 03:27:08
|
select object_name(object_id), count(*)
from sys.columns
group by object_name(object_id)
KH
Time is always against us
|
 |
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
|
|
goodman2253
Yak Posting Veteran
83 Posts |
Posted - 05/18/2012 : 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
83 Posts |
Posted - 05/18/2012 : 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)
Singapore
16745 Posts |
Posted - 05/18/2012 : 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
Time is always against us
|
Edited by - khtan on 05/18/2012 06:39:41 |
|
|
|
goodman2253
Yak Posting Veteran
83 Posts |
Posted - 05/18/2012 : 06:07:31
|
| Referred but didn't get the clear picture. |
|
|
|
ashishashish
Constraint Violating Yak Guru
India
404 Posts |
Posted - 05/18/2012 : 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
83 Posts |
Posted - 05/18/2012 : 06:47:21
|
| Is their any different solution we can have |
|
|
|
ashishashish
Constraint Violating Yak Guru
India
404 Posts |
Posted - 05/18/2012 : 06:52:56
|
Is this solution having some issues..??
------------------------------------------------
The answer is always no till than you don't ask. |
|
|
|
goodman2253
Yak Posting Veteran
83 Posts |
Posted - 05/18/2012 : 06:53:05
|
Thanks ashishashish..
It really works |
|
|
|
ashishashish
Constraint Violating Yak Guru
India
404 Posts |
Posted - 05/18/2012 : 06:54:34
|
Your welcome.
------------------------------------------------
The answer is always no till than you don't ask. |
|
|
| |
Topic |
|
Print table name and count the number of columns
