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)
270 Comments. Leave new
Why do these queries on the same table yield different results:
–This statement runs very fast
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘mytable’) AND indid < 2
–This one takes a while to execute
select count(*) from mytable
Mytable contains over 3millions records.
If you use versions prior to 2005, you need to run
dbcc updateusage command to get correct count
I am writing a join. The join is producing more records than desired.
Is there any way I can see the number of columns of the query returned when I say ‘select * from…’
Number of columns of the query that was just executed, not of some table that is already there.
Not possible until you move resultset to a temporary table
Nice Link
Hi All,
How to find the particular row in table?(means suppose one table contain 10 rows , in these 10 rows how to find the particular row(example in 10 rows i want 5 row how)?
There is no easy way to identify the row number until you order it by a specific column. You can use row_number() function Refer this link
hi……
select COUNT(*) from information_schema.columns
where table_name = ‘Your_Table_Name
its not working in oracle…………
You need to use ORACLE’s system tables
Do the google for the same
Hi, Ravi you can use following code to get the list of tables having 0 records.
———————————
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
WHERE a.row_count=0
GROUP BY a.table_name, a.row_count, a.data_size
DROP TABLE #temp
—————————-
Hi Dave,
I had a requirement like, I want to show the no of rows(count) as column name in temp table.
Thanks,
Sathish.
Hi Sathish,
if your are using SQL SERVER 2005, then you can use this following syntax
SELECT *,
COUNT(*) OVER(PARTITION BY ””) AS TotalRows
FROM TbaleName
Thanks,
Tejas
You dont need a partition
SELECT *,
COUNT(*) OVER() AS TotalRows
FROM TbaleName
Hi
Pinal
Thank you Very Much
Your code to insert multiple rows with one sql statement was useful…
is there any query to get the column count from a table using sql command
Thanks Tejas Shah.
Thanks! This is exactly what I needed!
hi guys,
i don’t usually use sql in my job…. but for this one purpose i’ve to use sqlite…
so can somebody just tell me a simple query to count just the number of columns in a table…
please hurry.. my time is running out…
PS. this sql is not that bad as it seems :)
@Sid
There are many ways to get this done, here are some of these.
select Count(*) ‘No Of columns’ from syscolumns where id = object_id(‘example’)
or
select count(*) ‘No Of columns’ from information_schema.columns where table_name = ‘example’
Or
select Count(*) ‘No Of columns’ from sys.columns where [object_id] = object_id(‘example’)
Hope this helps,
IM.
hi all
thanks for ur comments….
Hi Guys
New to SQL coding so can someone please help? I monitoring our replication and would like to take a row count of a table ‘table1’ and match it to the replication table on our tier 2 and 3 environment .
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
But I don’t want to receive and email everytime indication everything is ok. Ideally I would like to receive and email\ alert when the row counts do not match.
The one table I am looking at sits on 5 different servers. Sorry bit stuck !!
Also If I wanted to do row counts for mutiple tables accross the servers i.e table1 on all five servers, table 2, table 3 etc.
How could like be done?
Thanks in advance for your help!!
thx Pinal :) It helps me today to detect that tbXml table in Wsus 3.0 with 160.000 records has 1GB size :OOO
Makers of WSUS are crazy ;)))
Thx anyway for simple and handy snippet.
Regards
Thanks!! great tip saved me a lot of time
Hi Pinal,
Consider there are 5 databases in a sql server.Each database contains n number of tables. A column say ‘EMP id’ is repeated in different tables across the databases.
My requirement is to write a single query which should return the count of that column in all the databases.
Can u help me with this