May 14, 2017 at 3:22 am
Hello,
I have a users tables and i need to delete all the users that created before 2016 (i have create_date column).
The issue is that this table have many dependencies and i need to delete the users also from the dependent tables, but i don't know what tables dependent and how to delete the users data from all tables.
Can you please advise?
Thanks in advance.
May 14, 2017 at 4:03 am
Hi 89netanel,
I assume you have primary key foreign key dependencies defined in your tables. If that is the case, then you can find the list of dependent tables by the following query:
SELECT OBJECT_NAME(parent_object_id) FROM sys.foreign_keys FK WHERE referenced_object_id = OBJECT_ID(N'<table name>')
Once you get the list, you can decide whether to proceed with the delete operation or not. Please let us know if I understood your requirement wrong.
Thanks.
May 14, 2017 at 6:00 am
Hello Debasis,
Thank you very much for your quick response!
i got this dynamic SQL query to retrieve a script that generate all the table that have a dependency to my table and to delete all the data in there for the proper users.
The question is if there is any workaround to delete data (i afraid that the tables also will have them own dependencies and than i will get a very hard life)
select
'delete a from '+
OBJECT_NAME(parent_object_id)
+ ' a join usermaster b
on a.user_id = b.id
where b.create_date < ''11-30-2016''
'
FROM sys.foreign_keys FK
WHERE referenced_object_id = OBJECT_ID(N'dbo.USERSTABLE')
order by OBJECT_NAME(parent_object_id) asc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply