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 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
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’
can anybody solve this query, i have an employee table and i need to show all the data of those Employees who are Emp_Status ‘P’ (PERMANENT) and’B’ (PROBATIONARY)
Is this?
Where Emp_Status =’P’ and Emp_Status =’B’
Hi Pinal
i want to shoot a mail once in a day only through asp.net and
sql can you please tell me whats the query for this
waiting to your reply
Dev
Hi,
Why don’t you configure mail in SQL SERVER and develop a Stored Procedure which will be schedule to run at once a day.
This will reduce effort to maintain your task.
To configure mail in SQL, please refer:
Thanks,
Tejas
Hi i dont know how can configure mail server in sql server
can u please tell me
Dev1
i am using sql server 2000