List All Tables Sizes, Sorted By Size
Jan 13, 1999How do I get a list of table names and their sizes, sorted by sizes? The command sp_spaceused only lists one table at a time.
Thanks for your help.
Lan.
ADVERTISEMENT
List Of All Tables And Size
Oct 1, 2001Does anybody know how to make a query that presents ALL
tables and their sizes (in Kb or Mb) from a database?
Is there a Stored Procedure for this purpose?
Thanks
How To Get The First Row Of A Sorted List
May 14, 2007Hi,
I am a newbie so this might be a very basic question. I have a table that not sorted. So I write a query to sort the table like this
Select * from custInfo where custID <> '0' order by custID Desc
Now obviously I have a selection of the original table in desc order. Now I want to get the first custId from thid Desc selection. How do I do this.
So if the unsorted table looked like this
001 Martha
005 Steve
002 Mike
003 David
and the sorted selection looks like this
005 Steve
003 David
002 Mike
001 Martha
And then I want to select Steve's custID which is 005. how do i do this.
Thanks in advance.
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
DB Engine :: List Out All Database Sizes?
May 28, 2009find out the list of databases that have ''ldf'' size greater than ''mdf' using stored procedure?
View 7 Replies View RelatedKeeping Tables Sorted
Nov 28, 2003we have a simple table
Key, Name, Address, City, State, Zip ................ect
I would like to keep this table sorted by Name, theirfore I won't have to sort my results with every querry.
I think I need to add something to my insert to tell my table - "Hay take Jones", open up the prober place and stick him in the proper spot.
Ex: We have Appleby and Robertson in our table now. My insert would tell SQL Server to take Jones, figure our where he belongs (alpha), and stick him in, resulting in.
Appleby
Jones
Robertson
This way I wont have to as the querry to sort stuff every time I reference this table, this will save lots and lots of overhead. and help keep my clients happy with quick(er) response.
thanks in advance -arthur
Join Tables On Sorted Values
Nov 19, 2013Is there a way to join tables that have multiple matches to each other (2 records in one table and 2 in another) so that you get 2 records returned instead of 4 with only 1 JOIN ON qualifier?
In our warehouse DB, there is a master location table, an inventory location table, and physical table for counting all product in the warehouse. The master location table has one record per location, but there could be multiple items in that location so my outer join from the master location to the inventory table returns something like:
select M.MASTER_LOC, C.AS ORIG_ITEM, C.ORIG_LOT, C.ORIG_QTY
from
M_LOC M LEFT OUTER JOIN
C_INVT C ON M.MASTER_LOC = C.INVT_LOC
order by M.LOC_CODE
LOCATION ITEM LOT QTY
01-01-A 100 abc 25
01-02-A NULL NULL NULL
01-03-A 200 def 50
01-03-A 200 ghi 50
My problem is adding the third counted inventory table because it could look like anything depending on what we find in the racks:
LOCATION ITEM LOT QTY
01-01-A 100 abc 25 (exact match)
01-02-A 150 cba 75 (Item found)
01-03-A 200 ghi 50 (LOT swapped)
01-03-A 300 def 50 (Item Changed)
My join is returning 4 rows for location 01-03-A which I understand, but I'm wondering if I can sort within the join or make some temp tables so that instead of:
select M.MASTER_LOC, C.AS ORIG_ITEM, C.ORIG_LOT, C.ORIG_QTY, E.AS CNTD_ITEM, E.CNTD_LOT, E.CNTD_QTY
from
M_LOC M LEFT OUTER JOIN
C_INVT C ON M.MASTER_LOC = C.INVT_LOC LEFT OUTER JOIN
E_PHYS_INVT E ON M.MASTER_LOC = E.LOC_CODE
order by M.LOC_CODE
LOC1 ITEM LOT QTY LOC2 ITEM LOT QTY
01-03-A 200 def 50 01-03-A 200 ghi 50
01-03-A 200 def 5001-03-A 300 def 50
01-03-A 200 ghi 5001-03-A 200 ghi 50
01-03-A 200 ghi 5001-03-A 300 def 50
I'd like to just end up with 2 lines sorted by location, item, lot, qty so I can see that there is a problem with that location.
LOC1 ITEM LOT QTY LOC2 ITEM LOT QTY
01-03-A 200 def 5001-03-A 200 ghi 50
01-03-A 200 ghi 5001-03-A 300 def 50
Matching Tables Are Different Sizes
Apr 17, 2001Hey all,
Got a little problem. have 2 matching tables on different servers with the EXACT same column layout and data (the tables are being replicated with MSSQL7) and one table is 200MB while the other is 2000MB. I'm running MSSQL7 SP2. Any ideas???
-Marc
Tables ,sizes, Quanity
May 31, 2007Got a question,
still new to sql server express, been playing with it for a while, kinda enjoying it.
But
If you were designing a database that had many tables with the possibility of a large amount of data, would you keep them all in one database or would you disperse them into multiple databases. There will be some relations used in some of the tables, but not all.
I believe I will end up with about 21 different tables, 9 of them have the potential for 1,000's of records.
I do appreciate anyone's thoughts or concerns
Davids Learning
List Table Name And Size
Jul 29, 2002Anybody know any method/script I can use to see a list of table name
and table size (in bytes, not rows) within a database?
I know I can see the size of any particular table in "Table info" tab or using "sp_spaceused" procedure. But I can't generate a report with a list of table name and table size using these methods.
Any idea? Thanks.
John
List Databases With Their Size
Feb 12, 2007I want to list my databases, and physical related files, and their size but the following commands do not provide the size. Any idea?
SELECT * FROM sysdatabases
SELECT * FROM sys.sysfiles
I know it's silly question
Canada DBA
Transact SQL :: Query To Get DB List And Size
Nov 19, 2015I need to retrieve list of all databases and the size occupied by them in sql server. As of now am pulling data for each database using sys.dqatabase_files which is hard for me as there are around 40-50 db on SQL Server.
View 4 Replies View RelatedCreate List Of Table Names And Size For A Database
May 5, 2004Hi there,
I am trying to create a list of all the tables in one database and then list the size of each table. So for example I want to create a table with the table name and table size for one DB
E.g
Table1 1111KB
Table2 123300MB
Table3 120448KB
etc for all the tables in a particukar DB
I know there is a stored procedure to list the sizes: 'sp_spaceused' but not sure how to script all this together.
can anyone help please!!
From
NewToSQL
(URgert)Is There A Way I Can Increase The Size Of The Drop Down List In My Report Viewer
Jan 18, 2008
Hi,
I have a report on my report server... and it has set for multivalue parameter... but since the particular client has only one plan so they wont have the select all function... but the size of the ddl is so squished that we cannot see the Plan name at all...
So can someone pls help me as to how can i increase it..
Regards
Karen
Passing A List Of Numbers To A Stored Procudure, Having A Size More Than 8000 Characters
Aug 1, 2007Hi..
I m working on MS SQL Server 2000.
I am trying to pass a list of numbers to a stored procedure to be used with 'IN()' statement.
I was doing something like..
Create Procedure proc
(
@Items varchar(100) --- List of numbers
)
AS Begin
Declare @SQL varchar(8000)
Set @SQL =
'
Select Query......
Where products IN (' + @items + ') '
'
Exec (@SQL)
This stored procedure is working fine, but when i m adding more required stuff to that, the size exceeds 8000, & it gives the error "Invalid operator for data type. Operator equals add, type equals text."
Can any1 please help me out with this ASAP??
How Do I Get A List Of Tables In T-SQL
Mar 11, 2005Hi,
Is there anything equivalent to Oracle's Select * from tab in MS SQL.
How I Can Get List Of Tables?
Dec 24, 2005Hi friends,
How I can get list of tables and list of fields within those tables in SQL server.
Thnak a lot.
List Of Tables
Jun 6, 2001How to obtain the list of the tables of a base by un script
View 1 Replies View RelatedHow To Get A List Of Tables Currently In A DB
Mar 21, 2001Hi all,
Does any one know what command(s) I can use from a SQL prompt to tell me what tables are cruuently residing in a particular database. I'm looking for something along the lines of "select list_of_all_tables from tempdb", which can be applied to any valid db on the system.
Thanks in advance for any guidance.
Rgds,
Kevin
List From Three Tables
Oct 5, 2006HI I have three tables as here:
inventory
isbn, status, status_date, quantity
authors
id, first_name, family_name
books
isbn, category, title, num_pages, price, copyright, author1, author2, author3
How can I list title and author name of the book that is most in stock?
My another problem to
Listing names of authors who have not written ay books
Thanks folks!!!
List All Tables Ans SP
May 25, 2008Hi
can I through a script retrieve a list af all tables and stored procedure in a database?
List Tables Which Are Used By A SP
Sep 17, 2007Hi,
I got a request from a devloper asking, how can he find out what tables his stored procedure his using, so he can remove unwanted SP which are using old tables in the database.
Bharath JrDBA
List Of Tables In A Db
Jul 20, 2005Hi Allcan some one please tell me the transact sql code that can list all tablesin a db.thanksTodd
View 1 Replies View RelatedSize Of Tables
Jan 21, 2000I am finding that the size of my SQL Server 7 tables are approx 75% LARGER than the same Visual FoxPro table. I am not considering indexes in either one. The only difference is that the SQL Server table has a timestamp that the Visual FoxPro table does not.
Is this normal? or did I do something wacko?
I upsized the data from Visual Fox to SQL by appending the info in the Visual Fox table into a remote view and then doing a TABLEUPDATE to send it to SQL Server.
Any help would be appreciated.
Rick
Size Of Tables
Nov 16, 2001I'm having to do some estimation on the size of the db that I will eventually create.
I have a 6.7 million row table that holds 5 integers and a varchar(8).
Therefore my crude maths are that an integer is 4 bytes and a varchar is max 8 and therefore each line is 28 maxmimum bytes long. Multiply by 6.7 million and you get 1876000000 bytes or 183203KB or 179MB's.
With me playing with this table the db has balooned from 23mb to 1,325mb. This doesnt work out. One possible explanation is if SQL Server does the same as Access and you need to compress to gain the space back. I wasn't aware of this though. I have filled and emptied the table several times over to test the import routine.
I therefore have a few questions.
1. Why has the db ballooned to this size?
2. How do I accurately work out future db/table sizes?
3. Is there a way to work out the size of a table?
4. Do SQL db's have to be compacted like Access's?
Help appreciated.
Size Of Tables
Jun 5, 2008Hi,
I know I can check individual table's properties for the size and row count of each, but is there a way of doing it for all tables within a database in SQL server 2005 ? I have about 69 tables in one..
Thanks,
Raddy
Size Of Tables
Nov 8, 2006Hi,
I have a database which is 25 GB in size. The database has few tables.
Can I individually find out which table is occupying more space?
Thanks
Santhosh
Retrieving A List Ot Tables
Mar 7, 2007Hello Everyone and thanks for your help in advance. I am working on an application that connects to SQL Server. I need to find out if there is any way (I know there is, not sure how) to retrieve a list of tables within a database and also, a way to retrieve a list of databases within a server. I am using VB.Net in a web application. Any help on this owuld be greatly appreciated.
View 2 Replies View RelatedHow To Get List Of Tables In A Database
Jan 22, 2004Hello
How I get the list of tables in a database. I'm using sql server 2000.
Thanks
List User Tables
Apr 14, 2001I'm brain-dead today, sadly. If it weren't for IE remembering previous entries, I don't know if my name and email would have made it into the header correct :-)
I want the SQL command that lists the names of all user tables.
Alternatively, I have the following problematic Access 2000 code:
Public Sub ListAllTables()
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim cnn As New ADODB.Connection
Dim i As Integer, j As Integer
Dim vgRet As Variant
Dim intPrefixLen As Integer
Dim strAppend As String
DoCmd.Hourglass True
cnn.Open CurrentProject.Connection
cat.ActiveConnection = CurrentProject.Connection
intPrefixLen = Len(CON_pkgPrefix)
Debug.Print cat.Tables.Count
For j = (cat.Tables.Count - 1) To 0 Step -1
Set tbl = cat.Tables(j)
With tbl
Debug.Print tbl.Name
vgRet = SysCmd(acSysCmdSetStatus, tbl.Name)
End With
Next
Set tbl = Nothing
Set cnn = Nothing
Set cat = Nothing
DoCmd.Hourglass False
vgRet = SysCmd(acSysCmdSetStatus, " ")
End Sub
This code runs fine against an MDB but against SQL it includes all the views, rather than just the tables. If you have a fix for this, that will do just fine!
Arthur
Cannot List Tables Or Other Objects
Nov 16, 2004Sometimes, when a try to list tables or view (or access Management, etc) Enterprise Manager hungs up.
Using SELECT * FROM SYSOBECTS also freezes the process. But the SQLServer is operational for data updates and retrieves.
Anyone?
thanx!
List Tables In A View??
Jan 5, 2005Is there a way to easily list the tables/views that a view is using to get its data?
Thanks in advance,
Shawn
How Can I List The Tables If I Have SA Access?
Apr 3, 2008Hi,
I'm very new in SQL Server. Please help me to combine the connection string to the server.
62.33.197.7
login: sa
pwd: S#15trLdatabase
What would be the connection string then? And can I get the tables names if I have the connection string?