Wygenerować polecenie Delete z relacji kluczy obcych w SQL 2008?
Czy za pomocą skryptu / narzędzia można wygenerować polecenie delete na podstawie relacji tabel fk.
Tzn. mam tabelę: DelMe (ID) i jest 30 tabel z referencjami fk do jego ID, które muszę najpierw usunąć, czy jest jakieś narzędzie / skrypt, który mogę uruchomić, który wygeneruje 30 instrukcji delete na podstawie relacji FK dla mnie ?
(btw wiem o cascade delete na relacjach, nie mogę go użyć w tym istniejącym db)
Używam Microsoftu SQL Server 2008
5 answers
Usuń polecenia wygenerowane do użycia w SP z parametrem i jak na wyzwalaczach DELETE: (ten wariant obsługuje tylko jedną kolumnę FKs)
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')
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-01-28 10:40:50
Oto skrypt do kaskadowego usuwania przez Aasim Abdullah, działa u mnie na 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;
Przykład użycia 1
Zwróć uwagę na użycie w pełni kwalifikowanej nazwy kolumny w przykładzie. Jest to subtelne, ale musisz podać nazwę tabeli, aby wygenerowany SQL działał poprawnie.
EXEC uspCascadeDelete
@ParentTableId = 'Production.Location',
@WhereClause = 'Location.LocationID = 2'
Przykład użycia 2
EXEC uspCascadeDelete
@ParentTableId = 'dbo.brand',
@WhereClause = 'brand.brand_name <> ''Apple'''
Przykład użycia 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)'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-03-03 18:47:52
Jestem prawie pewien, że umieściłem kod tutaj na Stack Overflow, który robi to automatycznie za pomocą INFORMATION_SCHEMA
do generowania dynamicznego SQL, ale nie mogę go znaleźć. Zobaczę, czy uda mi się go zregenerować.
Być może będziesz musiał to trochę sprawdzić, nie mogłem znaleźć mojego oryginalnego kodu, więc zmodyfikowałem kod, który automatycznie buduje widoki spłaszczania dla schematów gwiazd.
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-07 14:55:29
Inną techniką jest użycie generatora kodu do tworzenia Sql. Jestem prawie pewien, że MyGeneration (Brak połączenia) ma istniejące szablony do tego. Za pomocą tego narzędzia i odpowiedniego szablonu możesz utworzyć skrypt sql, który usuwa odpowiednie rzeczy bez bólu.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-11-04 05:37:16
Niestety, myślę, że kaskadowanie jest narzędziem, o które prosisz. Rozumiem, że nie jestem w stanie go użyć, ale fakt, że istnieje jako wbudowana część db, praktycznie zabił potrzebę alternatywy.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-01-27 22:27:46