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

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

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