USE DatabaseName GO CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50)) SET NOCOUNT ON INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size FROM #temp a INNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate database_default GROUP BY a.table_name, a.row_count, a.data_size ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC DROP TABLE #temp
Reference: Pinal Dave (http://www.SQLAuthority.com)
277 Comments. Leave new
how to find number of columns in a table in sql server 2000.
SELECT Count(*) FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ‘GeneralProfiles’
Good one Saurabh. Thanks for your contribution.
Hi ,
can some one explain this query as the Information_schema.columns ia available in all tables by default and what is a table name here ” GeneralProfiles”
INFOMATION_SCHEMA.COLUMNS is a metadata table which contains data about columns of other tables.
HI ,
How can we get Row count for each column of a given table.
where if the row count should say ,
if Nulls are present in entire column for all rows should dispaly – NULL ,
if the entire column is Blank for all rows it should show BLANK.
Thanks.
How would I get column names added along with this query for each table?
hello,
what should we have to write query for counting a number of rows in a table. submit query.
How can we find out the Column names in SQL Server
like the ORACLE command
DESC
which is use to show the table structure
thanks
Use sp_help for view tables column like DESC in oracle.
how can we find the number of tables starting with some’p’in a database
select table_name from information_schema.tables
where table_name like 'p%'
to show the how many records in one database
This works great!
How can we get the row number of a particular table ?
By default there is no row_number
However based on the order of the column you can find it using row_number() function from version 2005 onwards
select row_number() over(order by somecol) ,* from your_table
This SQL Query returns total numbers of column in table
SELECT TOP 100 PERCENT WITH TIES c.TABLE_NAME , COUNT(*) NumColumns
FROM INFORMATION_SCHEMA.[COLUMNS ] c
inner join INFORMATION_SCHEMA.[TABLES] t
ON C.TABLE_SCHEMA = t.TABLE_SCHEMA
and c.TABLE_NAME = T.TABLE_NAME
GROUP BY c.TABLE_NAME
ORDER BY c.TABLE_NAME
You dont need top 100 percent as there is no subquery used
Hi Champs ….
Try this
SELECT COUNT(*) Statment will return the row counts !!
and one more thing with using SELECT COUNT(*) statment ….
Because the SELECT COUNT(*) statement makes a full table scan to return the total table’s row count, it can take an extremely long time for large tables. There is another way to determine the total row count in a table. In this case, you can use the sysindexes system table. There is a ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*):
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘table_name’) AND indid < 2
Cheers
Praveen
Note that if you use version 200, you need to use
dbb updateusage(‘db_name’)
in order to have correct number of rows
indid<2 means indid either 0 or 1.
If indid is 0 measn heap.
Indid is 1 means clustered index
But in case of nonclustered index indid is greater then 1.
So how to find the row count of a table if it uses nonclustered index?
@Varun – Non clustered Index would have value > 1 but heap also would be there.
Thanks Pinal you just saved my life :)
Write a Query to select column value in row form
Like
Table
Col1 Col2
Aaa 1
Bbb 2
Ccc 3
Ddd 4
Eee 5
Out put of query should belike
Col1 Aaa Bbb Ccc Ddd Eee
Col2 1 2 3 4 5
Search for PIVOT in google
hi vinod,
try creating a udf. add a cursor to read each row in your table and concat the values as you read. output the values using the udf a select statement.
hope this helps.
No. That is not the effecient solution
From the output it seems that he wants PIVOT
select COUNT(*) from dba_tab_columns
where table_name = YourTableNameHere;
Table name should be in upper case!!
It is ok if the database is case insensitive
Thanks a lot
” select COUNT(*) from dba_tab_columns
where table_name = YourTableNameHere; ”
:- this command does not exiquete ,please give one example use-sing above command
How to get the no. of rows in a table structure ?
hi suganya,
you can write the query in this way
select count(any column name) from yourtable.bcoz u can take any column to find the no of rows
That is not very true
If you use *, you will get all rows count
If you use count(col), you get count where col is not null
Select Count(*) from Table_Name is the query to get total no of rows in a table … we cannot take Column_Name in palce of * bcoz
Count(Column_Name) gives count of that particular column whereas Count(*) gives count count of entire table Rows
To be clear, count(col) will omit NULL values
Thanks for this info. Basic knowledge that i dint have :-)
SQL Server 2000 & 2005 both support a stored procedure call sp_help
call it with the tablename following it and you’ll get your table structure and indexes, filegroups, entities, etc….
here are some examples of how to use it…
sp_help MyTableName
sp_msforeachtable “sp_help ‘?'”
sp_msforeachtable “select ‘?’ TableName, count(*) as TotalRecords from ?”
Thanks pinal
hi guys,
how can i show some particular columns from a tabel without writing any column name?means even dnt know abt that particular column name.
bhuvnesh
You can try the following link
The idea is just give the number, it would give data to those many columns
If you speficy 10, you will get data fro first 10 columns
hi
I saw your blog
But i am still getting getting a problem
I have a table Xho.Department where
And Xho is for schema
now when i use below query
Select * from information_schema.columns
where table_name =’Xho.Department’
it returns nothing
But I am Having A table Name ‘Xho.Department’
so when i pass the table name ‘Xho.Department’ ,4 in your sp it produsing an error ……………..
Msg 536, Level 16, State 5, Procedure TopNcolumns, Line 26
Invalid length parameter passed to the SUBSTRING function.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘from’.
What happens you when try this?
Select * from information_schema.columns
where table_name =’Department’ and table_schema=’Xho’