July 18, 2011 at 1:05 am
i have 1000 tables in my database.i want to send all of my tables to filegroup .what do i do??
July 18, 2011 at 2:35 am
Write to script to recreate the clustered index in the filegroup.
July 18, 2011 at 3:20 am
ok . i have this script :
create unique clustered index PK_AccountType On AccAccountType (ID) With (DROP_EXISTING=ON) ON ACC
go
create unique nonclustered index IX_AccountType ON AccAccountType ([AccTypeName] ASC, [BankId] ASC) With (DROP_EXISTING=ON) ON ACC
but i have 1000 tables and its very dificult to write script for all of them
please advice me in script????
can i have script that send all of table to another filegroup?
July 18, 2011 at 3:25 am
It's easy enough to write a query that uses the system tables to generate those create index statements for you.
Why do you want to move all 1000 tables to a new filegroup? Typically one splits tables across filegroups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2011 at 3:57 am
ok its easy for you but i dont know what do i do? i have 1000 tables and i want to send tables to 10 filegroups.forexample i have 300 tables that start with "Acc" and i want to send them to "AccFilegroup" or i have 400 tables that start with "Buy" and i want to send them to "BuyFileGroup".
now i want 1 script for tables that start with "ACC" to send them to"AccFileGroup" .
please give me script
please help me
July 18, 2011 at 4:37 am
please help me:crying:
please give me query
July 18, 2011 at 6:07 am
isnt anyone to help me??
July 18, 2011 at 7:21 am
a.e.ghorbani (7/18/2011)
isnt anyone to help me??
What have you done to investigate Gail's answer to you? I think you were pointed in the correct direction to look at the system tables. Do some searching on that.
http://qa.sqlservercentral.com/articles/Administration/queryingsystemtables/1906/
July 18, 2011 at 7:26 am
a.e.ghorbani (7/18/2011)
isnt anyone to help me??
Have some patience will you. Bumping your post twice in 2 hours is completely unnecessary.
We're all volunteers here, we all have our own jobs, own problems and help out here in our spare time. I'm not going to put questions here ahead of work for paying clients.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2011 at 10:26 pm
ok.Excuse me !Im very sorry.my boss wants me to do this project and i search very much but i cant find any answer to my question.i khnow , i can create index on filegroup and send one by one tables to filegrope but i want to send all of table to filegrope.
July 19, 2011 at 5:47 am
please help me!!!
i want 1 script that send 10 table from one groupe to another filegrope together . please advice me.
July 19, 2011 at 10:28 pm
Grasshopper
Any DBA will at some stage need to work under pressure
I just happen to have some code to move tons of files from filegroup A to B based on the filegroup ID.
It's usefull to understand it, the many joins is due to some junkies deciding to have 22 column composite indexes. (don't ask) developers...
set transaction isolation level read uncommitted
select 'CREATE ' +case when z.is_unique = 1 then 'UNIQUE' else '' end + ' ' +case when z.type_desc like 'NON%' then ' NONCLUSTERED ' else ' CLUSTERED ' end + ' INDEX ' + convert(nvarchar(200),z.name )
+ ' on ' +object_name(a.object_id) + '('
+ replace(replace('[' +col_name(a.object_id,a.column_id) +']' +','
+ Case when (a.is_included_column = 0 And b.is_included_column = 1 ) then (isnull(') INCLUDE ([' +col_name(a.object_id,b.column_id)+']','') ) else (isnull('[' +col_name(a.object_id,b.column_id)+']','')) end + ','
+ Case when (b.is_included_column = 0 And c.is_included_column = 1 ) then (isnull(') INCLUDE ([' +col_name(a.object_id,c.column_id)+']','') ) else (isnull('[' +col_name(a.object_id,c.column_id)+']','')) end + ','
+ Case when (c.is_included_column = 0 And d.is_included_column = 1 ) then (isnull(') INCLUDE ([' +col_name(a.object_id,d.column_id)+']','') ) else (isnull('[' +col_name(a.object_id,d.column_id)+']','')) end + ','
+ Case when (d.is_included_column = 0 And e.is_included_column = 1 ) then (isnull(') INCLUDE ([' +col_name(a.object_id,e.column_id)+']','') ) else (isnull('[' +col_name(a.object_id,e.column_id)+']','')) end + ','
+ Case when (e.is_included_column = 0 And f.is_included_column = 1 ) then (isnull(') INCLUDE ([' +col_name(a.object_id,f.column_id)+']','') ) else (isnull('[' +col_name(a.object_id,f.column_id)+']','')) end + ','
+ Case when (f.is_included_column = 0 And g.is_included_column = 1 ) then (isnull(') INCLUDE ([' +col_name(a.object_id,g.column_id)+']','') ) else (isnull('[' +col_name(a.object_id,g.column_id)+']','')) end + ','
+ Case when (g.is_included_column = 0 And h.is_included_column = 1 ) then (isnull(') INCLUDE ([' +col_name(a.object_id,h.column_id)+']','') ) else (isnull('[' +col_name(a.object_id,h.column_id)+']','')) end + ','
+ Case when (h.is_included_column = 0 And i.is_included_column = 1 ) then (isnull(') INCLUDE ([' +col_name(a.object_id,i.column_id)+']','') ) else (isnull('[' +col_name(a.object_id,i.column_id)+']','')) end + ','
+ Case when (i.is_included_column = 0 And j.is_included_column = 1 ) then (isnull(') INCLUDE ([' +col_name(a.object_id,j.column_id)+']','') ) else (isnull('[' +col_name(a.object_id,j.column_id)+']','')) end + ','
+ Case when (j.is_included_column = 0 And k.is_included_column = 1 ) then (isnull(') INCLUDE ([' +col_name(a.object_id,k.column_id)+']','') ) else (isnull('[' +col_name(a.object_id,k.column_id)+']','')) end + ','
+ Case when (k.is_included_column = 0 And l.is_included_column = 1 ) then (isnull(') INCLUDE ([' +col_name(a.object_id,l.column_id)+']','') ) else (isnull('[' +col_name(a.object_id,l.column_id)+']','')) end + ')',',,','') ,',)',')')
+ ' with (drop_existing=on) on [PRIMARY] '
from sys.index_columns a
--select * from sys.filegroups
left join sys.index_columns b
on a.object_id = b.object_id
and a.index_id = b.index_id
and a.key_ordinal < b.key_ordinal
left join sys.index_columns c
on b.object_id = c.object_id
and b.index_id = c.index_id
and b.key_ordinal < c.key_ordinal
left join sys.index_columns d
on c.object_id = d.object_id
and c.index_id = d.index_id
and c.key_ordinal < d.key_ordinal
left join sys.index_columns e
on d.object_id = e.object_id
and d.index_id = e.index_id
and d.key_ordinal < e.key_ordinal
left join sys.index_columns f
on e.object_id = f.object_id
and e.index_id = f.index_id
and e.key_ordinal < f.key_ordinal
left join sys.index_columns g
on f.object_id = g.object_id
and f.index_id = g.index_id
and f.key_ordinal < g.key_ordinal
left join sys.index_columns h
on g.object_id = h.object_id
and g.index_id = h.index_id
and g.key_ordinal < h.key_ordinal
left join sys.index_columns i
on h.object_id = i.object_id
and h.index_id = i.index_id
and h.key_ordinal < i.key_ordinal
left join sys.index_columns j
on i.object_id = j.object_id
and i.index_id = j.index_id
and i.key_ordinal < j.key_ordinal
left join sys.index_columns k
on j.object_id = k.object_id
and j.index_id = k.index_id
and j.key_ordinal < k.key_ordinal
left join sys.index_columns l
on k.object_id = l.object_id
and k.index_id = l.index_id
and k.key_ordinal < l.key_ordinal
inner join sys.indexes z
on a.object_id = z.object_id
and a.index_id = z.index_id
inner join sys.data_spaces x
on x.data_space_id = z.data_space_id
where a.key_ordinal = 1
and isnull(b.key_ordinal,2) =2
and isnull(c.key_ordinal,3) =3
and isnull(d.key_ordinal,4) = 4
and isnull(e.key_ordinal,5) = 5
and isnull(f.key_ordinal,6) = 6
and isnull(g.key_ordinal,7) = 7
and isnull(h.key_ordinal,8) = 8
and isnull(i.key_ordinal,9) = 9
and isnull(j.key_ordinal,10) = 10
and isnull(k.key_ordinal,11) = 11
and isnull(l.key_ordinal,12) = 12
and object_name(z.object_id) like 'xxx'
and x.data_space_id = 1
and z.index_id = 1
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply