Toad World® Forums

access and set SQL Server 2008 Filegroup at TABLE and INDEX level


#1

Hi,

I’m trying to change the Filegroup at the Table and Index levels for a given workspace/model. For some reason, I’m pulling back NULL values for the Filegroup and am not sure how to proceed.

My main goal is to be able to reference (read) the Filegroup name as well as be able to set the Filegroup (write).

Would someone mind showing me a snippet of javascript that makes this possible? I understand how to set up a macro, but I need help with the actual code to reference the Filegroup.

Thanks!


#2

var Filegroup = Model.Filegroups.GetObjectByName(“FindedFilegroupName”);

if (Filegroup != null)

Entity.SetLinkedObject(“Filegroup”, Filegroup);

I hope it helps. Let me know if you want better example.

Regards,

Vaclav

Hi,

this should work:

// Find a Filegroup in list of existing Filegroups


#3

Very helpful. Thanks for the information. In addition, I’m wondering if there’s a way to create a NULL filegroup, which would come up in Toad as “–Not Specified–”. Any ideas?

I’m not sure how to reference something like this through the GetObjectByName function:

var FileGroupRef = Model.Filegroups.GetObjectByName("–Not Specified–");

Basically, I want the ability to remove any references to filegroups, both for tables and indexes, and allow those to be assigned by the DBA (the default Filegroup).

Thanks,


#4

Hi,

there is no NULL filegroup. You can only remove assignment from entity.

In general, both Entity and Filegroup object knows the other object. That’s why it is necessary to remove both existing link. Locate filegroup and remove link to entity. Then locate entity and remove link to filegroup.

Sample script:

function main()
{
var e, ent, fgrp;
for(e=0; e<Model.Entities.Count; e++)
{
ent = Model.Entities.GetObject(e);

if(ent.Filegroup != null)
{
fgrp = ent.Filegroup;
// remove link to entity
fgrp.RemoveObjectLink(ent);
// remove link to filegroup
ent.RemoveObjectLink(fgrp);
}
}
}

Regards,

Vaclav


#5

Awesome! Thanks for your help. This worked perfectly. From your example, I was able to extract the logic and duplicate this for Indexes and PK.

Thanks again!


#6

my pleasure :slight_smile:

Have a great weekend,

Vaclav