September 25, 2003 at 7:46 am
My Problem Is This
I have Installed An SQL Server 2k With Collation Latin....1255 (Hebrew Support)
Thant I Have Restored Some Database Backed Up With SQL Server 7
After a a Week of Working With These Databases I Had To Change The Server Collation To Hebrew_CS_IS
To that I Had Backed Up All User Databases and Reinstalled the server since rebuilM.exe Failed.
After Restoring the databases I had run A script to Change DataBases Collation to Hebrew_CS_IS
It worked fine But It did Not Change The Varchar and Char Column Colation From The Latin....1255 To The Hebrew Collation.
I have Written a Query That Can Create the Nessary Commands to Change The Collation of a Column by using
alter table <tn> Alter Column <CN> Collate <CLN>
But When Tring to run the Result Comand I got Errors A bout Statisc, Indexes , Keys That are depended on the Collation.
So no I have To Drop All Indexes From A Database and then Rebuild them Again.
Does Any One Have A script that Create The Keys , Indexes Drop Na dCreate Script.
Thanks Gil Adi
September 25, 2003 at 3:46 pm
The following 2 queries will allow you to create a script to drop the constraints and indexes. I don't have one handy for actually creating a script to re-create them.
-- This will create alter table statements to drop the constraints.
SELECT 'ALTER TABLE ' + so.name + ' DROP CONSTRAINT ' + soc.name
FROM sysobjects soc
JOIN sysconstraints sc ON soc.id = sc.constid
JOIN sysobjects so ON sc.id = so.id
WHERE soc.type = 'C'
AND so.type = 'U'
-- This will create DROP INDEX statements.
SELECT 'DROP INDEX [' + so.name + '].[' + si.name + ']'
FROM sysobjects so
JOIN sysindexes si ON so.id = si.id
WHERE so.xtype = 'U' AND so.status >=0
AND si.status > 0
ORDER BY so.name, si.indid
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 28, 2003 at 10:31 am
Hi Gil,
well like Gary I have not script to recreate them, but here is what I use to drop
select
'drop index ' + o.name + '.' + i.name
from
sysindexes i
join sysobjects o
on i.id = o.id
and i.indid between 1 and 254
and o.type = 'u'
and i.name not like '_WA%'
Or:
select case when objectproperty(object_id(i.name), 'IsConstraint') = 1
then 'alter table ' + object_name(id) + ' drop constraint ' +
i.name
else 'drop index ' + object_name(id) + '.' + i.name
end
from sysindexes i
where i.indid between 1 and 254
and objectproperty(id, 'IsUserTable') = 1
and indexproperty (id, name, 'IsStatistics') = 0'
order by object_name(id), i.indid desc
in addition to Gary's solution it takes also care of statictics
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply