August 1, 2008 at 2:33 pm
I am having some serious trouble figuring out how to do a request given to me. We have a strongly FKed db that they just told me I need to make a script to delete data dynamically. The idea confuses my just thinking about it. so there will be a stored procedure and I am supposed to delete all children and so on. They would be sending into the sp: table name, guid, primary key field.
First I thought about using cascade delete but some of the tables are not normalized like they should be to allow cascade. So it allows cascade for about 60% of the db but now I have to have a delete script for the rest. All I want to do is show them this solution will not work. I need to have something that works before I can do that. Right now I am using the cursor below and it is up to 10 sec on my local box.
CREATE PROCEDURE PROC_CheckAndDelete (@TableName varchar(50), @TableOwner varchar(50), @PrimaryKeyValue varchar(100),
@IsPrimaryKeyValueTypeString bit, @RetMsg tinyint output)
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
DECLARE @f_quqlifier sysname,
@f_owner sysname,
@f_tablename sysname,
@f_columnname varchar(100),
@strsql varchar(500),
@p_quqlifier sysname,
@p_owner sysname,
@p_tablename sysname,
@p_columnname sysname
SET @RetMsg = 0
DECLARE @fkeys TABLE
(p_quqlifier sysname,
p_owner sysname,
p_tablename sysname,
p_columnname sysname,
f_quqlifier sysname,
f_owner sysname,
f_tablename sysname,
f_columnname varchar(100),
key_seq smallint,
updaterule smallint,
deleterule smallint,
fk_name sysname,
pk_name sysname,
diff sql_variant)
-- Collecting all foreign key information
INSERT INTO @fkeys EXEC sp_fkeys @TableName, @TableOwner
----*************************************************************************************************************
----Checking whether any dependants exist.
----*************************************************************************************************************
DECLARE cur_fkeys CURSOR
FOR
SELECT p_quqlifier, p_owner, p_tablename, p_columnname, f_quqlifier, f_owner, f_tablename, f_columnname
FROM @fkeys
WHERE deleterule = 1
--
OPEN cur_fkeys
FETCH NEXT FROM cur_fkeys INTO @p_quqlifier, @p_owner, @p_tablename, @p_columnname, @f_quqlifier, @f_owner, @f_tablename, @f_columnname
WHILE (@@FETCH_STATUS=0)
BEGIN
DECLARE @f_tablePrimaryKey as varchar(100)
SET @f_tablePrimaryKey = @f_tablename + 'id'
SET @strsql = 'SELECT '+@f_tablename + @f_tablePrimaryKey + ' FROM '
SET @strsql = @strsql + @f_quqlifier + '.' + @f_owner + '.' + @f_tablename
IF (@IsPrimaryKeyValueTypeString=1)
SET @strsql = @strsql + ' WHERE ' + @f_columnname + ' = ' + CHAR(39) + @PrimaryKeyValue + CHAR(39)
ELSE
SET @strsql = @strsql + ' WHERE ' + @f_columnname + ' = ' + @PrimaryKeyValue
-- If records exist, should stop the execution and make the return values as "Dependant exist. cannot delete".
INSERT INTO [Deleteholder]
([TableName]
,[FieldName]
,[PrimaryKey]
,[deleteSequence])
VALUES
(@f_tablename
,@f_tablePrimaryKey
,@P_Value
,1)
END
FETCH NEXT FROM cur_fkeys INTO @p_quqlifier, @p_owner, @p_tablename, @p_columnname, @f_quqlifier, @f_owner, @f_tablename, @f_columnname
END
August 1, 2008 at 3:15 pm
Take a look at these threads:
Truncate All Tables
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341
Find Table Reference Levels
August 1, 2008 at 3:41 pm
Both those scripts look good but I have a few problems.
The user using the sp will not have permissions to alter a table. So I can not disable FK and triggers. The other script takes me to the place I was before. Which is one level under the parent. I need to see the children of the children to delete from the bottom up.
Here is some psuedo code
Proc (@TableName varchar, @TheGuid unique identifier, @PrimaryKeyField varchar)
Get FK's from table and start looping
SP_Fkeys @TableName
1) Store Table, field, guid for retreival later
2) Once I have that first FK I need to start the loop over to see the children of the FK table
3) At the bottom of the branch start deleting and then work my way back up.
August 1, 2008 at 4:06 pm
JKSQL (8/1/2008)
Both those scripts look good but I have a few problems.The user using the sp will not have permissions to alter a table. So I can not disable FK and triggers. The other script takes me to the place I was before. Which is one level under the parent. I need to see the children of the children to delete from the bottom up.
Here is some psuedo code
Proc (@TableName varchar, @TheGuid unique identifier, @PrimaryKeyField varchar)
Get FK's from table and start looping
SP_Fkeys @TableName
1) Store Table, field, guid for retreival later
2) Once I have that first FK I need to start the loop over to see the children of the FK table
3) At the bottom of the branch start deleting and then work my way back up.
The user EXECuting the stored procedure does not need rights to the tables (unless you are using dynamic SQL - which may be the case). If that is the case, I wonder what would have if you setup the procedure to EXECUTE AS with a privileged user account (probably not a recommended method, but - maybe?).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 1, 2008 at 5:22 pm
JKSQL,
Are you attempting to cleanup orphaned child rows, or are you deleting all instances of a given PK (guid) value throughout your schema (from children up to parent)?
If you are deleting all occurrences of a given PK (guid) value, my next question would be why? Is there no value to retaining history in the database?
Ken
August 2, 2008 at 12:19 pm
Yes in this case there is no need to retain history. they are trying to write a program that will work on SQL express 2K5. Since there is a 4 g limit they want the ability to delete all history. There will be no need for an audit trail if guess. I know that sounds weird but that is what I am told. The execute as may be what I need to do to make this work. I have been thinking about this since yesterday and I do not think the above code will be efficient. When a user say is deleting a transaction I do not want them to have to sit there for 30 seconds to delete one transaction.
Is there anyway to create a parent child table on relationships where I can get to the bottom level quickly possibly without a cursor?
I really appreciate the help on this one because I am not getting anywhere. You guys have all had some good ideas. I think I can use them all but I need that piece of logic to get me to the base table.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply