Query DB For Table Sizes/number Of Rows
Dec 10, 2007Hi
I have a database with 150 tables. I want to make a query ranking those tables by table size and/or number of rows.
Is this possible?
Many thanks
ds9
ADVERTISEMENT
Which Is The Most Efficient Query To Find Out The Total Number Of Rows In A Table?
Mar 30, 2006which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query
View 10 Replies View RelatedQuery Table Sizes
May 17, 2002Hi,
I am looking to runa query to get the sizes of the tables in my SQL 7 DB.
I know I can access the info in Enterprise Manager, under "Tables & Indexes".
But I need to get this info via a query.
I need rows and size.
I figured out how to get rows through the sys tables:
select sysobjects.name, sysindexes.rows
from sysobjects,sysindexes
where sysobjects.name = sysindexes.name
and xtype = 'U'
Is the size of each table stored in a sys table as well? I can't find it.
Query To Give All Table Sizes On A Database (was Query Help)
Mar 9, 2006Hi,
Does anyone has query to give all table sizes on a database?
Appreciate your help.
Thanks
How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time
Sep 24, 2007Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
How To Archiv Table To Another Table With Unique Number For All Rows Once + Date
Apr 28, 2008need help
how to archiv table to another table with unique number for all rows once + date time (not the second only day time +minute)
i need whan i insert to the another table add 2 more fields (unique number , date_time )
this is the table 1 i select from
ID fname new_date val_holiday
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1
222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3
333 ccc 03/04/2008 4
333 ccc 04/04/2008 4
this is the table 2 i insert into
----------------------------------
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------
111 aaaa 15/03/2008 1 666 15/04/2008 17:03
111 aaaa 16/03/2008 1 666 15/04/2008 17:03
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 666 15/04/2008 17:03
111 aaaa 20/03/2008 1 666 15/04/2008 17:03
111 aaaa 21/03/2008 1 666 15/04/2008 17:03
222 bbb 02/05/2008 3 666 15/04/2008 17:03
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3 666 15/04/2008 17:03
222 bbb 05/05/2008 3 666 15/04/2008 17:03
222 bbb 06/05/2008 3 666 15/04/2008 17:03
222 bbb 07/05/2008 3 666 15/04/2008 17:03
222 bbb 08/05/2008 3 666 15/04/2008 17:03
222 bbb 09/05/2008 3 666 15/04/2008 17:03
333 ccc 03/04/2008 4 666 15/04/2008 17:03
333 ccc 04/04/2008 4 666 15/04/2008 17:03
for evry archiv table to another table (insert) i need to get a unique number + date time (not the second only day time +minute)
next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------
111 aaaa 15/03/2008 1 667 15/04/2008 17:15
111 aaaa 16/03/2008 1 667 15/04/2008 17:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 667 15/04/2008 17:15
.........................
.....................................................................667 15/04/2008 17:15
next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------
111 aaaa 15/03/2008 1 668 15/04/2008 08:15
111 aaaa 16/03/2008 1 668 15/04/2008 08:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 668 15/04/2008 08:15
.........................
.....................................................................668 15/04/2008 08:15
TNX
Getting The Number Pf Rows In A Query Result
Sep 1, 2004My site have a complicated search, the search give the results in two stages- the first one giving the number of results in each section:
"In the forums there is X results for the word X
In the articles there is X results...."
And when the user click one of those lines, the list shows the specific results in that section.
My problem is that I don't know how to calculate the first part, for now I use dataset, and table.rows.count to show the number of results in each section. Since my site have more then ten, it looks like a great waste to fill such large dataset (in some words it can be thousands of rows in each section) only for getting the number of rows…
Are there is a sql procedure or key word that will give me only the number of results (the number of times that specific word showing in the columns?)
Great thanks
Number Of Rows In A Table
Nov 18, 2007HI!
1.select count(*) from sales
2.select rows from sysindexes where id=object_id('sales') and indid<2
both queries return number of rows.
can anyone tell me which one is better?
What Is The Easiest Way To Get The Number Of Rows In A Table?
Jul 24, 2007With classic asp I would do something like this:
set conn = server.createobject("ADODB.Connection")
conn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=databasename "set rs = conn.execute("select count(*) from myTable")response.write rs(0)How can I do this with asp.net in the fewest number of lines?
Return Number Of Rows From A Table
Dec 9, 2007Im am trying to return the number of rows in a table and i only can get a response of true thanks for any help
View 3 Replies View RelatedHow To Find The Number Of Rows In A Table
May 12, 2006I try to find the number of rows in a table with this commands:
CountRec = New SqlParameterCountRec.ParameterName = "@countrec"CountRec.SqlDbType = SqlDbType.IntCountRec.Value = 0MyCommand = New Data.SqlClient.SqlCommand()MyCommand.CommandText = "select count(*) as @countrec from Customer;"MyCommand.CommandType = Data.CommandType.TextMyCommand.Connection = MyConnectionMyCommand.Parameters.Add(CountRec)MyCommand.Connection.Open()MyReader = MyCommand.ExecuteReaderiRecordCount = CountRec.Value
This is the result:
Incorrect syntax near '@countrec'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '@countrec'.Source Error:
Line 39:
Line 40: MyCommand.Connection.Open()
Line 41: MyReader = MyCommand.ExecuteReader
Line 42: iRecordCount = CountRec.Value
Line 43:
Source File: E:DevelopWebASPwebAccessTimberSalesUserEntry.aspx.vb Line: 41
What to do? I need a complete example to see how it works.
Thanks...
Number Of Table Rows In The Catalog
Mar 11, 2008Hi,
What catalog table has the number of rows of a table ?
Limit Of Number Of Rows In A Table ?
Jun 28, 2007Is there a limit of how many rows a table can have in SQL compact Edition? I didn't find anything in the documentation, but I get regularly a funny error message "Expression evaluation caused an overflow. [ Name of function (if known) = ]" when I try to create record number 32768 (is equal to 2 to the power of 15).
Where is this limit documented ?
Code Snippet
const string connectionString = "Data Source='" + dbFileName + "'; Max Database Size = 4091; temp file max size = 4091";
using (SqlCeEngine testEngine = new SqlCeEngine(connectionString)) {
testEngine.CreateDatabase();
}
using (SqlCeCommand addMValuesTableComand = testDbConnection.CreateCommand()) {
addMValuesTableComand.CommandText = "CREATE TABLE MValues (MSerieId int, TimeStamp smallint, Value real, PRIMARY KEY(MSerieId, TimeStamp))";
addMValuesTableComand.ExecuteNonQuery();
}
//fill table
StatusLabel.Text = "fill MValues Table";
using (SqlCeCommand fillTableComand = testDbConnection.CreateCommand()) {
int i = 0;
try {
int iterationCount = (int)RecordsCountNumericUpDown.Value;
ProgressBar.Value = 0;
ProgressBar.Maximum = iterationCount;
for (i = 0;i < iterationCount;i++) {
fillTableComand.CommandText = "INSERT MValues VALUES (1, " + i.ToString() + ", " + (i/100.0).ToString() + ")";
fillTableComand.ExecuteNonQuery();
ProgressBar.Value = i+1;
}
} catch (Exception ex) {
ErrorTextBox.Text = "Error occured" + Environment.NewLine +
"Iterations: " + i.ToString() + Environment.NewLine +
"Error Message: " + ex.ToString();
}
}
Column In Query Results To Number Rows Sequentially
Apr 20, 2015I need a column in my query results that just numbers the rows sequentially (i.e. 1, 2, 3). How can I do that?
View 6 Replies View RelatedT-SQL (SS2K8) :: Number Of Rows And Range For Each Partition In A Table?
Mar 13, 2015Is it possible to show the number of rows and the range for each partition in a table ?
This shows me the range but not the row count per partition
SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] FROM sys.partition_functions AS spf
INNER JOIN sys.partition_range_values sprv
ON sprv.function_id=spf.function_id
WHERE (spf.name=N'myDateRangePF')
ORDER BY [ID] ASC
What Is The Maximum Number Of Rows Retrieve From A Table To Reports
Apr 5, 2007hi friends,
i got a error while retrieving more than 100000 rows (records) from a table .. can any one tell me what is the maximum number of rows retrieve from a database to reports... and how can i overcome this issue...
Add Column To Existing Table With Large Number Of Rows
Dec 24, 2007Hey Guys
i need to add a datetime column to an exisitng table that has like 1.2 million records and its being accessed frequently
but i cant afford to stop the db at all
whenever i do : alter table mytable add Updated_date datetime
it just takes too long and i have to stop executing the query after a couple of mins
I am running sql express 2005 sp2. db size is over 3 gb but still under the 4 gb limit
can u plz advice on how to add this column. its urgent!!
thanks in advance
Retrieving Specific Page(number Of Rows) Form Table
Sep 27, 2005hi,
i need SP that receive 2 integers ,@NUM_ROWS and @PAGE_NUMBER,
and return the rows in that page.
for example:
SP(4,2) will return 4 rows in page number 2 .
So if i have table with 9 rows i will get rows 5-8,
the first page is rows 1-4 the second page is 5-8 and the 3 page is row 9.
i have to assume that rows can be deleted form that table.
thanks
Insert Rows Based On Number Of Distinct Values In Another Table?
May 21, 2014I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
how can I update Table 1 to get desired results?
SQL Server 2014 :: Huge Number Of Rows In Table Spool
Jul 6, 2015I have a CTE query against a table with 32K rows that runs fine in 2008R2. I am running it in 2014 Std Ed. against the same data and it runs very slowly. Looking at the execution plan I think I see what's contributing to the slowness.
Note that the "actual number of rows" is some 351M...how is this possible?
the query:
declare @amts table (claim int,allowed decimal(12,2),copay decimal(12,2),deductible decimal(12,2),coins decimal(12,2));
;with unpaid (claimID) as (select claimID from claim where amt+copay + disct+mm + ded=0)
insert @amts
select lineID, sum(rc), sum(copay), sum(deduct),
case when sum(mm)>0 and (sum(mm)<sum(mmamt)) then sum(mm) else 0 end
from claimln
where status is null
and lineID not in (select claimID from unpaid)
group by lineID
it's like there's some massively recursive process going on?
Controlling Number Of Rows To Display In A Table And Matrix On One Page
Jul 19, 2007Is there a way to control how many Detail Rows are displayed on one page in Table and Matrix controls?
View 1 Replies View RelatedHow To Make A Table To Display Perticular Number Of Rows Per Page??
Nov 29, 2007Hi,
Can anybody tell how to ristrict the number of rows per page in SSRS?
I have a table in my report.I know we have to use" =Int(RowNumber(Nothing)/25)" in group expression.But I already have 3 group expression in my table.Where to write??
Thanks.
SQL Server 2008 :: Way To Check To Find Number Of Rows And Size Of A Table
Apr 29, 2015How can we monitor the all tables in all databases and send notifications to the team.Is there a way to check to find the no of rows and size of a table last month and find out growth % now
View 4 Replies View RelatedDataset Into A Table Or Matrix With Fixed Number Of Columns, Variable Rows
Jul 13, 2007Hi
I have a dataset with 2 columns, a rownumber and a servername - eg
rownumber servername
1 server1
2 server2
....
15 server15
I want to display the servernames in a report so that you get 3 columns - eg
server1 | server2 | server3
server4 | server5 | server6
...
server13 | server14 | server15
I have tried using multiple tables and lists and filtering the data on each one but this then makes formating very hard - i either end up with a huge gap between columns or the columns overlap
I have also tried using a matrix control but cant find a way to do this.
Does anybody know an easy way to do this? The data comes from sql 2005 so i can use a pivot clause on the dataset if somebody knows a way to do it this way. The reporting service is also RS2005
Thanks
Anthony
SQL Server 2012 :: Insert Rows Based On Number Of Distinct Values In Another Table
May 20, 2014I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
How can I update Table 1 to see desired results?
T-SQL (SS2K8) :: Write Into A Table User-entered Value And Increment That Number N Times As Existing Rows
Jun 25, 2014I need to have a script where it ask the user for a value, the script will search for all records that match the value. Then it will display the numbers of records found and ask the user to enter a different value. The rest of the script will use this new value and increment by 1 n times as the number of records found. I started the script where it will ask for "HANDLE" and display the number of records found with that "HANDLE"
declare @HANDLE as varchar(30)
declare @COUNT as varchar(10)
declare @STARTINV as varchar(20)
set @HANDLE = ?C --This is the parameter to search for records with this value
set @STARTINV = ?C --User will input the starting invoice number
SELECT COUNT as OrderCount FROM SHIPHIST
where HANDLE = @HANDLE
I just can't figure out how to proceed to use the entered invoice # and increment by 1 until it reach the number of records found.
This will be the end results:
Count=5 --results from query
STARTINV=00010 --Value entered by user
Handle,Inv_Num
AAABBB,00010
AAABBB,00011
AAABBB,00012
AAABBB,00013
AAABBB,00014
Built In Limit Or Setting That Limits The Number Of Rows From An OLE DB Source Table In A Data Flow Task?
Feb 16, 2007I have a table that I'm loading as part of a control flow that in turn is copied to a target table by using a data flow task. I am doing this because a different set of fields may be used from the source entry to create the target entry based on a field in the source table. That same field may indicate that multiple entries need to be created in the target table from one source table entry for which I use a multi-cast transformation.
The problem I'm having is that no matter how many entries there are in the source table, the OLE DB Source during execution only shows 7,532 entries being taken from the source table. If there are less than 7,532 entries in the source table, everything processes fine. More than 7,532 and the data flow task only takes 7,532 and then seems to hang. It also seems as though only one path of the multi-cast transformation is taken when the conditional split directs a source entry down that path.
Seems like a strange problem I know, but any insight anyone could provide is appreciated. Thanks.
Table Sizes In SQL Server 2000
Nov 14, 2001How can I get the table sizes for data and transaction logs just like we had in SQL Server 7.0 on the first screen of the Entreprise Manager?
I remember having a bar showing used space in blue and unused in magenta. I bet there are a couple of functions that can be added in a script that will retreive this info.
Table Sizes In SQL Server 2000
Nov 14, 2001How can I get the table sizes for data and transaction logs just like we had in SQL Server 7.0 on the first screen of the Entreprise Manager?
I remember having a bar showing used space in blue and unused in magenta. I bet there are a couple of functions that can be added in a script that will retreive this info.
List Of All Table Sizes In A Databases
Mar 6, 2000Hi,
Is there a query I can run to retrieve a list of all tables and their sizes in a database? I want something that is like the feature in Enterprise Manager when you click on a database and then the 'Tables & Index' link. It lists the tables and their respective size. I want to push this into a spread sheet.
The reason why I am doing this is the compare data between 2 different databases. Since I cannot find a tool that will compare the data, the closest I can get (without bcp-ing out all data and comparing) is to look at the sizes of each table.
Thanks!
Joyce
Decreasing The Varchar Column Sizes In A Table
Jul 27, 2004I have a table in my database and the table has almost 45 columns and the rowsize is 10468 bytes.in that most of the colums have varchar datatypes and and i think coz of poor knowledge of the data most of the columns with varchar data were given more column length. Now i want to decrease the size of those columns and to see the row size would be around 8k Bytes.If i do this now, does it affect the table performance much....Infact can i do this as there is lot of data (almost 2 million rows) in the table.If it is possible is there anything to be taken care before changing the column lenghts.
Thanks.
Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query
Dec 25, 2006While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.
For example,the output of the Aggregate Transformation may be 960216 ,but the
'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.
I'm sure the Group By of the Aggregate Transformation is right!
But ,when I set the "keyscale" property of the transformation,the results match!
In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.
Thanks for your advice.
T-SQL (SS2K8) :: Capture Database Sizes Table With Date
Jun 5, 2014I'm trying to capture the sizes of all Databases into a Permanent Table and include the Date.
It works when inserting into a #Temp Table.
When I try inserting into a permanent table it returns NULL.
The following code needs modified to create a permanent table and store the Date:
CREATE TABLE #databases ( DATABASE_NAME VARCHAR(50), DATABASE_SIZE FLOAT, Date VARCHAR(100) )
INSERT #Databases EXEC ('EXEC sp_databases');
SELECT@@SERVERNAME AS SERVER_NAME, DATABASE_NAME,
DATABASE_SIZE AS 'KB',
ROUND(DATABASE_SIZE / 1024, 2) AS 'MB',
ROUND((DATABASE_SIZE / 1024) / 1024, 2) AS 'GB',
CONVERT(date, getdate()) AS Date FROM #databases
DROP TABLE #databases;