June 23, 2008 at 12:45 am
Hi all,
I want to find no of columns for a table in all databases.
Suggestion or query please....
Thanks in advance....
June 23, 2008 at 3:25 am
Query below for Columns per table entered
select count (*) "Number of Columns", C.object_id,O.name from sys.columns C
inner join sys.objects O
on C.object_id = O.object_id
where O.name ='Table Name' -- Enter table name
group by C.object_id, O.Name
Not sure what you mean all databases
Try below on a database for for tables with column count
select count (*) "Number of Columns",O.Name
from sys.columns C
inner join sys.objects O
on C.object_id = O.object_id
where type_desc = 'USER_TABLE'
group by C.object_id, O.Name
June 23, 2008 at 3:37 am
Thanks Wayne Yenson for the reply....:)
June 23, 2008 at 9:22 am
If you want to find the number of columns for the table in all databases, you can use sp_msforeachdb to execute a sql statement for each database.
exec sp_msforeachdb
'select ''Database: ?'' as [Database], count (*) as [Number of Columns],O.Name as [Table]
from sys.columns C
inner join sys.objects O
on C.object_id = O.object_id
where type_desc = ''USER_TABLE''
group by C.object_id, O.Name'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply