I have about 50 tables with created-by and changed-by referencing the user table. When I want to delete the user, I would like to create a list of what tables this user has data in, so one can go delete/edit everything belonging to said user. Is there any way of doing this in SQL?
我有大约50个表创建和更改 - 通过引用用户表。当我想删除用户时,我想创建一个这个用户有数据表的列表,这样就可以删除/编辑属于所述用户的所有内容。在SQL中有没有办法做到这一点?
6 个解决方案
#1
Not exactly sure of what you're going for here..
不确定你要去的地方..
If you want a list of foreign keys referencing a table try this question How can I list all foreign keys referencing a given table in SQL Server?
如果需要引用表的外键列表,请尝试此问题如何列出引用SQL Server中给定表的所有外键?
#2
You can use cascaded delete.
您可以使用级联删除。
#3
Have you tried this one http://web.archive.org/web/20080511142245/http://sqlserver2000.databases.aspfaq.com:80/schema-how-do-i-find-all-the-foreign-keys-in-a-database.html
你试过这个吗http://web.archive.org/web/20080511142245/http://sqlserver2000.databases.aspfaq.com:80/schema-how-do-i-find-all-the-foreign-keys -in-A-database.html
#4
SQL 2005+ provides a number of system views like sys.tables and sys.foreign_key_columns which may help you.
SQL 2005+提供了许多系统视图,如sys.tables和sys.foreign_key_columns,它们可以帮助您。
SELECT
pt.name AS ParentTable,
pc.name AS ParentColumn,
rt.name AS ReferencedTable,
rc.name AS ReferencedColumn
FROM sys.foreign_key_columns fkc
INNER JOIN sys.tables pt ON pt.object_id = fkc.parent_object_id
INNER JOIN sys.columns pc ON pc.column_id = fkc.parent_column_id AND
pc.object_id = fkc.parent_object_id
INNER JOIN sys.tables rt ON rt.object_id = fkc.referenced_object_id
INNER JOIN sys.columns rc ON rc.column_id = fkc.referenced_column_id AND
rc.object_id = fkc.referenced_object_id
#5
this will list all tables where your ID exists:
这将列出您的ID所在的所有表:
DECLARE @Query varchar(1000)
DECLARE @MaxRow int
DECLARE @CurrentRow int
DECLARE @CurrentTable varchar(500)
DECLARE @UserID int
SET @UserID=???
CREATE TABLE #Rows
(
RowID int not null primary key identity(1,1)
,TableWithForeignKey varchar(500)
)
CREATE TABLE #Temp
(
RowValue int
)
INSERT INTO #Rows
(TableWithForeignKey )
select
t.name as TableWithForeignKey --, fk.constraint_column_id as FK_PartNo , c.name as ForeignKeyColumn
from sys.foreign_key_columns as fk
inner join sys.tables as t on fk.parent_object_id = t.object_id
inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where fk.referenced_object_id = (select object_id from sys.tables where name = '????????')
--order by TableWithForeignKey, FK_PartNo
SELECT @MaxRow=@@ROWCOUNT
SET @CurrentRow=0
SELECT * FROM #Rows
WHILE @CurrentRow<@MaxRow
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT @CurrentTable=TableWithForeignKey FROM #Rows WHERE RowID=@CurrentRow
--SET @Query='DELETE FROM '+@CurrentTable+' WHERE UserID='+CONVERT(varchar(20),@UserID)
SET @Query='SELECT COUNT(*) FROM '+@CurrentTable+' WHERE YourIDhere='+CONVERT(varchar(20),@UserID)
PRINT @Query
INSERT INTO #Temp
EXECUTE (@Query)
IF NOT EXISTS (SELECT RowValue FROM #Temp WHERE RowValue>0)
BEGIN
PRINT 'no matches found'
DELETE #Rows WHERE RowID=@CurrentRow
END
ELSE
BEGIN
PRINT 'matches found!!'
END
DELETE #Temp
END
--list all tables where the ID exists
SELECT * FROM #Rows
#6
A couple of methods for finding the tables that reference your User table through foreign key relationships have been posted and here's another: http://www.sqlservercurry.com/2009/03/simple-stored-procedure-to-find-foreign.html
通过外键关系找到引用User表的表的几种方法已经发布,这是另一种方法:http://www.sqlservercurry.com/2009/03/simple-stored-procedure-to-find-foreign。 HTML
Once you get the list of tables, you're going to have to write the SQL to determine which tables have records for a specific user. There's no way around that. Since the queries will all look the same, you could paste the table list into Excel, use a formula to build the queries, then paste the queries back into SSMS.
获得表列表后,您将不得不编写SQL以确定哪些表具有特定用户的记录。没有办法解决这个问题。由于查询看起来都一样,您可以将表格列表粘贴到Excel中,使用公式来构建查询,然后将查询粘贴回SSMS。
#1
Not exactly sure of what you're going for here..
不确定你要去的地方..
If you want a list of foreign keys referencing a table try this question How can I list all foreign keys referencing a given table in SQL Server?
如果需要引用表的外键列表,请尝试此问题如何列出引用SQL Server中给定表的所有外键?
#2
You can use cascaded delete.
您可以使用级联删除。
#3
Have you tried this one http://web.archive.org/web/20080511142245/http://sqlserver2000.databases.aspfaq.com:80/schema-how-do-i-find-all-the-foreign-keys-in-a-database.html
你试过这个吗http://web.archive.org/web/20080511142245/http://sqlserver2000.databases.aspfaq.com:80/schema-how-do-i-find-all-the-foreign-keys -in-A-database.html
#4
SQL 2005+ provides a number of system views like sys.tables and sys.foreign_key_columns which may help you.
SQL 2005+提供了许多系统视图,如sys.tables和sys.foreign_key_columns,它们可以帮助您。
SELECT
pt.name AS ParentTable,
pc.name AS ParentColumn,
rt.name AS ReferencedTable,
rc.name AS ReferencedColumn
FROM sys.foreign_key_columns fkc
INNER JOIN sys.tables pt ON pt.object_id = fkc.parent_object_id
INNER JOIN sys.columns pc ON pc.column_id = fkc.parent_column_id AND
pc.object_id = fkc.parent_object_id
INNER JOIN sys.tables rt ON rt.object_id = fkc.referenced_object_id
INNER JOIN sys.columns rc ON rc.column_id = fkc.referenced_column_id AND
rc.object_id = fkc.referenced_object_id
#5
this will list all tables where your ID exists:
这将列出您的ID所在的所有表:
DECLARE @Query varchar(1000)
DECLARE @MaxRow int
DECLARE @CurrentRow int
DECLARE @CurrentTable varchar(500)
DECLARE @UserID int
SET @UserID=???
CREATE TABLE #Rows
(
RowID int not null primary key identity(1,1)
,TableWithForeignKey varchar(500)
)
CREATE TABLE #Temp
(
RowValue int
)
INSERT INTO #Rows
(TableWithForeignKey )
select
t.name as TableWithForeignKey --, fk.constraint_column_id as FK_PartNo , c.name as ForeignKeyColumn
from sys.foreign_key_columns as fk
inner join sys.tables as t on fk.parent_object_id = t.object_id
inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where fk.referenced_object_id = (select object_id from sys.tables where name = '????????')
--order by TableWithForeignKey, FK_PartNo
SELECT @MaxRow=@@ROWCOUNT
SET @CurrentRow=0
SELECT * FROM #Rows
WHILE @CurrentRow<@MaxRow
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT @CurrentTable=TableWithForeignKey FROM #Rows WHERE RowID=@CurrentRow
--SET @Query='DELETE FROM '+@CurrentTable+' WHERE UserID='+CONVERT(varchar(20),@UserID)
SET @Query='SELECT COUNT(*) FROM '+@CurrentTable+' WHERE YourIDhere='+CONVERT(varchar(20),@UserID)
PRINT @Query
INSERT INTO #Temp
EXECUTE (@Query)
IF NOT EXISTS (SELECT RowValue FROM #Temp WHERE RowValue>0)
BEGIN
PRINT 'no matches found'
DELETE #Rows WHERE RowID=@CurrentRow
END
ELSE
BEGIN
PRINT 'matches found!!'
END
DELETE #Temp
END
--list all tables where the ID exists
SELECT * FROM #Rows
#6
A couple of methods for finding the tables that reference your User table through foreign key relationships have been posted and here's another: http://www.sqlservercurry.com/2009/03/simple-stored-procedure-to-find-foreign.html
通过外键关系找到引用User表的表的几种方法已经发布,这是另一种方法:http://www.sqlservercurry.com/2009/03/simple-stored-procedure-to-find-foreign。 HTML
Once you get the list of tables, you're going to have to write the SQL to determine which tables have records for a specific user. There's no way around that. Since the queries will all look the same, you could paste the table list into Excel, use a formula to build the queries, then paste the queries back into SSMS.
获得表列表后,您将不得不编写SQL以确定哪些表具有特定用户的记录。没有办法解决这个问题。由于查询看起来都一样,您可以将表格列表粘贴到Excel中,使用公式来构建查询,然后将查询粘贴回SSMS。