| Author |
Topic |
|
wardsan
Starting Member
48 Posts |
 Posted - 2005-03-15 : 07:03:10
|
| Folks,I have a problem in that a reporting user on a database creates tables on the fly. I have the database setup so index,data, text/images go into their own filegroups. However, when these tables are created, they go into the default filegroup. Is there a way I can intercept this so data, indexs, text go into their appropriate filegroups? ThanksSandra |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-03-15 : 07:07:57
|
| Your users shouldn't be creating tables like this.If you want to apply (some) degree of control, then set up the tables by the use of stored procedures, and revoke the user's permission to create them directly.Better than this, rework the application so that the users do not create tables.-------Moo. :) |
 |
|
|
wardsan
Starting Member
48 Posts |
Posted - 2005-03-15 : 08:37:45
|
| You're completely right, they shouldn't be. Unfortunately, this is a bought in application... This totally goes against our standards. I have no control over how the code is working... |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-03-15 : 08:48:57
|
| If you have no control then there's not a lot you can do. There's no method that I know of for intercepting the CREATE TABLE command on the way in.-------Moo. :) |
|
|
|
wardsan
Starting Member
48 Posts |
Posted - 2005-03-15 : 10:45:58
|
| I wrote a script based on indid in sysindexes to determine the filegroup. I will have them after the fact. Not sure if I can move a table with text/image columns that is populated though?? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-03-15 : 10:47:26
|
| You can shift the data by moving the clustered index into whatever filegroup you want it to be in. I'm not sure of any particular restrictions regarding text or images.-------Moo. :) |
|
|
|
wardsan
Starting Member
48 Posts |
Posted - 2005-03-16 : 03:40:57
|
| I am getting a bit lost. I need to be able to move heaps, tables with clustered indexes, all other indexes and text_image. In oracle I could just do a 'create table <new_table> as select * from <old_table>' and then do a rename to move it but this is not possible in sql server. Seems I need to extract the table definition somehow...I've no idea how to do this. Also, it doesn't seem possible to rebuild an index in a new filegroup, again I need the definition for the index. I am lost.... |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-03-16 : 05:43:16
|
| I think SELECT * INTO ... FROM ... Is probably functionally equivalent, but you would then need to recreate any indexes. Alternatively you could go into design table and table properties in Enterprise Manager, change the data and text filegroups, and it will (slowly) do the job for you behind the scenes.-------Moo. :) |
|
|
|
wardsan
Starting Member
48 Posts |
Posted - 2005-03-16 : 06:13:06
|
| I really need to automate this, thanks a million for your help. Is there anything similar for indexes? |
|
|
|
wardsan
Starting Member
48 Posts |
Posted - 2005-03-16 : 06:16:17
|
| I was a bit stupid, if I do a select * into....., I can't change the filegroup. I would still need the table definition. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-16 : 07:54:50
|
| sp_helpindex will give you index definitions, including unique, (non)clustered, and the columns. It's pretty easy to take its output and use it to drop and recreate indexes on a new filegroup.Text and image data can't be moved, BUT you can change the default filegroup for the database to the text/image filegroup, then go back and move the data and indexes to their respective filegroups.You should run Profiler and check the exact statement used by the application when it creates a table. If it specifies the filegroup explicitly, then you may have a little more work ahead of you. |
|
|
|
|
modifying filegroups