Dynamic Index Drop and Create

I am working on a data warehouse that is SQL Server 2000 based and what
I need to do is create two stored procs that do the following psuedo
code. I can write it myself but if someone has already written it then
why write it again or if you know of solutions that are close to what I
want that would be a good start. The reason that these need to be in
two seperate procs is that I want to seperate out the Drop and Creates
from the stored procedure that is loading the table. Instead of
hardcoding the drops and creates I want this procedure to be more
dynamic so that as we add indexes for tunning we don't have to maintain
our stored procedures.
proc_DropIndexes @.DatabaseName, @.TableName
- Write all necessary current index information to a work table for
@.DatabaseName and @.TableName to allow for recreation
- Drop all current indexes for @.DatabaseName and @.TableName
proc_CreateIndexes @.DatabaseName, @.TableName
- Read all necessary current index information from a work table for
@.DatabaseName and @.TableName to allow for recreation
- Create all indexes for @.DatabaseName and @.TableName
Thanks,
SpencerThe following appear to be good starts...
http://www.code-magazine.com/articl...b9cc0d4526e1913

Dynamic Index Drop and Create

I am working on a data warehouse that is SQL Server 2000 based and what
I need to do is create two stored procs that do the following psuedo
code. I can write it myself but if someone has already written it then
why write it again or if you know of solutions that are close to what I
want that would be a good start. The reason that these need to be in
two seperate procs is that I want to seperate out the Drop and Creates
from the stored procedure that is loading the table. Instead of
hardcoding the drops and creates I want this procedure to be more
dynamic so that as we add indexes for tunning we don't have to maintain
our stored procedures.
proc_DropIndexes @.DatabaseName, @.TableName
- Write all necessary current index information to a work table for
@.DatabaseName and @.TableName to allow for recreation
- Drop all current indexes for @.DatabaseName and @.TableName
proc_CreateIndexes @.DatabaseName, @.TableName
- Read all necessary current index information from a work table for
@.DatabaseName and @.TableName to allow for recreation
- Create all indexes for @.DatabaseName and @.TableName
Thanks,
SpencerThe following appear to be good starts...
http://www.code-magazine.com/articleprint.aspx?quickid=0301101&printmode=true
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/1f4dd406fa18d676/8b9cc0d4526e1913?lnk=st&q=scripting+sql+server+indexes+create+drop&rnum=16&hl=en#8b9cc0d4526e1913

Dynamic create table, create index based upon a given database

Can I dynamically (from a stored procedure) generate
a create table script of all tables in a given database (with defaults etc)
a create view script of all views
a create function script of all functions
a create index script of all indexes.
(The result will be 4 scripts)

Arno de Jong,
The Netherlands.The following will create a script for each type of object:

EXEC XP_CMDSHELL '"C:\Program Files\Microsoft SQL
Server\MSSQL\Upgrade\SCPTXFR" /I /D DATABASE_NAME /S SERVER_NAME /F
C:\PATH_NAME'

--
David Portas
----
Please reply only to the newsgroup
--

"A.M. de Jong" <arnojo@.wxs.nl> wrote in message
news:bpkpq8$7vi$1@.reader10.wxs.nl...
> Can I dynamically (from a stored procedure) generate
> a create table script of all tables in a given database (with defaults
etc)
> a create view script of all views
> a create function script of all functions
> a create index script of all indexes.
> (The result will be 4 scripts)
> Arno de Jong,
> The Netherlands.