December 23, 2011 at 3:08 am
Hello all, i am currently creating a script to update my databse schema to change all collumns that are varchar to NVARCHAR but, before i can do this i need drop all indexes.
Does anyone know where to pull out the INDEX creation syntax out of the database so i can use this to rebuild all my inexes after updating the schema.
this is what i have so far to list the indexes
DECLARE @IndexInfo TABLE (index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
)
INSERT INTO @IndexInfo
exec sp_msforeachtable 'sp_helpindex ''?'''
select * from @IndexInfo
order by 1
***The first step is always the hardest *******
December 23, 2011 at 3:20 am
Ooh, I have some partial code on this somewhere. What you need is in sys.indexes, sys.index_columns, sys.columns, sys.objects, sys.data_spaces and sys.partition*. How easy it seems, there are a LOT of options possibly used, so this is actually a tricky task.
One idea: Use a source control tool to build the code for you?
December 23, 2011 at 3:37 am
Thanks, thought it might be that easy lol *** goes and cowers in a corner ***** :w00t:
***The first step is always the hardest *******
December 23, 2011 at 3:55 am
script out all the tables(only with schema) and in advanced tab, select "Script Indexes = True"
If your database have unexpected number of tables, then you can script out only required tables(only with schema) and its indexes.
----------
Ashish
December 23, 2011 at 4:01 am
Also note that you don't have to drop all indexes. You have to drop all indexes and constraints (primary, unique, foreign, check) that are on any of the string columns you're converting.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply