gpt4 book ai didi

SQL:查询许多具有相同列名但特定值结构不同的表

转载 作者:行者123 更新时间:2023-12-04 20:56:04 26 4
gpt4 key购买 nike

我正在清理 ERP,我需要删除对未使用的用户和用户组的引用。有很多外键约束,因此我想确保真正摆脱所有痕迹!

我发现这段简洁的代码可以在我的数据库中找到具有特定列名的所有表,在这种情况下让我们看看用户组:

select table_name from information_schema.columns
where column_name = 'GROUP_ID'

根据结果,我可以在 40 多个表格中搜索我未使用的 ID...但这很乏味。所以我想自动执行此操作并创建一个查询,循环遍历所有这些表并删除它在 GROUP_ID 列中找到 Unused_Group 的行。

在删除任何内容之前,我想可视化现有数据,因此我开始使用字符串连接构建类似这样的内容:

declare @group varchar(50) = 'Unused_Group'
declare @table1 varchar(50) = 'TABLE1'
declare @table2 varchar(50) = 'TABLE2'
declare @tableX varchar(50) = 'TABLEX'

select @query1 = 'SELECT ''' + rtrim(@table1) + ''' as ''Table'', '''
+ rtrim(@group) + ''' = CASE WHEN EXISTS (SELECT GROUP_ID FROM ' + rtrim(@table1)
+ ' WHERE GROUP_ID = ''' + rtrim(@group) + ''') then ''MATCH'' else ''-'' end FROM '
+ rtrim(@table1)

select @query2 = [REPEAT FOR @table2 to @tableX]...

EXEC(@query1 + ' UNION ' + @query2 + ' UNION ' + @queryX)

这给了我结果:

TABLE1  |  Match
TABLE2 | -
TABLEX | Match

这适用于我的目的,我可以在不更改任何其他代码的情况下为任何用户组运行它,当然很容易适应来自这些相同表的 DELETE,但对于 75 或所以我必须在用户和组之间处理表。

我遇到了 this link on dynamic SQL这一刻的强度和密度足以吓跑我……但我认为解决方案可能就在某处。

我非常熟悉 JS 和其他语言中的 FOR() 循环,这对于结构良好的数组来说是小菜一碟,但显然在 SQL 中就没那么简单了(我我仍在学习,但发现很多关于可用的 FOR 和 GOTO 解决方案的负面讨论......)。理想情况下,我有一个脚本可以查询以查找具有特定列名的表,如上查询每个表,并向我吐出一个匹配项列表,然后执行第二个类似的脚本来删除行。

谁能帮我指明正确的方向?

最佳答案

好的,试试这个,有三个变量;列、colValue 和预览。 Column 应该是您要检查相等性的列 (Group_ID),colValue 是您要查找的值 (Unused_Group),preview 应该是 1 以查看您将删除的内容,0 以删除它。

Declare @column     Nvarchar(256),
@colValue Nvarchar(256),
@preview Bit

Set @column = 'Group_ID'
Set @colValue = 'Unused_Group'
Set @preview = 1 -- 1 = preview; 0 = delete

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

-- Get all the tables with a column named [GROUP_ID]
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 = @column

Select *
From #tables

Declare @SQL Nvarchar(Max),
@schema Nvarchar(256),
@table Nvarchar(256),
@iter Int = 1

-- As long as there are tables to look at keep looping
While Exists (Select 1
From #tables)
Begin
-- Get the next table record to look at
Select @schema = SchemaName,
@table = TableName
From #tables
Where tID = @iter

-- If the table we're going to look at has dependencies on tables we have not
-- yet looked at move it to the end of the line and look at it after we look
-- at it's dependent tables (Handle foreign keys)
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
-- Move the table to the end of the list to retry later
Update t
Set tID = (Select Max(tID) From #tables) + 1
From #tables t
Where tableName = @table
And schemaName = @schema

-- Move on to the next table to look at
Set @iter = @iter + 1
End
Else
Begin
-- Delete the records we don't want anymore
Set @Sql = Case
When @preview = 1
Then 'Select * ' -- If preview is 1 select from table
Else 'Delete t ' -- If preview is not 1 the delete from table
End +
'From [' + @schema + '].[' + @table + '] t
Where ' + @column + ' = ''' + @colValue + ''''

Exec sp_executeSQL @SQL;

-- After we've done the work remove the table from our list
Delete t
From #tables t
Where tableName = @table
And schemaName = @schema

-- Move on to the next table to look at
Set @iter = @iter + 1

End
End

将其转换为存储过程只需将顶部的变量声明更改为存储过程创建,这样您就可以摆脱...

Declare @column     Nvarchar(256),
@colValue Nvarchar(256),
@preview Bit

Set @column = 'Group_ID'
Set @colValue = 'Unused_Group'
Set @preview = 1 -- 1 = preview; 0 = delete
...

并将其替换为...

Create  Proc DeleteStuffFromManyTables (@column Nvarchar(256), @colValue Nvarchar(256), @preview Bit = 1)
As
...

你会用...来调用它

Exec    DeleteStuffFromManyTable 'Group_ID', 'Unused_Group', 1

我对代码进行了注释以帮助您理解它在做什么;祝你好运!

关于SQL:查询许多具有相同列名但特定值结构不同的表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14820540/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com