Loop Through All Tables And Delete Records

I'm new to MsSql and I'm not sure if this can be done but I figured I'd ask before I want on my way with the current process.. I need to create a script that loops through all tabl

Solution 1:

sp_MSForEachTable is an undocumented stored procedure which will run a command for each table in the database:

USE MyDatabase

DECLARE@CorporationIdVARCHAR(50)
SET@CorporationId='52D3AEFE-8EBD-4669-8096-4596FE83BB36'DECLARE@SqlVARCHAR(MAX)
SET@Sql='
IF COL_LENGTH(''?'',''CorporationId'') IS NOT NULL
BEGIN
    DELETE FROM Web.?
    WHERE CorporationId = '''+@CorporationId+'''
END
'EXEC sp_MSForEachTable @Sql

Solution 2:

Here's another one... could easily be changed to a stored procedure...

Declare@corpID Nvarchar(256)
Set@corpID='xxx'

If      Object_ID('tempdb..#tables') IsNotNullDropTable #tables
CreateTable #tables (tID Int, SchemaName Nvarchar(256), TableName Nvarchar(256))

Insert  #tables
SelectRow_Number() Over (OrderBy s.name, so.name), s.name, so.name
From    sysobjects so
Join    sys.schemas s
        On  so.uid = s.schema_id
Join    syscolumns sc
        On  so.id = sc.id
Where   so.xtype ='u'And     sc.name ='CorporationId'Declare@SQL Nvarchar(Max),
        @schema Nvarchar(256),
        @table Nvarchar(256),
        @iterInt=1

While   Exists (Select1From    #tables)
BeginSelect@schema= SchemaName,
                @table= TableName
        From    #tables
        Where   tID =@iter

        If      Exists (Select1From    sysobjects o
                        Join    sys.schemas s1
                                On  o.uid = s1.schema_id
                        Join    sysforeignkeys fk
                                On  o.id = fk.rkeyid
                        Join    sysobjects o2
                                On  fk.fkeyid = o2.id
                        Join    sys.schemas s2
                                On  o2.uid = s2.schema_id
                        Join    #tables t
                                On  o2.name = t.TableName Collate Database_Default
                                And s2.name = t.SchemaName Collate Database_Default
                        Where   o.name =@tableAnd     s1.name =@schema)
        BeginUpdate  t
                Set     tID = (SelectMax(tID) From #tables) +1From    #tables t
                Where   tableName =@tableAnd     schemaName =@schemaSet@iter=@iter+1EndElseBeginSet@Sql='Delete t
                                From    ['+@schema+'].['+@table+'] t
                                Where   CorporationId = '''+@corpID+''''Exec    sp_executeSQL @SQL;

                Delete  t
                From    #tables t
                Where   tableName =@tableAnd     schemaName =@schemaSet@iter=@iter+1EndEnd

Solution 3:

You could run a query like this:

SELECT'DELETE FROM ['+ s.name +'].['+ t.name +'] WHERE CorporationId = ''52D3AEFE-8EBD-4669-8096-4596FE83BB36'''FROM sys.columns c
    innerjoin sys.tables t
        ON t.object_id= c.object_id
    innerjoin sys.schemas s
        ON s.schema_id = t.schema_id
where c.name ='CorporationId'

and then either copy and paste the results into a new query window and execute the new query or iterate over the results with a cursor and execute each result with the exec statement.

Solution 4:

Here is a Sql Fiddle that proves the below query

You can get the tables from this query:

SELECT Name, IsChecked =0INTO #Tables
FROM sys.Tables
WHEREEXISTS
(
  SELECT*FROM sys.columns 
  WHERE object_id = OBJECT_ID(sys.Tables.Name) AND sys.columns.Name ='blah'
) 

Then, you can create a dynamic query and execute it for the tables that you found

WHILE(SELECTCOUNT(*) FROM #Tables WHERE IsChecked =0) >0BEGINSELECT TOP 1@TableName= Name FROM #Tables WHERE IsChecked =0SET@DeleteQuery='DELETE FROM '+@TableName+' WHERE CorporationID = '''+@CorporationId+''''EXECUTE sp_executeSQL @DeleteQuery;
    UPDATE #Tables SET IsChecked =1WHERE Name =@TableNameEND