Script to genrerate index, constraints and trigger recreation codes

[meilan]

Gurus

I have a small database which has sever identity gap problems. I have read many articles about fixing the problem. Since I don't have sa right, so most of the scripts I can not use. This is what I proposed to do. Giving a identity column in table A, drop all the indexes, constraints and triggers in table A, also drop its FK table's trigger (if any), so when I do updates, the triggers will not fire. I am thinking about store the indexes, constraints and trigger information in temp tables, after I drop them and fix the identity values, create them back to the database sequentially. Do you agree with my approach? do you have any scripts(stored procedures, no perl, c scripts please) that can share with me regarding generate codes for recreation for indexes, constraints and triggers for a table (entire database maybe)? Thanks in advance.

Kevin

 

[qyllr]

kevin,

unfortunately, i have perl and ksh scripts...

the simplest thing to do if we are talking about 1 table is to do the following:

1. sp_helpconstraint table_name and copy the output to a file...

2. sp_helpindex table_name and copy the output to a file...

3. Recreate your constraint file to include the alter table, add and go lines... sample:

alter table table_name
add constraint constraint_name PRIMARY KEY nonclustered( column_name)
go

4. Recreate your index file... sample:

create unique clustered index index_name
on table name (column_name)
on 'default'
go

5. save the files as file_name.sql.

6. after, updating you can just fire the SQL scripts... don't forget the where clause and backup the data...

7. isql -Udbo_user_name -P -Sdatabase_name -ifile_name.sql -ofile_name.out...

8. remember to run your clustered index first, followed by the nonclustered ones... then apply the FK constraints...

make sure that your user account has dbo alias...if not you will not be able to drop database objects...

an alternative solution is to bcp out the data, re sequence the out file (i use perl to do this), drop the constraints/index, bcp in, re-index, apply constraints...

hope this helps,
q.

 

[meilan]

--
Dear Gyllr - IActually I feel much pain now using the approch I decided earlier. You mentioned the "
an alternative solution is to bcp out the data, re sequence the out file (i use perl to do this), drop the constraints/index, bcp in, re-index, apply constraints..."

Can you please share your Perl script and the steps with me? the method I chose before is almost impossible and took long time to finish. How does your perl scripts handles PK/FK issue?

Thanks SO MUCH !!

Kevin

 

[qyllr]

kevin,

my perl script is a bit different from your scenario... it only handles one table and adds one column...

if you want to follow the bcp out/in suggestion... do the following:

1. bcp out the file...

bcp databaseName..tableName out tableName.dat -UuserName -Ppassword -SserverName -c -t "|" -r "\n"

2. create a new table with the same schema as the table you are re-sequencing... only this time add a new identity column... this will generate the new sequence for you along with the old sequence... you will need to create two indices -- one for the new id and one for the old... you can use the sample from the previous posting...

create table newTableName(
columnName {int|smallint|tinyint|numeric(p,0)} identity,
...,
...
)

3. bcp in the file to the newTableName table... the new sequence will be generated automatically... the sample below has -b20000 to ensure that you don't run out of temp space...

bcp databaseName..newTableName in tableName.dat -UuserName -Ppassw

 

[qyllr]

kevin,

my perl script is a bit different from your scenario... it only handles one table and adds one column...

if you want to follow the bcp out/in suggestion... do the following:

1. bcp out the file...

bcp databaseName..tableName out tableName.dat -UuserName -Ppassword -SserverName -c -t "|" -r "\n"

2. create a new table with the same schema as the table you are re-sequencing... only this time add a new identity column... this will generate the new sequence for you along with the old sequence... you will need to create two indices -- one for the new id and one for the old... you can use the sample from the previous posting...

create table newTableName(
columnName {int|smallint|tinyint|numeric(p,0)} identity,
...,
...
)

3. bcp in the file to the newTableName table... the new sequence will be generated automatically... the sample below has -b20000 to ensure that you don't run out of temp space...

bcp databaseName..newTableName in tableName.dat -UuserName -Ppassword -SserverName -b20000 -c -t "|" -r "\n"

4. create an update SQL Script for the child table...

use databaseName
go

update childTable1Name
set childTable1Name.oldSequenceColumnName = newTableName.newSequenceColumnName
from childTable1Name, newTableName
where childTable1Name.oldSequenceColumnName = newTableName.oldSequenceColumnName
go

create a new script for the parent table just
tweek the SQL script above...

5. drop the safety triggers and FK constraints...

6. run your update SQL scripts...

7. you may need to re-index when you are done with all your tables...

please be careful... i hope you can test this in a test environment before you execute it to your target database...

q.

 

[meilan]

Thanks for taking time to help. Actually I am fixing things as you suggested but you probably know too, it is a PAIN ..

Kevin