June 29, 2006 at 11:04 am
Hi there,
I need some help regarding 2 issues:
1. I need to retrieve the previous primary key index (this is generated automatically by sql server) of all the tables in a db instance and drop it.
2. After dropping all this pk indexes I need to re-create a new PK indexes with my given predefined name.
I need is a sql statement in order to run this thru a jdbc driver invoke thru an application.
Your help is greatly appreciated.
Thanks
June 29, 2006 at 12:58 pm
I have a couple of questions for you. First, is there a reason why you would allow an application to drop/recreate the Primary Keys on all tables in your database? Creating a Primary Key also creates a clustered index by default. Depending on the size of your tables, this could take a significant amount of time to re-create every PK in your DB. This sounds to me like it should be a one-time maintenance task so that you can get your PK names how you want them. Second, where will the new 'predefined' name come from?
June 29, 2006 at 1:24 pm
John,
1. Dropping all the primary keys will be at least a one time task in order for us to fix an old problem. We found out that some of our customers does not have a primary key on some of their table and worse we found out it is not consistent which table does or does not have a primary key defined. In order for us to fixed the problem, it is better to drop all existing primary keys on all table in the db. After that we need to recreate all primary keys to a predefined ones (see step 2).
2. In order for us to recreate all the primary key, I'm doing a combination of using our db utility (written in java) that can read a list of table names and it's corresponding primary key name in a file and create the necessary sql statement and execute that statement thru jdbc. If you have a better solution, please let me know.
I've tried this constructing this sql but I'm having a problem with using the variables. Here is the sql and I'm having a problem in the line "ALTER TABLE @sysname DROP CONSTRAINT @idxname". Thanks for replying so promptly, I really appreciate it and your time.
DECLARE @sysid int,
@sysname varchar(50),
@idxname varchar(50)
DECLARE mycursor CURSOR STATIC
GLOBAL SCROLL
FOR
SELECT so.id, so.name, si.name
FROM sysobjects AS so
INNER JOIN sysindexes AS si ON (so.id = si.id)
WHERE so.xtype = 'U' AND si.indid = 1
ORDER BY so.name
OPEN mycursor
FETCH NEXT FROM mycursor
INTO @sysid, @sysname, @idxname
WHILE @@FETCH_STATUS = 0
BEGIN
ALTER TABLE @sysname DROP CONSTRAINT @idxname
FETCH NEXT FROM mycursor
INTO @sysid, @sysname, @idxname
END
CLOSE mycursor
DEALLOCATE mycursor;
June 29, 2006 at 1:39 pm
Turn to dynamic SQL.
DECLARE @sysid int,
@sysname varchar(50),
@idxname varchar(50),
@sql varchar(500)
DECLARE mycursor CURSOR STATIC
GLOBAL SCROLL
FOR
SELECT so.id, so.name, si.name
FROM sysobjects AS so
INNER JOIN sysindexes AS si ON (so.id = si.id)
WHERE so.xtype = 'U' AND si.indid = 1
ORDER BY so.name
OPEN mycursor
FETCH NEXT FROM mycursor
INTO @sysid, @sysname, @idxname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER TABLE ' + QUOTENAME(@sysname) + ' DROP CONSTRAINT ' + QUOTENAME(@idxname)
EXEC (@sql)
FETCH NEXT FROM mycursor
INTO @sysid, @sysname, @idxname
END
CLOSE mycursor
DEALLOCATE mycursor
N 56°04'39.16"
E 12°55'05.25"
June 29, 2006 at 1:59 pm
Peter,
Tried your sql and it works!
Thanks you very much for you help and time.
God Bless you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply