Modifying Table Size
Hi guys,
I am trying to modify my table sizes. I need to extend the size of certain columns in several tables and keep the data presently in those tables also.
On my first attempt I successfully made the changes but had no data after dropping the original tables. Then I had to restore from a back up and I was
back to where I started.
Does anyone know a reliable way of doing this with out having to create another DB and transferring the data after making the size changes to my tables ?
Thanks,
Alan
View Complete Forum Thread with Replies
Related Forum Messages:
Database Automatically Creates Xxx_Temp Table While Modifying / Updating Table Structure .
Hello friends, I am new to the SQL Server 2005 development. From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company. Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that, "There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider". where xxx is the table name. I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist. Thanks in advance. Any help will be appreciated.
View Replies !
Modifying Table Column
Can someone tell me the easiest/best way to make a modification to a table where you change a column constraint from not null to null? Would you rename the table, create the new one with the different column and then transfer the old data into the new definition? thanks
View Replies !
Modifying SYSJOBSTEPS Table
I have some rather large TSQL scripts I'd like to schedule as jobs. Unfortunately, the SysJobSteps table in the MSDB Database is limited to 3200 characters while I need it to be 5000. Does anyone know if it's possible to increase the size of this to allow for larger TSQL scripts? Thanks!
View Replies !
Modifying System Table
Quick Question: I want to change an execution time for 345 jobs and do not want to do it one by one in EM. I have changed the active_start_time and next_run_time in the sysschedules table but it is not showing up within the EM job folder under the next run date as being modified. Does anyone know what I am missing? TIA, Daimon
View Replies !
Modifying A Table From A View
I have two tables, a data table (MainTable) that contains a user initials and other empty rows and then i have a user table. I also have an inner join view with the initials from both tables as the joining field. I want the users to be able to modify the data table (MainTable) but also display the users name field. here is the sql of the view: SELECT dbo.MainTable.DateCreated, dbo.InitialsListing.Initials, dbo.MainTable.CustomerNumber, dbo.InitialsListing.AdvocateName, dbo.MainTable.Supervisor, dbo.MainTable.Complete, dbo.MainTable.FirstNotify, dbo.MainTable.SecondNotify, dbo.MainTable.ThirdNotify, dbo.MainTable.AdvocateInitials FROM dbo.InitialsListing INNER JOIN dbo.MainTable ON dbo.InitialsListing.Initials = dbo.MainTable.AdvocateInitials
View Replies !
Trouble Modifying A Table
Hi There, I am trying to add a new field to a table using enterprise manager by right clicking on the table, choosing design table and adding the new field/column. But when I choose save changes, I receive an error message: ------------------------------------------------------------ Errors were encountered during the save process. Some of the database objects were not saved. 'Comments' Table -Unable to modify table ODBC error: [Microsoft][ODBC SQL Server Driver][SQL SERVER] ... ... Cannot drop the table 'dbo.Comments' because it is being used for replication ----------------------------------------------------------- If it helps I am trying to add a binary (bit) field and a text field of 50 characters. I think the replication it refers to is a merge replication (not sure though is there a way i can find out in EM?) I have read some articles which refer to this problem and suggest using stored procedures but I really dont know how to do that, having never used them before. I need an idiots guide please! Let me know if I need to provide more info. Thanks.
View Replies !
Help On Comparing And Modifying A Resultset Or Table
HI everybody need help badly on the following have to recordsets with the following with the following SQL SELECT place, count(minus)as countminus order by place group by place THE RESULT WOULD BE PLACE countminus ABC 10 DEF 50 HIJ 5 KLM 2 MNO 0 MY OTHER QUERY IS SELECT plus, count(plus) as countplus GROUP BY plus ORDER BY plus THE RESULT WOULD BE: PLUS COUNTPLUS ABC 10 DEF 20 my problem is i need to add another column to the first resultset (if possible) or create a table with the same field and values of the first result set with the additional column OF THE second resultset COUNTPLUS... with this condition.. I need to loop with single row from the column PLACE on the first result set and compare it with the column PLUS on the second result set. if there is a value of the PLACE=PLUS get the corresponding value of the COLUMN COUNTPLUS AND WRITE it on the COUNTPLUS ON THE NEW TABLE.. ELSE THE VALUE IS 0 this i would do it up to the END OF FILE EOF... THE RESULT WOULD BE THIS..... PLACE countminus COUNTPLUS ABC 10 10 DEF 50 20 HIJ 5 0 KLM 2 0 MNO 0 0 I WAS WORKING ON THIS BUT COULDN'T GET THROUGH IT ANYTHING WOULD BE A BIG HELP FOR ME FROM YOU GUYS
View Replies !
Modifying Existing Table Schemas?
By default, I constructed my database using the dbo Schema. I have since created new Schemas, and want to change some of the tables from dbo. Is there a way to change the existing schema of a table without disrupting the data that is inside the table? For example: Current: dbo.Products Desired Change: NewSchemaName.Products Thanks.
View Replies !
Newbie: Modifying Table = Wrong Data In View?
Hi there, Completely new to the world of databases. I'm a designer who works primarily in Flash. In any case, I'm trying to manage an application that uses MS SQL and learn about the wonderful world of databases. Ok, I modified a table (e.g. I added a column called "Rate") that had associated views (created by another developer). Noticed that my application went a little wonky as some of my variables within my app took on the value of the data in the "Rate" column. I checked one of the views and noticed that a column within the view (e.g. TutorID) was assuming the values in the "Rate" column. Note: The column TutorID had been blank before the change to the table. I'm completely lost as to why this is happening. Do I need to rebuild the view? Can I just reset the original view? Thanks. Oh yeah, I'm using SQL4X Manager J from Mac Guru (if that helps).
View Replies !
Table Size And Database Size
Hi, i use this script that show me the size of each table and do the sum of all the table size. SELECT X.[name], REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows], REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved], REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data], REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size], REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused] FROM (SELECT CAST(object_name(id) AS varchar(50)) AS [name], SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows], SUM(CONVERT(bigint, reserved)) * 8 AS reserved, SUM(CONVERT(bigint, dpages)) * 8 AS data, SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size, SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused FROM sysindexes WITH (NOLOCK) WHERE sysindexes.indid IN (0, 1, 255) AND sysindexes.id > 100 AND object_name(sysindexes.id) <> 'dtproperties' GROUP BY sysindexes.id WITH ROLLUP) AS X ORDER BY X.[name] the problem is that the sum of all tables is not the same size when i make a full database backup. example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from? THX
View Replies !
Get &&"Invalid Cursor State&&" When Modifying A Table In SQL 2000
When I try to modify a table that I just created I get the following error message: - Unable to modify table ODBC error:[Mircrosoft][ODBC SQL Server Driver] Invalid cursor state. SP3 has been applied to SQL Server 2000. Can anyone help explain what is causing this error? There is sufficient space for the database and transaction log.
View Replies !
Trouble With Update Trigger Modifying Table Which Fired Trigger
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View Replies !
Table Size
Hello, How can i get the size of a table in sqserver 2000 ? How can read a image field ? Thanks for this answer.
View Replies !
Max Size Of A Table
Is there a maximum or optimum number of rows I should have in a table so that I can have fastest search queries. I am a novice programmer just developed something for my work place. The database has a table created by converting data from excel spreadsheets. There were 24 spreadsheets for 12 months each having approximately 500 rows. Designed this way the table will have approximately 24 * 500 = 12000 records. Should I consider redesigning the database to make searches faster
View Replies !
Table Size ;
Hi all, How can I find the exact size in the disk occupied by a TABLE ? When I execute "sp_spaceused" it returns the following parameters for a table reserved data index_size unused Which of the parameters I should consider to calculate the exact space occupied by the table. Thanks in advance, Hari Haran Arulmozhi
View Replies !
Max Table Size?
Is there a practical size limit, in MB's, of a table in SQL Server 6.5? Is there a size, that once exceeded, degrades performance signifigantly? I am speaking of raw megabytes. The table in question will consist of only 3 int columns but has the possiblity of becoming VERY LARGE (+1,000,000 rows). I am still in the design phase and can change my strategy if this will prove to be a problem. Thanks for any help!
View Replies !
Table Size.
hello all, I am trying to solve this problem for quite some time.. I was wondering if I can get some help.. These questions are all abt. MSSQL 6.5 1. Is there a limit on the size of the table ? 2. Does it make sense to have more tables if the size of the row size is more that the limit set by 6.5 or i should let have more rows in a different table with duplicate entries for a particular field. 3. What is the number of rows before the performance of a query starts getting affected.. Thanks.. Sachin.
View Replies !
Table Size
I am wondering if there is the limitation of maximum table size in SQL 6.5. I have a table with 2.6GB and 12,000,000 rows in SQL 6.5 database. Is this a problem? TIA, Stella Liu
View Replies !
Table Size In SQL 6.5
I'm trying to run a query or sp that will give me a list of tables and the number of rows in it. Is there any way for me to do this? I appreciate the assistance. Toni
View Replies !
Table Size
I created a same table on two different server with same data. I run sp_sapceuse on both server and I got following result it's useing 392 MB for table name rows reserved data index_size unused -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------ DUNS_SITE 100000 401288 KB 400000 KB 1264 KB 24 KB its using only 97.3 MB name rows reserved data index_size unused -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------ DUNS_SITE 100000 99720 KB 99376 KB 328 KB 16 KB WHY????? Thanks Manesh
View Replies !
Table Size
I have some question and would like ask experts: The largest table in our database eats up above 4G . we do "sp_spaceused" for this table.The length of all columns of this table ( just int, char, varchar, money ,numeric fields types) is about 200 bytes, and the table has around 1,300,000 rows, but the reserved spaced for this table is 4,800,000kb and the data space is around 4,600,00kb. How can average each row take 3.7kb ( the total size of all columns just 200 bytes)? Any other things I need check? Any one can give any suggestion what cause this problem? or it is normal? Thank you very much. Judy
View Replies !
SQL Table Size
Hello, If i want to know the size of table then how can i do it in SQL Server 2000 and in SQL Server 2005. -- how much amount of data can a table store in sql. -- On which thing the size of table depends. or Can anyone give me a introduction about the size of table in sql
View Replies !
Row Size Of Each Row In Each Table - Urgent
Hi. I am trying to get a row count of each row of each table in the database. Is that possible? Using a SP or UDFS? I dont want the column size of each table but the total datasize of each row.So for example if I have 5 rows each in 3 tables I need a query that will return 15 rows with the size of each row(size of all coumn data summed together). Thanks.
View Replies !
Find Table Size
Env: SQL Server 2000The following sql stmt seems to find a particular table's sizeprogrammatically:select top 1 [rows],rowcntfrom sysindexeswhere ID = object_id('aUserTable')and status = 0and used > 0However,a) I'm not 100% sure of its consistency;b) Both [rows] col and [rowcnt] col seems to produce same data, whichone is supposed to be more accurate (or more up to date)?TIA.
View Replies !
Table Size Count
Hi allPlease help me to find out table size in MS-SQLhow can I count or identify, this specific table is using some xyz kbof space of my hdd.thanks
View Replies !
Table Size Question
I've got a table with 180 million rows and 19 columns. There may be a need to add an additional 250 columns(all of them decimal (7,2). how prohibitive would this be from a performance standpoint? Many Thanks, Isaac
View Replies !
Calculating The Size Of A Table In A Db
If I have a database with a list of tables is there a way to calculate the size of each table individually and then calculate the size all the tables. If you have 1 table with say 10 rows and 3 columns and the width of the columns are of variable length you could do something like ( column1width + column2width + column3width ) * No.of Rows = Tablesize So my question is can I reference the column width of different columns in a table using sql ? Another issue is that some of the columns are different datatypes so I should be taking that into consideration as well. From searching the internet so far I have seen little on SQL showing how to reference column width in a table.
View Replies !
Work Table Row Size... Help !!
I have a large select query which runs OK on our live server. However it always fails on the test server. The message is: Work table row size 2082 which is greater than the maximum 2014. (or words to that effect.) The Server configurations are identical, apart from the test server has less memory. The databases and tables are identical in structure and the database on the test server has been refreshed with 'live' data from the live system and all indexes have been rebuilt. I can get the query to run, by removing columns from the select, but I dont understand why there are no problems in the live environment. Any advice or help would be greatly appreciated. Thanks in advance. Tom W
View Replies !
Find The Size Of A Table.
Hi, I liked to know if there are any stored procedures or sql scripts that will return the size of a table, to include all the data as well. I have been asked to do this and I'm not sure I understand how to calculate the size based on x # of rows of data and data page size, etc, etc, etc. Any information would be very helpfuly. Thanks and Happy Holidays. Joe
View Replies !
Maximum Table Size?
Hello, Does anyone knows what the maximum table size is? I'm asking this because I have a feeling that dbcc newalloc and dbcc checktable report more often errors on larger tables. Just recently I received an error 2529 on a table with over 17 million rows and about 3.9 GB large. Thanks, Stef
View Replies !
Table Size Calculation
Hi, I know this is a basic qn, but I would apprecoiate if someone can reply back. I have a table with 90 rows. The datatypes and lengths are as follows for the columns: varchar- 60 varchar-60 float-8 float-8 image-16 What is the formula for calculating the size of the table? Thanks in advance Karen
View Replies !
Average Size Of A Row In A Table
How do I find the average size of a row in a table? I need to calculate a row size in a number of tables, then sum those to find the average size of one record ( a hotel guest in this case), which includes entries in a dozen tables. Thanks
View Replies !
List Table Name And Size
Anybody 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
View Replies !
Average Row Size For A Table
I want to find out the average rwo size of a table in sql server 2000. How can i achive this requirment? Please let me know how can i do this? Thanks & regards, Dishant Sharma
View Replies !
Calculate Table Size
Hi all! How can I calculate the combined table size for the following: The Destination and the ShipmentWages tables on a database of a courier service have approximately 10,000 rows and 15,000 rows, respectively. The average row size of the Destination table is 4KB and that of the ShipmentWages table is 3KB. Thanks, ndba
View Replies !
Table Size Usage ( Bug ? )
I started to insert 200,000 records in an empty table and rolled back the insertion after it reached 190,000th record. The table swell by 8MB in the meanwhile. Even after the rollback was complete, the size of the table was 8MB with zero rows in it. Does SQL server not update the pages after a transaction is rolled back ? How do i shrink this table size now ?
View Replies !
Size Of Table(Urgent)
Hello, I've a database in Production db which is 300 MB and one of its table has 948900 rows then how can I know how much size this particular table is of. Thanks.
View Replies !
Table Size/Breakup
SQL 7 I have inherited a system where several of the tables have WAY TOO MANY fields (over 255) and performance, well, you know. I've been thinking about breaking the tables out into multiple tables and I know a view linking all these tables together can provide me with backward compatibility. I'm wondering if a view will truely be a greater gain than leaving it alone while we redesign. hhhmmmm Thoughts ? Craig
View Replies !
Total Row Size In A Table
Hi, I am getting this error when I try to run a script to create a table. The error reads as below The total row size (12488) for table exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added. Can somebody tell me what I have done wrong? thanks zoey
View Replies !
|
TRACKER
