如何在一个查询中删除所有表中的所有引用数据

本文介绍了如何在一个查询中删除所有表中的所有引用数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有一张产品表。在许多表格中有许多外键与productid相对应。 我想从产品表中删除特定产品但同时我要删除还有其他表的引用，如果这些表中的ID数据可用。我没有任何级联可用于我的任何外键 plese建议 什么我试过了： 我试图做这样的事情 删除a。*，b。* 来自消息a LEFT JOIN usersmessages b ON b.messageid = a.messageid WHERE a.messageid = 1 但是很多表我都没有发现这种方式是正确的解决方案 SQL DELETE命令只能从一个表中删除，因此您需要先从子表中删除所有条目，然后使用多个DELETE命令从主表中删除。我建议设置一个建立事务的存储过程，然后在COMMIT之前以正确的顺序执行DELETE。 这就是为什么CASCADE在那里：到防止你必须这样做！ 如果有外键，你可以从下面查询获得所有子表的列表（列：FKTABLE_NAME） EXEC sp_fkeys ' tableName' 如果您的产品名称存储在其他表格中，而不是主键值 您可以搜索所有表格：数据库中的列通过以下查询 - DROP表#Results DECLARE @ SearchStr nvarchar （ 100 ）= productName' CREATE TABLE #Results（ColumnName nvarchar （ 370 ），ColumnValue nvarchar （ 3630 ）） DECLARE @ TableName nvarchar （ 256 ）， @ ColumnName nvarchar （ 128 ）， @ SearchStr2 nvarchar （ 110 ） SET @ TableName = ' ' SE T @ SearchStr2 = QUOTENAME（' ％ ' + @ SearchStr + ' ％' ，' '''） WHILE @ TableName IS NOT NULL BEGIN SET @ ColumnName = ' ' SET @ TableName = （ SELECT MIN（QUOTENAME（TABLE_SCHEMA）+ ' 。' + QUOTENAME（TABLE_NAME）） FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ' BASE TABLE' AND QUOTENAME（TABLE_SCHEMA）+ ' 。' + QUOTENAME（TABLE_NAME） > @ TableName AND OBJECTPROPERTY （ OBJECT_ID （ QUOTENAME（TABLE_SCHEMA）+ ' 。 + QUOTENAME（TABLE_NAME）），' IsMSShipped' ）= 0 ） WHILE （ @ TableName IS NOT NULL ） AND （ @ ColumnName IS NOT NULL ） BEGIN SET @ ColumnName = （ SELECT MIN（ QUOTENAME（COLUMN_NAME）） FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME （ @ TableName ， 2 ） AND TABLE_NAME = PARSENAME （ @ TableName ， 1 ） AND DATA_TYPE IN （' char'，' varchar'，' nchar'，' n varchar'） AND QUOTENAME（COLUMN_NAME）> @ ColumnName ） IF @ ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC （ ' SELECT''' + @ TableName + ' 。' + @ ColumnName + ' ''，LEFT（' + @ ColumnName + ' ，3630） FROM' + @ T ableName + ' （NOLOCK）' + ' WHERE' + @ ColumnName + ' LIKE' + @ SearchStr2 ） END END END SELECT ColumnName，ColumnValue FROM #Results 您可以尝试类似的事项 - DELETE a，b FROM 消息a LEFT JOIN usersmessages b WHERE b.messageid = a.messageid 和 a.messageid = 1 希望，它有帮助：） Hi , I have one product table . There are lots of foreign keys with respective to productid in many tables.What i want to delete particular products from my product table but at the same time i want to remove there references from other tables as well if those ids data is available in any of those tables . I dont have any cascade available for any of my foreign key plese suggestWhat I have tried:I was trying to do something like this DELETE a.*, b.* FROM messages a LEFT JOIN usersmessages b ON b.messageid = a.messageid WHERE a.messageid = 1but tere are many tables and I dont found this way is correct 解决方案 The SQL DELETE command can only delete from one table, so you need to remove all entries from your subtables first, then from the master table, using multiple DELETE commands. I'd suggest setting up a stored procedure which establishes a transaction, then performs the DELETEs in the correct order before the COMMIT.That is why CASCADE is there: to prevent you having to do this!If foreign key is there you can get list of all child table (column: FKTABLE_NAME) from below queryEXEC sp_fkeys 'tableName'if your product name is stored in other table with string value instead of Primary keyyou can search all table:column from database by below query--DROP table #Results DECLARE @SearchStr nvarchar(100)='productName' CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)SET @TableName = ''SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')WHILE @TableName IS NOT NULLBEGINSET @ColumnName = ''SET @TableName = (SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = 'BASE TABLE'ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableNameANDOBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0)WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)BEGINSET @ColumnName =(SELECT MIN(QUOTENAME(COLUMN_NAME))FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA= PARSENAME(@TableName, 2)ANDTABLE_NAME= PARSENAME(@TableName, 1)ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')ANDQUOTENAME(COLUMN_NAME) > @ColumnName)IF @ColumnName IS NOT NULLBEGININSERT INTO #ResultsEXEC('SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2)ENDENDENDSELECT ColumnName, ColumnValue FROM #ResultsYou can try something like-DELETE a, bFROM messages a LEFT JOIN usersmessages b WHERE b.messageid = a.messageid and a.messageid = 1Hope, it helps :) 这篇关于如何在一个查询中删除所有表中的所有引用数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！