循环遍历所有表并删除记录

2022-01-16 00:00:00 tsql sql-server sql-server-2008-r2

我是 MsSql 的新手，我不确定这是否可以完成，但我想在我想要使用当前流程之前我会先问一下..

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..

我需要创建一个脚本，循环遍历数据库中的所有表并删除 CorporationId = "xxx" 的行.有一些表没有此列，但在我的大约 50 个表中，只有一两个没有.

I need to create a script that loops through all tables in a database and deletes the rows where CorporationId = "xxx". There are a few tables that do not have this column, but of my ~50 tables, only 1 or two do not.

我可以用这个单独删除表中的记录:

I can individually delete the records in the table with this:

USE MyDatabase

DECLARE @CorporationId UniqueIdentifier
DECLARE @TokenId UniqueIdentifier
DECLARE @CompanyCode nChar(3)

SET @CorporationId = '52D3AEFE-8EBD-4669-8096-4596FE83BB36'

print 'Starting Web.GasOrder'

DELETE FROM Web.GasOrder
WHERE       CorporationId = @CorporationId

print 'Starting Web.GasOrderNumber'

DELETE FROM Web.GasOrderNumber
WHERE       CorporationId = @CorporationId

etc..

但是为每个表创建一个变得乏味.

But this is getting tedious creating one for each table.

当然，有些表是有关系的.

有没有一种简单的方法可以做到这一点，还是我需要为每个表手动完成?

Is there an easy way to do this or do I need to do it manually for each table?

更新

我尝试过的大多数选项都遇到了关系问题并给我一个错误.

Most of the options that I have tried run into problems with relationships and give me an error.

推荐答案

还有一个……可以很容易地改成存储过程……

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

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

If      Object_ID('tempdb..#tables') Is Not Null Drop Table #tables
Create  Table #tables (tID Int, SchemaName Nvarchar(256), TableName Nvarchar(256))

Insert  #tables
Select  Row_Number() Over (Order By 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),
        @iter Int = 1

While   Exists (Select  1
                From    #tables)
Begin

        Select  @schema = SchemaName,
                @table = TableName
        From    #tables
        Where   tID = @iter

        If      Exists (Select  1
                        From    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 = @table
                        And     s1.name = @schema)
        Begin
                Update  t
                Set     tID = (Select Max(tID) From #tables) + 1
                From    #tables t
                Where   tableName = @table
                And     schemaName = @schema

                Set     @iter = @iter + 1
        End
        Else
        Begin
                Set     @Sql = 'Delete t
                                From    [' + @schema + '].[' + @table + '] t
                                Where   CorporationId = ''' + @corpID + ''''

                Exec    sp_executeSQL @SQL;

                Delete  t
                From    #tables t
                Where   tableName = @table
                And     schemaName = @schema

                Set     @iter = @iter + 1

        End
End

相关文章