Query Table Sizes
May 17, 2002
Hi,
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.
View 2 Replies
ADVERTISEMENT
Mar 9, 2006
Hi,
Does anyone has query to give all table sizes on a database?
Appreciate your help.
Thanks
View 2 Replies
View Related
Dec 10, 2007
Hi
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
View 6 Replies
View Related
Nov 14, 2001
How 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.
View 2 Replies
View Related
Nov 14, 2001
How 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.
View 2 Replies
View Related
Mar 6, 2000
Hi,
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
View 2 Replies
View Related
Jul 27, 2004
I 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.
View 2 Replies
View Related
Jun 5, 2014
I'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;
View 9 Replies
View Related
Aug 4, 2015
The below query works perfectly fine, except that it produces many outputs instead of one continuous table that can be easaly converted to xml / csv by copying it from the "Results" window.
What I need is a query that will produce a single result for all the tables in all the databases on the server.
The results:
The query:
DECLARE @begin INT = 1, @end INT, @sql NVARCHAR(MAX)
DECLARE @CREATE_TEMPLATE VARCHAR(MAX);
DECLARE @DBNAME VARCHAR(255);
DECLARE @SQL_SCRIPT VARCHAR(MAX);
SELECT @end = COUNT(name) FROM sys.databases
SET @CREATE_TEMPLATE = '
[Code] ....
View 11 Replies
View Related
Oct 13, 2015
I have a few databases that are using Partitioned Views in order to manage the table sizes and they all work well for our purposes. Recently I noticed a table that had grown to 400+ million rows and want to partition it as well, so I went about creating new base tables based on the initial table's structure, just adding a column to both table and primary key to be able to build a Partitioned View on them.The first time around, on a test system, everything worked flawlessly but when I put the same structure in place on the production system I get the dreaded "UNION ALL view 'DBName.dbo.RptReportData' is not updatable because the primary key of table '[DBName].[dbo].[RptReportData_201405]' is not included in the union result. [SQLSTATE 42000] (Error 4444)" error.
I have searched high and low and everything I see points to a few directives in order for a UNION ALL view to be updatable:
- Need a partitioning column that is part of the primary key
- Need a CHECK constraint that make the base tables exclusive, i.e. data cannot belong to more than one table
- Cannot have IDENTITY or calculated columns in the base tables
- The INSERT statement needs to specify all columns with actual values, i.e. not DEFAULT
Well, according to me, my structure fulfills these conditions but the INSERT fails anyway. CREATE scripts below scripted from SQL Server. I only modified them to be on a single row - it is easier to verify that they are identical in a text editor that way.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
[code]....
View 3 Replies
View Related
Jul 23, 2004
Anyone here with a ready to go sqlscript that lists all db's, files, sizes, owner etc? I guess it's a combination of sp_databases, sp_helpdb and sp_helpdb [db].
View 2 Replies
View Related
Sep 18, 2007
hi,
i'm planning to create a DB of about 1TB of text.
can SQL EXPRESS handle this sizes of data? what about SQL 2005?
does it matter if i put all the data in 1 table?
Thanks.
View 1 Replies
View Related
Mar 23, 2000
Hello,
I was told a while ago that your Log should be 2 times the size as your data. Is this a good rule of thumb?
Lee
View 2 Replies
View Related
Jul 29, 2002
Does anyone know of a quick way to find out what the largest indexes on a database are? I have a number of tables and was wondering if there's a stored proc or query that I can execute that will list the indexes and their size in order by size? Thanks
View 1 Replies
View Related
Jul 30, 2002
Does anyone know of a quick way to find out what the largest indexes on a database are? I have a number of tables and was wondering if there's a stored proc or query that I can execute that will list the indexes and their size in order by size? Thanks
View 1 Replies
View Related
Jan 18, 2006
Hi there
I hope this isn't a totally stupid question, but I was wondering how to check the physical size the DBs take up on disks...
Does MSSQL save DBs as a specific file-extension? Is there an SQL command I can run that will give extensive info on DB sizes?
Any help welcome :)
Thanks!
View 1 Replies
View Related
Apr 28, 2008
When you have the autogrowth turned on for log files. What happens when you put a max file size on it? Will just overwrite the old logs to keep the file at the max size or will it just create a new file every time it hits the max size?
Thanks!
View 1 Replies
View Related
Feb 20, 2008
I'm putting together a manual system that tracks data growth in a certain database. I was going to use sp_spaceused as a part of it, but then realized the datatypes for size are CHAR, not INT or BIGINT. I was going to do counts, averages, etc. on those columns but that wouldn't work against a CHAR field obviously. I could easily write a little something to strip out the KB, but was hoping there was another way to get those figures.
Secondly...has anynoe seen a stored procedure/code/etc. that just calculates the largest/smallest/average row size for a table? I haven't been able to find anything anywhere...
Any insight would be greatly appreciated...
Thanks!
View 2 Replies
View Related
Jun 19, 2004
Hello again everyone....
I have another question for everyone....
I am currently cleaning up my database to get its total size down and am not sure how nvarchar and varchar work exactly.
When defining the length of a varchar or nvarchar in enterprise manager, will that effect the size of the entry (as far as data size) no matter what the length of the entry? In other words, will there be a difference in Data Size for an entry with the length of 4 characters with a definition of varchar(4) versus an entry with the length of 4 characters with a definition of varchar(50).
****If there is no difference, is there any reason in trying to best guess the size to give nvarchar or varchar columns? It would seem easier to just define the lengths of columns which need variable lengths to 200 or 400 just to save time in not trying to best guess what the size might be...*****
Thanks ahead for any help...
-Alec
View 3 Replies
View Related
Apr 17, 2001
Hey 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
View 1 Replies
View Related
Nov 8, 2000
Hi,
my log files are growing like anything. One of my log file size is 20GB.
How i have to reduce the log file size.
If i run DBCC command is it come backs...
Pls tell me the way how i have to find the free space and reduce logsizes.
After taking backups also my log file sizes are not reducing.
Thanks!
Kavira
View 2 Replies
View Related
Dec 16, 1999
I have inherited a number of databases which were substantially over sized when they were set up. I'd like to reduce both the log and database files to be smaller than their original sizes, what's the easiest way to do this? If anyone has any experience of doing this please reply.
View 1 Replies
View Related
Aug 5, 2003
We are looking at installing a new Oracle server for a client but have been told that they have used Oracle in the past but had a lot of problems with slow response even though the bandwidth on the WAN was barely being used. He says that this was due to the fact that Oracle sends out very small packets across the network meaning that there are hundreds of packets being sent out. This caused a problem on the routers being used as it was killing the processors. Is this still the case and have you had other reports of slow response of this nature?
View 1 Replies
View Related
Mar 30, 1999
Ok, I have a new one. Several of my devices are showing with negative sizes when viewed in edit in enterprise manager. I cannot edit them as the change now button is grayed-out. Oddly enough they are all located on the same drive. The master (on C drive), and the tempdb (on D drive) both show as the default device. I am very confused. User access to the information is fine. What gives?
View 2 Replies
View Related
Jul 21, 2006
Hi All,
I ran the following query to get the log file sizes for all databases:
select (size*8.00)/1024,filename from master..sysaltfiles
When I compared the results from this query with the transaction log properties in EM, not all of the sizes match.
For example, EM shows the transaction log size for tempdb to be 2 MB but the results of the query shows that transaction log size for tempdb is .5 MB. Which query can I run to get the numbers that would match between two? Thanks.
View 3 Replies
View Related
Jan 22, 2004
Wonder if anyone can help me out here.
I'm trying to set up a job to run overnight that mails me the size of all the databases on SQL Server.
The way I'm getting the size of the db's is by running the following in SQL Query Analyzer...
EXEC sp_MSforeachdb @command1="print '?' select cast(name as varchar(32)), round(size * 8 / 1.024,3) from ?..sysfiles"
Can anyone suggest a way that I can export the results of this to a text file that can then be mailed to me as part of a scheduled job.
Or can anyone suggest a better/easier way of doing this??
Thanks in advance. :)
View 6 Replies
View Related
Oct 1, 2007
Admin is kind of new to me. Could someone recommend how to reduce these file sizes?
In Dev, my MDF is 16 MB, LDF .5 MB.
I run a script to recreate the schema and insert rows on the Host.
When downloading a db backup from the Host and restoring it to Dev, my MDF is 24 MB, LDF 25 MB.
The same dataset grew by ~35 MB!!!
Any advice is welcome. Thank you. Rick
View 11 Replies
View Related
Jul 23, 2005
Can any one direct me to sources for best practices of field types and sizesto use for commonly used information such as address, names, city, businessnames ....Thanks, Brian
View 3 Replies
View Related
Feb 25, 2008
I have reporting services installed on a windows 2003 server. If i open a report directly on the server using Internet Explorer 7 and reportviewer and click the print button on the report viewer control. The print document lines up perfectly on the output page and prints beautifully. I have a winforms app that calls the same report as an IMAGE and then prints it. The size of the output on the print page is way off and the font is almost triple. I have it working in another clients environment with similar serve setup and same versions. I have tried different print drivers and different workstations but I think it has something to do with reporting services config. Can someone please help. I have set all margins in the .rdl file to 0 and when i create the PrinterSettings object I set all margins to zero again.
Thanks in advance.
Mike
View 3 Replies
View Related
May 31, 2007
Got 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
View 4 Replies
View Related
Jul 30, 2014
I am in the middle of capturing a workload to try and tune a SQL instance and was wondering what kinds of sizes people capture in terms of traces. I am only 1 day into a capture and I believe a typical workload would be a week long capture and I am already at 10GB of files. I am only capturing rpc_completed and sql_batch_completed.
What sizes of workloads do other people capture and then where do you analyse them, do you have particular dedicated server for this kind of thing as at present I am looking to use my local PC. Also what rollover file sizes do people tend to use, I am currently using 1GB.
View 7 Replies
View Related
Feb 26, 2015
I have one .mdf and two .ndf files on the same drive. The .mdf file size =275GB, one .ndf file size = 300GB and other .ndf file size = 135GB. Is this normal to have 3 different file size? if not what can I do to fix this? I don't have option to make all files to initial size equal to 300GB as a .ndf.If I have to add a .ndf file (in case of running out the above drive), what initial file size should I set up for new file on new drive? And how data gets distributed across all 4 files (including new .ndf on different drive)?
View 2 Replies
View Related
Oct 14, 2006
I've got a 9gig DB which when backed up using the gui comes to 5gigs.When backed using the SQL "backup database" command it comes to 20gigs.Can someone suggest a good reason for the difference?
View 2 Replies
View Related
