This query shows rows, columns and size for each table, so you can spot the biggest table in database.
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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
276 Comments. Leave new
I find the question from OW very relevant and important. Matching tables by their names duplicate the records from tables with same names in different schemas. Can anybody please shed a light to this point?
Here is my solution using catalog views, which are more flexible than using stored procedures, and considers tables with same name in different schemas:
USE my_DB
SELECT s.[name] [schema]
, t.[name] [table]
, t.create_date
, t.modify_date
, p.rows
, SUM(a.used_pages) * 8 data_KB
FROM sys.schemas s
JOIN sys.tables t
ON s.[schema_id] = t.[schema_id]
JOIN sys.partitions p
ON t.[object_id] = p.[object_id]
JOIN sys.allocation_units a
ON a.container_id =p.partition_id
GROUP BY
s.[name]
, t.[name]
, t.create_date
, t.modify_date
, p.rows
ORDER BY SUM(a.used_pages) DESC
GO
Note that the stored procedure sp_spaceused uses dynamic management view (sys.dm_db_partition_stats) instead of catalog view.
How do you get increment rowcount total in a while loop fowhen you are deleting records from a one table and in the end you want to print the total number of records deleted.
@Joel
See if @ROWCOUNT works in your situation.
how to get number of row intable and use it in gui application
All,
Please can anyone help me out for the way out for writing a procedure for searching numbers in all tables in a database having multiple schemas.
Hoping that you all champs can help me out as I am new bee.
Rgds,
BR..
Follow this post. It will help you in getting what you want. But you need to change the code little bit. Let me know if you find any difficulties
Hi All,
In a table A, there are 3 columns and in 500 records like below
Table A
a1 a2 a3
1 4 6
2 5 7
7 3 9
etc……
My Question is:
How to write select statment do display 2,3,9 numbers records present in a table.
Hi Bala,
Could you please explain, what is the logic behind it?
Tejas
I have a query which is belongs to compare the one table columns count in the diff database.and i need to find out if there is any diff between the database count need to display the no of count with coulmns.
Any one can help to get the result.
Please refer the system sp like
sp_columns ‘table_name’
which give u total columns of table.
Hi guys,
I have read thru this whole thread and all the solutions are great when it comes to reading table properties from the system.
I still do not see a solution on how to get the information from a table generated from a select statement.
Example:
select * from tbl_1 join tbl_1 and tbl_2
(two tables, one result set)
I need the number of columns and all properties for this result set. remember this is a on the fly table not in the information_schema.
Can it be done without converting it to a temp table?
@Dragonvic
Instead of using *, specify the COLUMN-list. If two COLUMNs have the same name, give them unique names manually.
Thank you Brian,
but I think you misunderstood my question. I am trying to display property information on a result set like it is done on an existing table:
select *
from information_schema.columns
where table_catalog = ‘your_table’
@Dragonvic
What do you mean by “property information”?
Also, please given an example of the problem and the way you want it to look. I do not understand the issue.
I did give an example…
select *
from information_schema.columns
where table_catalog = ‘your_table’
where it says ‘your_table’ enter any one of your tables.
Tha query will give you properties for that table (columns, keys, values, etc.)
I want to pull the same properties from a result set that is not already a table.
example:
select col1, col2, col3 …
from tbl1 join tbl1 on tbl2.c1 = tbl1.c1
@Dragonvic
I don’t understand what you want to see. If the query result is a pseudo-table for this example, the properties of the returned COLUMNs would be same as the COLUMNs in the original TABLEs. Hence, INFORMATION_SCHEMA has it. IOW, select *
from information_schema.columns
where table_catalog IN (‘tbl1’, ‘tbl2’)
Without using a temporary table it is not possible
But Brian already told you the effecient method
That helps a bit, however, the actual tables (tbl1 and tbl2) are not native to sql, they are being imported using OPENROWSET, coming from a DBF and from an XLS. I need the ability in SQL to let me know how many columns these imports have so I can properly append to the correct local tables.
@Dragonvic
So, you want to know if there is a way to extract some schema information via OPENROWSET?
I have no idea.
No, that’s NOT what I was asking. I wnat to get the info from temp tables. But I think I know how, openrowset saves the data in a temp table.
I know that you can pull information_schema from tempdb. The new challenge would be to hit the right table because tempdb saves the table name diferently from what you would name it.
@dragonvic
See, i knew i was confused. I didn’t get that from your original question. Unfortunately, i am no expert, and i do not know the answer to your question.
Hi Pinal
how are you
in my website i want to shoot auto mail after every day which
content the total records of the table which enter in privious
privious day can you please tell me how can i do this
waiting to your reply its urgent
and also please tell me how can i find previous day date to current date so that make a serch
where
date_col>=dateadd(day,datediff(day,0,getdate())-1,0) and
date_col<dateadd(day,datediff(day,0,getdate()),0)
Hello Dev,
You can get the date of previous day by subtracting 1 from GETDATE() as below:
SELECT GETDATE()-1
You can write the WHERE clause as below:
WHERE datecolumn >= CONVERT(VARCHAR(10), GETDATE()-1) AND datecolumn < CONVERT(VARCHAR(10), GETDATE())
Regards,
Pinal Dave
hi Pinal,
I am writing to the query given below
select * from tblqcontacts
WHERE txtDate = CONVERT(VARCHAR(10), GETDATE()-1)
to find the previous date all records in a table
but there is an error occurs which is
“Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.”
what i can do to find all record of previous date of current date
waiting to your reply
@dev1
Why are you CONVERTing to VARCHAR? Remove the CONVERT and there is no error.
To change a data, perhaps DATEADD() would work better for you.
The effecient method is
select * from tblqcontacts
WHERE
txtDate >=dateadd(day,datediff(day,0,getdate())-1,0) and
txtDate <dateadd(day,datediff(day,0,getdate()),0)
Hi pinal
Txtdate is a datetime column in querry
also please tell me how can i find total records of a table through sql querry
Hello Dev,
The updated query is following:
select * from tblqcontacts
WHERE txtDate >= CONVERT(VARCHAR(10), GETDATE()-1,101) AND txtDate < CONVERT(VARCHAR(10), GETDATE(),101)
Regards,
Pinal Dave
I have 10 rows with 3 columns in a table. i have to retrive the field values when column1 is x. In column1 many fields has value as x. Help me to form the SQL query
where column1=’X’