Need Stored Procedure to delete data in the database

Posted by Rama Sagar under Sql Server on 2/11/2013 | Points: 10 | Views : 2065 | Status : [Member] [MVP] | Replies : 2
Hi folks,

i need a store procedure to delete all the data in the particular tables of a database which is having foreign key and primary key constraints

Note:- i was having 40 tables in a database where i need to delete data in only 35 tables which are of foreign key constraints

ramasagar


Responses

Posted by: aswinialuri-19361 on: 2/12/2013 [Member] Starter | Points: 25

Up
0
Down
hiii
i think it is useful to you

http://blog.sqlauthority.com/2012/08/16/sql-server-curious-case-of-disappearing-rows-on-update-cascade-and-on-delete-cascade-part-1-of-2/

Thanks & Regards
Aswini Aluri


Mark as Answer if it helps you
Thanks&Regards
Aswini Aluri

Rama Sagar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sourabh_Mishra1 on: 2/12/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

This is the query which is helpful to find in ur database which tables are foregin keys and which having Primary key
SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
GO

Now This is the Stored Procedure which can delete ur table records

CREATE PROC USP_DeleteDataFromTables
(
@TName Varchar(50)
)
AS

DECLARE @Query NVARCHAR(50)
SET @Query ='DELETE FROM '+@TName
EXEC (@Query)

GO

Here when u execute this stored procedue u have to just send a paramater value for ur table name for example u have employee_master table so u wright
EXEC USP_DeleteDataFromTables 'Employee_Master'

Now automatically when u press F5 all data will be removed from that table.

if u want any more suggestion u can contact me on sourabh_mishra1@hotmail.com

Sourabh Mishra

Rama Sagar, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response