delete in sql

Question

Hi,   I have a database 'Database1'. Inside that i have multi-company structure as Company1, Company2 & so on. I want to delete all the tables within Comapany1 including system tables, data tables with their data at one instance. Currently, i have to go to each table and press delete manually. Can anyone please help with  SQL query to carry out this task.   eg: Each type has 3 kinds of tables created. I want to delete entire things of Company1 dbo.Company1.MySettings.data dbo.Company1.MySettings.sidedata dbo.Company1.MySettings.audit dbo.Company1.MyContacts.data dbo.Company1.MyContacts.sidedata dbo.Company1.MyContacts.audit

sudipta sanyal · Answer

--IF YOU WANT TO DELETE ALL RECORDS FROM TABLE THEN PASS 1=1 OR IF YOU WANT TO DELETE RECORDS BASED ON CODITION THEN PASS column_name = value like user_id = 2 --SO YOUR QUERY LIKE  EXEC pr_delete_child_records 'parent_table', '1 = 1' FOR ALL  --SO YOUR QUERY LIKE  EXEC pr_delete_child_records 'parent_table', 'column_name = value' FOR ALL
CREATE  PROCEDURE [dbo].[pr_delete_child_records]  @tableName varchar(50), @criteria nvarchar(MAX)
AS BEGIN DECLARE @foreign_key_name VARCHAR(200), @foreign_table VARCHAR(200), @foreign_column VARCHAR(200), @parent_table VARCHAR(200), @parent_column VARCHAR(200), @child_criteria VARCHAR(MAX), @cSQL VARCHAR(MAX) DECLARE l_c CURSOR LOCAL FOR
SELECT p_table.name AS parent_table,         p_column.name AS parent_column,         Fk_table.name AS foreign_table,         fk_column.name AS foreign_column,         Fk_name.name AS foreign_key_name FROM dbo.sysforeignkeys FK   INNER JOIN dbo.syscolumns p_column ON FK.fkeyid = p_column.id AND FK.fkey = p_column.colid  INNER JOIN dbo.syscolumns fk_column ON FK.rkeyid = fk_column.id  AND FK.rkey = fk_column.colid  INNER JOIN dbo.sysobjects p_table ON FK.fkeyid = p_table.id  INNER JOIN dbo.sysobjects Fk_table ON FK.rkeyid = Fk_table.id  INNER JOIN dbo.sysobjects Fk_name ON FK.constid = Fk_name.id WHERE Fk_table.Name = @tableName
OPEN l_c
FETCH NEXT FROM l_c INTO @parent_table , @parent_column, @foreign_table , @foreign_column, @foreign_key_name  WHILE @@FETCH_STATUS = 0 BEGIN
SET @child_criteria = @parent_column + ' in (SELECT [' + @foreign_column + '] FROM [' +                                @foreign_table +'] WHERE ' + @criteria + ')'                                EXEC pr_delete_child_records @parent_table, @child_criteria PRINT @parent_table + ' ' + @child_criteria  FETCH NEXT FROM l_c INTO @parent_table , @parent_column, @foreign_table , @foreign_column, @foreign_key_name  END CLOSE l_c DEALLOCATE l_c
SET @cSQL = 'DELETE FROM [' + @tableName + '] WHERE ' + @criteria
PRINT @cSQL
EXEC (@cSQL)
END

Naitik Jani · Answer

Welcome..
Yes..you are right..
But that needs to be taken care by us which tables are referenced tables and which are primary tables but still will check if i can find another way that this list comes in way reference table first and last primary table.

Jasmine Nagrecha · Answer

Hi Naitik,
Firstly thank you so much for your prompt reply.
The drop query works well with most tables. However, for few types it throws error of foreign key constraint and for few permission not available errors.
But when i delete the base type, i can than delete the reference data.
Overall much eased task now. Thanks once again.
regards,
Jasmine N

Naitik Jani · Answer

Hi,
You can try this commands
SELECT 'DROP TABLE "' + TABLE_NAME + '"'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'company1%'
So you will get list of Table names with queries and you can select result and paste it in Query window and after executing script all tables will be dropped.
You can create script for same.
Hope this helps !!
Thanks,
Naitik