Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2011-02-02 : 14:48:24
|
| any one got a gnarly script, or can point me in a good direction for creating one where i can generate a list of all the indexes on a table and the statements to create them?my senerio is this, every 2 months, i have to drop 12 tables, recreate them, and import a flat file into them.every time i do this, i have to generate create statements for all the indexes (using the tasks > generate scrips wizard)then drop the tables, create them import the data, and then create the indexes...i'd like to do the whole thing though SSIS, the indexing is the only thing holding me back. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-02 : 14:51:04
|
Why do you have to drop the 12 tables and recreate them?Yuu can truncate the structure and simply insert the new data (or the old data back into it for that matter)What is the purpose of dropping and recreating?
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2011-02-06 : 23:08:15
|
| well the tables are all 220 million rows. so yeah, keeping the indexes during the bulk insert might take a little like :)serious drag w/ indexes from what i've told. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-06 : 23:23:29
|
Look into partitioning. That isn't that much data. Dropping,recreating, reloading...I presume to keep your indexes up to date?Generate the script once and save it. Do the structures and indexes change that often that you have regenerate the code? .
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-07 : 03:51:09
|
| Here is a script that will generate the create index statements for you:http://www.sqlservercentral.com/scripts/Index+Management/31652/- LumbagoMy blog-> www.thefirstsql.com |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2011-02-07 : 11:44:33
|
| perfect Lumbago, thanks! |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2011-02-07 : 11:54:22
|
| One thing about this lumbago, any way to pick up the INCLUDE parameters on the index? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-08 : 05:44:36
|
| Well...the included columns are specified in sys.index_columns with the bit-column "is_included_column" so maybe you can work something out...? The ssc-script will have to be modified to fit the "new" naming of sys-tables but I'm sure you'll manage :)- LumbagoMy blog-> www.thefirstsql.com |
|
|
|
|
|
|
|
|
create index script