| Author |
Topic |
|
potinenir
Starting Member
19 Posts |
 Posted - 2003-07-15 : 14:33:58
|
| 1. How do we move the tables from one filegroup to another and also should we recreate the indexes on the table after the move?2. is there any dba related view/table to show all the table names and the row count of those tables? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-15 : 14:50:46
|
| 1. To move the table, you will need to create it on the other filegroup using the CREATE TABLE statement , but name it something else. Then copy the data over to this new table. Then apply all of your indexes and constraints on this new table. Then drop the old table. Then rename this new table to the name of the old table. 2. The first query below will give you a list of all of the tables in alphabetical order. The second query will write out the T-SQL commands that you need to get the row count of all of the tables. Just copy the output of the second query into a new window and run it.SELECT name FROM sysobjects WHERE type = 'U' and name <> 'dtproperties' ORDER BY NAMESELECT 'SELECT COUNT(*) AS ' + name + ' FROM ' + nameFROM sysobjects WHERE type = 'U' and name <> 'dtproperties' ORDER BY NAMETara |
 |
|
|
potinenir
Starting Member
19 Posts |
Posted - 2003-07-15 : 15:49:44
|
| this is the output after running the 2nd query.SELECT COUNT(*) AS 20030527 FROM 20030527SELECT COUNT(*) AS 20030528 FROM 20030528SELECT COUNT(*) AS 20030529 FROM 20030529SELECT COUNT(*) AS 20030530 FROM 20030530And this is the error i get when i run the output in a separate query analyzer. Any ideas of why??Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '20030527'.Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect syntax near '20030528'.Server: Msg 170, Level 15, State 1, Line 3Line 3: Incorrect syntax near '20030529'.Server: Msg 170, Level 15, State 1, Line 4Line 4: Incorrect syntax near '20030530'.Server: Msg 170, Level 15, State 1, Line 5 |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-15 : 15:53:31
|
| The column name is illegal; wrap it in brackets ( [20030527] )Jonathan{0} |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-15 : 15:53:33
|
| Try this instead:SELECT 'SELECT COUNT(*) AS [' + name + '] FROM ' + name FROM sysobjects WHERE type = 'U' and name <> 'dtproperties' ORDER BY NAMETara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-15 : 15:54:53
|
| Damn, beat by Jonathan again and this time by only 2 seconds. I'll have to stop eating my lunch at the same time as posting answers.Tara |
|
|
|
potinenir
Starting Member
19 Posts |
Posted - 2003-07-15 : 16:03:21
|
| here's the output and resultset for the querySELECT COUNT(*) AS [20030527] FROM 20030527Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '20030527'. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-15 : 16:14:09
|
| There's a pattern here potenir ...If one needs to escape an object name with brackets b/c it's all numerics, then what might you want to do to the table name to make it work?Jonathan{0} |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-15 : 16:22:27
|
| I think 2 seconds has GOT to be a recordBrett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-15 : 16:56:58
|
| SELECT 'SELECT COUNT(*) AS [' + name + '] FROM [' + name + ']'FROM sysobjects WHERE type = 'U' and name <> 'dtproperties' ORDER BY NAMETara |
|
|
|
potinenir
Starting Member
19 Posts |
Posted - 2003-07-16 : 10:24:09
|
| Regarding my previous question of moving tables between filegroups..is this procedure good to use?To move tables from one filegroup to another, 1. From Server Enterprise Manager,expand a server group; then expand a server.2. Expand Databases; then expand appropriate database.3. Click Tables; then right-click the table and choose 'Design Table'.4. Click 'Table and Index Properties' on the top menu and change the table filegroup.Does this invalidate the indexes or have any other performance degradations?? |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-16 : 10:38:22
|
I'll yield to Tara on this one ...  Jonathan{0} |
|
|
|
potinenir
Starting Member
19 Posts |
Posted - 2003-07-16 : 11:00:03
|
| Any ideas ??Thanks,Rajeev |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-16 : 12:08:40
|
quote:
Regarding my previous question of moving tables between filegroups..is this procedure good to use?To move tables from one filegroup to another, 1. From Server Enterprise Manager,expand a server group; then expand a server.2. Expand Databases; then expand appropriate database.3. Click Tables; then right-click the table and choose 'Design Table'.4. Click 'Table and Index Properties' on the top menu and change the table filegroup.Does this invalidate the indexes or have any other performance degradations??
I have never done it this way, so I don't have any idea. Using Enterprise Manager is something that I use as little as possible. I would suspect that the indexes are fine. But it's better to be safe than sorry, so recreate them.Tara |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-07-16 : 19:08:59
|
| Actually, you can do this even more easily. Drop the clustered index and recreate it on the new filegroup (or just create it if you don't have one). The data is physically stored according to the clustered index definition. You can then move the other indexes in the same manner, or keep them on the existing filegroup, it's your choice. It would be better to drop all the nonclustered indexes first; since they get rebuilt when you redo the clustered index, there's no point in rebuilding them a second time when you move them as well.EM probably does the same thing behind the scenes if you have a clustered index already, but the only way to know exactly is to start a trace that logs the statements being generated by EM, then perform the change. |
|
|
|
|
table moving