Delete Everything (not really)

  • 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?

  • Can you please eleborate your question?

    It's not clear from your question why you want to delete the key/value.

  • 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?



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • 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.

    Jayanth Kurup[/url]

  • 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

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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

  • 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/

  • 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)

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply