Создать выражение на вывод из внешних отношений в SQL 2008?
Возможно ли с помощью script/tool создать инструкцию delete на основе таблиц fk отношений.
то есть. У меня есть таблица: DelMe (ID), и есть 30 таблиц с ссылками fk на свой ID, которые мне нужно удалить первым, есть ли какой-нибудь инструмент / script, который я могу запустить, который будет генерировать 30 операторов удаления на основе FK отношения для меня?
(Кстати, я знаю о каскадном удалении по отношениям, я не могу использовать его в этом существующем db)
Я использую Microsoft SQL Server 2008
Ответы
Ответ 1
Операторы DELETE, созданные для использования в SP с параметром, и как триггеры ON DELETE:
(этот вариант поддерживает только одноколонные FK)
SELECT 'DELETE '+detail.name+' WHERE '+dcolumn.name+' = @'+mcolumn.name AS stmt,
'DELETE ' + detail.name + ' FROM ' + detail.name + ' INNER JOIN deleted ON ' +
detail.name + '.' + dcolumn.name + ' = deleted.' + mcolumn.name AS trg
FROM sys.columns AS mcolumn
INNER JOIN sys.foreign_key_columns ON mcolumn.object_id =
sys.foreign_key_columns.referenced_object_id
AND mcolumn.column_id = sys.foreign_key_columns.referenced_column_id
INNER JOIN sys.tables AS master ON mcolumn.object_id = master.object_id
INNER JOIN sys.columns AS dcolumn
ON sys.foreign_key_columns.parent_object_id = dcolumn.object_id
AND sys.foreign_key_columns.parent_column_id = dcolumn.column_id
INNER JOIN sys.tables AS detail ON dcolumn.object_id = detail.object_id
WHERE (master.name = N'MyTableName')
Ответ 2
Ниже приведено script для каскадного удаления Aasim Abdullah, работает для меня на MS SQL Server 2008:
IF OBJECT_ID('dbo.udfGetFullQualName') IS NOT NULL
DROP FUNCTION dbo.udfGetFullQualName;
GO
CREATE FUNCTION dbo.udfGetFullQualName
(@ObjectId INT)
RETURNS VARCHAR (300)
AS
BEGIN
DECLARE @schema_id AS BIGINT;
SELECT @schema_id = schema_id
FROM sys.tables
WHERE object_id = @ObjectId;
RETURN '[' + SCHEMA_NAME(@schema_id) + '].[' + OBJECT_NAME(@ObjectId) + ']';
END
GO
--============ Supporting Function dbo.udfGetOnJoinClause
IF OBJECT_ID('dbo.udfGetOnJoinClause') IS NOT NULL
DROP FUNCTION dbo.udfGetOnJoinClause;
GO
CREATE FUNCTION dbo.udfGetOnJoinClause
(@fkNameId INT)
RETURNS VARCHAR (1000)
AS
BEGIN
DECLARE @OnClauseTemplate AS VARCHAR (1000);
SET @OnClauseTemplate = '[<@pTable>].[<@pCol>] = [<@cTable>].[<@cCol>] AND ';
DECLARE @str AS VARCHAR (1000);
SET @str = '';
SELECT @str = @str + REPLACE(REPLACE(REPLACE(REPLACE(@OnClauseTemplate, '<@pTable>', OBJECT_NAME(rkeyid)), '<@pCol>', COL_NAME(rkeyid, rkey)), '<@cTable>', OBJECT_NAME(fkeyid)), '<@cCol>', COL_NAME(fkeyid, fkey))
FROM dbo.sysforeignkeys AS fk
WHERE fk.constid = @fkNameId; --OBJECT_ID('FK_ProductArrearsMe_ProductArrears')
RETURN LEFT(@str, LEN(@str) - LEN(' AND '));
END
GO
--=========== CASECADE DELETE STORED PROCEDURE dbo.uspCascadeDelete
IF OBJECT_ID('dbo.uspCascadeDelete') IS NOT NULL
DROP PROCEDURE dbo.uspCascadeDelete;
GO
CREATE PROCEDURE dbo.uspCascadeDelete
@ParentTableId VARCHAR (300), @WhereClause VARCHAR (2000), @ExecuteDelete CHAR (1)='N', --'N' IF YOU NEED DELETE SCRIPT
@FromClause VARCHAR (8000)='', @Level INT=0 -- TABLE NAME OR OBJECT (TABLE) ID (Production.Location) WHERE CLAUSE (Location.LocationID = 7) 'Y' IF WANT TO DELETE DIRECTLY FROM SP, IF LEVEL 0, THEN KEEP DEFAULT
AS -- writen by Daniel Crowther 16 Dec 2004 - handles composite primary keys
SET NOCOUNT ON;
/* Set up debug */
DECLARE @DebugMsg AS VARCHAR (4000),
@DebugIndent AS VARCHAR (50);
SET @DebugIndent = REPLICATE('---', @@NESTLEVEL) + '> ';
IF ISNUMERIC(@ParentTableId) = 0
BEGIN -- assume owner is dbo and calculate id
IF CHARINDEX('.', @ParentTableId) = 0
SET @ParentTableId = OBJECT_ID('[dbo].[' + @ParentTableId + ']');
ELSE
SET @ParentTableId = OBJECT_ID(@ParentTableId);
END
IF @Level = 0
BEGIN
PRINT @DebugIndent + ' **************************************************************************';
PRINT @DebugIndent + ' *** Cascade delete ALL data from ' + dbo.udfGetFullQualName(@ParentTableId);
IF @ExecuteDelete = 'Y'
PRINT @DebugIndent + ' *** @ExecuteDelete = Y *** deleting data...';
ELSE
PRINT @DebugIndent + ' *** Cut and paste output into another window and execute ***';
END
DECLARE @CRLF AS CHAR (2);
SET @CRLF = CHAR(13) + CHAR(10);
DECLARE @strSQL AS VARCHAR (4000);
IF @Level = 0
SET @strSQL = 'SET NOCOUNT ON' + @CRLF;
ELSE
SET @strSQL = '';
SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + '''';
IF @ExecuteDelete = 'Y'
EXECUTE (@strSQL);
ELSE
PRINT @strSQL;
DECLARE curs_children CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT constid AS fkNameId, -- constraint name
fkeyid AS cTableId
FROM dbo.sysforeignkeys AS fk
WHERE fk.rkeyid <> fk.fkeyid -- WE DO NOT HANDLE self referencing tables!!!
AND fk.rkeyid = @ParentTableId;
OPEN curs_children;
DECLARE @fkNameId AS INT,
@cTableId AS INT,
@cColId AS INT,
@pTableId AS INT,
@pColId AS INT;
FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId; --, @cColId, @pTableId, @pColId
DECLARE @strFromClause AS VARCHAR (1000);
DECLARE @nLevel AS INT;
IF @Level = 0
BEGIN
SET @FromClause = 'FROM ' + dbo.udfGetFullQualName(@ParentTableId);
END
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @strFromClause = @FromClause + @CRLF + ' INNER JOIN ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + ' ON ' + dbo.udfGetOnJoinClause(@fkNameId);
SET @nLevel = @Level + 1;
EXECUTE dbo.uspCascadeDelete @ParentTableId = @cTableId, @WhereClause = @WhereClause, @ExecuteDelete = @ExecuteDelete, @FromClause = @strFromClause, @Level = @nLevel;
SET @strSQL = 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + @strFromClause + @CRLF + 'WHERE ' + @WhereClause + @CRLF;
SET @strSQL = @strSQL + 'PRINT ''---' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF + @CRLF;
IF @ExecuteDelete = 'Y'
EXECUTE (@strSQL);
ELSE
PRINT @strSQL;
FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId;
--, @cColId, @pTableId, @pColId
END
IF @Level = 0
BEGIN
SET @strSQL = @CRLF + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + ' TOP LEVEL PARENT TABLE''' + @CRLF;
SET @strSQL = @strSQL + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' WHERE ' + @WhereClause + @CRLF;
SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF;
IF @ExecuteDelete = 'Y'
EXECUTE (@strSQL);
ELSE
PRINT @strSQL;
END
CLOSE curs_children;
DEALLOCATE curs_children;
Пример использования 1
Обратите внимание на использование полного имени столбца в примере. Он тонкий, но вы должны указать имя таблицы для сгенерированного SQL для правильной работы.
EXEC uspCascadeDelete
@ParentTableId = 'Production.Location',
@WhereClause = 'Location.LocationID = 2'
Пример использования 2
EXEC uspCascadeDelete
@ParentTableId = 'dbo.brand',
@WhereClause = 'brand.brand_name <> ''Apple'''
Пример использования 3
exec uspCascadeDelete
@ParentTableId = 'dbo.product_type',
@WhereClause = 'product_type.product_type_id NOT IN
(SELECT bpt.product_type_id FROM dbo.brand_product_type bpt)'
Ответ 3
Я уверен, что я разместил здесь код в Stack Overflow, который делает это автоматически, используя INFORMATION_SCHEMA для генерации динамического SQL, но я не могу его найти. Позвольте мне посмотреть, смогу ли я его восстановить.
Возможно, вам потребуется немного проверить это, я не смог найти свой исходный код, поэтому я изменил код, который у меня был, который автоматически создает представления flattend для звездообразных схем.
DECLARE @COLUMN_NAME AS sysname
DECLARE @TABLE_NAME AS sysname
DECLARE @IDValue AS int
SET @COLUMN_NAME = '<Your COLUMN_NAME here>'
SET @TABLE_NAME = '<Your TABLE_NAME here>'
SET @IDValue = 123456789
DECLARE @sql AS varchar(max) ;
WITH RELATED_COLUMNS
AS (
SELECT QUOTENAME(c.TABLE_SCHEMA) + '.'
+ QUOTENAME(c.TABLE_NAME) AS [OBJECT_NAME]
,c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c WITH (NOLOCK)
INNER JOIN INFORMATION_SCHEMA.TABLES AS t WITH (NOLOCK)
ON c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
AND t.TABLE_TYPE = 'BASE TABLE'
INNER JOIN (
SELECT rc.CONSTRAINT_CATALOG
,rc.CONSTRAINT_SCHEMA
,lkc.TABLE_NAME
,lkc.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
WITH (NOLOCK)
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE lkc
WITH (NOLOCK)
ON lkc.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG
AND lkc.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
AND lkc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WITH (NOLOCK)
ON rc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
AND rc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
AND rc.UNIQUE_CONSTRAINT_NAME = tc.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE rkc
WITH (NOLOCK)
ON rkc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
AND rkc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
AND rkc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE rkc.COLUMN_NAME = @COLUMN_NAME
AND rkc.TABLE_NAME = @TABLE_NAME
) AS j
ON j.CONSTRAINT_CATALOG = c.TABLE_CATALOG
AND j.CONSTRAINT_SCHEMA = c.TABLE_SCHEMA
AND j.TABLE_NAME = c.TABLE_NAME
AND j.COLUMN_NAME = c.COLUMN_NAME
)
SELECT @sql = COALESCE(@sql, '') + 'DELETE FROM ' + [OBJECT_NAME]
+ ' WHERE ' + [COLUMN_NAME] + ' = ' + CONVERT(varchar, @IDValue)
+ CHAR(13) + CHAR(10)
FROM RELATED_COLUMNS
PRINT @sql
Ответ 4
Другой метод - использовать генератор кода для создания Sql. Я уверен, что MyGeneration (без подключения) имеет существующие шаблоны для этого. С помощью этого инструмента и правого шаблона вы можете создать sql script, который без каких-либо проблем удалит соответствующие материалы.
Ответ 5
К сожалению, я считаю, что каскадирование - это инструмент, о котором вы просите. Я понимаю, что не могу его использовать, но тот факт, что он существует как встроенная часть db, в значительной степени убил необходимость альтернативы.
Ответ 6
Я проверил это с 4 уровнями внешних ключей, и он сгенерировал 110 операторов DELETE.
Часть этого кода я взял здесь: https://www.mssqltips.com/sqlservertip/4059/script-to-delete-data-from-sql-server-tables-with-foreign-key-constraints/
IF OBJECT_ID('dbo.usp_GenerateCascadeDelete', 'P') IS NOT NULL
DROP PROC dbo.usp_GenerateCascadeDelete;
GO
CREATE PROC dbo.usp_GenerateCascadeDelete @table VARCHAR(256)
, -- use two part name convention
@lvl INT = 0
, -- do not change
@ParentTable VARCHAR(256) = ''
, -- do not change
@debug BIT = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @dbg BIT;
DECLARE @lvlcount int
DECLARE @nextlvl int
DECLARE @maxlvl int
DECLARE @curS CURSOR;
DECLARE @statements table (stmt varchar(MAX), lvl int)
SET @dbg = @debug;
IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
DROP TABLE #tbl
CREATE TABLE #tbl
(id int IDENTITY,
lvl int,
masterTable VARCHAR(256),
detailTable varchar(256),
masterColumn varchar(256),
detailColumn varchar(256),
deleteStatement varchar(MAX),
whereStatement varchar(MAX)
);
-- if the table is already here don't repeat it
IF EXISTS
(
SELECT 1
FROM #tbl
WHERE masterTable = @table
)
RETURN 0;
INSERT INTO #tbl
(lvl
,masterTable
,detailTable
,masterColumn
,detailColumn
)
SELECT @lvl
, @table AS [master-table]
,detail.name AS [detail-table-name]
,mcolumns.name AS [master-column-name]
,dcolumns.name AS [detail-column-name]
FROM sys.columns AS mcolumns
INNER JOIN sys.foreign_key_columns fkcolumns ON mcolumns.object_id = fkcolumns.referenced_object_id
AND mcolumns.column_id = fkcolumns.referenced_column_id
INNER JOIN sys.columns AS dcolumns ON fkcolumns.parent_object_id = dcolumns.object_id
AND fkcolumns.parent_column_id = dcolumns.column_id
INNER JOIN sys.tables AS detail ON dcolumns.object_id = detail.object_id
WHERE mcolumns.object_id = OBJECT_ID(@table);
IF @dbg = 1
PRINT REPLICATE('----', @lvl) + 'lvl ' + CAST(@lvl AS VARCHAR(10)) + ' = ' + @table;
IF NOT EXISTS
(
SELECT *
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID(@table)
)
RETURN 0;
ELSE
BEGIN -- else
SET @ParentTable = @table;
SET @curS = CURSOR FORWARD_ONLY LOCAL READ_ONLY
FOR SELECT tablename = OBJECT_NAME(parent_object_id)
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID(@table)
AND parent_object_id <> referenced_object_id; -- add this to prevent self-referencing which can create a indefinitive loop;
OPEN @curS;
FETCH NEXT FROM @curS INTO @table;
WHILE @@fetch_status = 0
BEGIN --while
SET @lvl = @lvl + 1;
-- recursive call
EXEC dbo.usp_SearchFK
@table,
@lvl,
@ParentTable,
@dbg;
SET @lvl = @lvl - 1;
FETCH NEXT FROM @curS INTO @table;
END; --while
CLOSE @curS;
DEALLOCATE @curS;
END; -- else
IF @lvl = 0
BEGIN
-- set schema names
UPDATE #tbl
SET
masterTable = '[' + OBJECT_SCHEMA_NAME(OBJECT_ID(masterTable)) + '].[' + masterTable + ']',
detailTable = '[' + OBJECT_SCHEMA_NAME(OBJECT_ID(detailTable)) + '].[' + detailTable + ']';
-- level 0 is simple
UPDATE #tbl
SET deleteStatement = 'DELETE ' + detailTable,
whereStatement = detailTable + '.[' + detailColumn + '][email protected]'
WHERE lvl = 0;
-- get the maximum level
SELECT @maxlvl = MAX(lvl) FROM #tbl;
PRINT @maxlvl
SET @lvlcount = 0
WHILE @lvlcount <= @maxlvl
BEGIN
SET @nextlvl = @lvlcount + 1
UPDATE t2
SET deleteStatement = 'DELETE ' + t2.detailTable,
whereStatement = ' EXISTS (SELECT 1 FROM ' + t2.masterTable + ' WHERE ' + t2.masterTable + '.' + t2.masterColumn + '=[' + t2.detailColumn + '] AND ' + t1.whereStatement + ')'
FROM #tbl t2
INNER JOIN #tbl t1 ON t1.detailTable = t2.masterTable AND [email protected]
WHERE t2.lvl = @nextlvl;
SET @lvlcount = @lvlcount + 1;
END
-- add one last delete for the main table
INSERT INTO #tbl
(
lvl,
masterTable,
detailTable,
masterColumn,
detailColumn,
deleteStatement,
whereStatement
)
SELECT TOP 1
-1, -- lvl - int
masterTable, -- masterTable - varchar(256)
'', -- detailTable - varchar(256)
'', -- masterColumn - varchar(256)
'', -- detailColumn - varchar(256)
'DELETE ' + masterTable, -- deleteStatement - varchar(max)
masterColumn + '[email protected]' -- whereStatment - varchar(max)
FROM #tbl WHERE lvl=0;
INSERT INTO @statements
SELECT 'DECLARE @key uniqueidentifier -- change to appropriate data type' AS [Stmt], (@maxlvl+2) AS [lvl]
UNION
SELECT 'SET @key = ''value''' AS [Stmt], (@maxlvl + 1) AS [lvl]
UNION
SELECT deleteStatement + ' WHERE ' + whereStatement AS [Stmt], [lvl] FROM #tbl
ORDER BY lvl DESC;
SELECT stmt FROM @statements;
END;
RETURN 0;
END;
