August 16, 2011 at 7:47 pm
I have a key that is added to every single transaction in the system (similar to this: [SUBSTRING(REPLACE(NEWID(),'-',''),1,32)]. What I need is a script that will delete that key EVERYWHERE is is found in the database. Anyone have any idea on how to do that?
August 16, 2011 at 11:42 pm
Can you please eleborate your question?
It's not clear from your question why you want to delete the key/value.
August 17, 2011 at 1:05 am
Yes, please explain this further as it is not clear what you want: For example, do you want to change the table's definitions to have the column removed from the keys and/or tables or do you want to remove all rows from any tables having a particular value in one of their columns, or in one of their key columns or in one of the columns referencing a particular value?
August 17, 2011 at 1:17 am
http://msdn.microsoft.com/en-us/library/ms186973.aspx
Assuming by key you mean primary and foreign keys the above link explains cascading.
You could also have a DMl trigger on the table which perform the same action but gives you much more control.
However be very careful using these options as they have can easily mess up your system by Accident . A simple delete without a where clause could wipe out the entire data.
August 17, 2011 at 5:04 am
If I'm right you want to search for something and replace it in every column in every user table.
Ok, if you really determined, that is for you:-D:
DECLARE @sql NVARCHAR(4000)
DECLARE cupd CURSOR FAST_FORWARD
FOR
SELECT N'update ' + schema_name(st.[schema_id]) + '.' + st.name +
' set ' + sc.name + ' = SUBSTRING(REPLACE(' + sc.name + ',''-'',''''),1,32) ' +
' where CHARINDEX(''-'',' + sc.name + ',0) > 0' as [sql]
from sys.columns sc
join sys.types tp on tp.system_type_id = sc.system_type_id
join sys.tables st on st.[object_id] = sc.[Object_Id]
where tp.name in ('varchar','char','nvarchar','nchar')
and st.[type] = 'U'
OPEN cupd
FETCH NEXT FROM cupd INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @sql
EXEC (@sql)
FETCH NEXT FROM cupd INTO @sql
END
CLOSE cupd
DEALLOCATE cupd
August 17, 2011 at 7:59 am
By Keys, I do mean PRIMARY & FOREIGN keys.
The initial order is put into the system with a primary key for that record. That PK is now used in about 12 other tables as a FK linking the records. I need to delete the row with the PK and all subsequent rows where the FK = PK
August 17, 2011 at 8:09 am
If you have cascading deletes setup on your relationships just delete the parent. If you don't then you will have to delete the records from ALL the children first and then delete the parent.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 17, 2011 at 8:14 am
As you ingoring the forum advise to present your question in a polite way, I can only show you a generic sample:
DELETE fk
FROM TableWithFK fk
JOIN TableWithPK pk ON pk.PkColumn = fk.FkColumn
WHERE pk.PkColumns = 'SomeKey'
DELETE TableWithPK WHERE PkColumns = 'SomeKey'
Please Note: WHERE clause will only be required if you want to delete the record for particular key(s)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply