I have a lot of Foreign Key Columns with cascade delete set to NO ACTION in some specific Schema and I want to Set the FK Columns to ON DELETE SET NULL (for Example) where Primary Key Column's Schema relating to this Foreign Key Column is starting with 'Store'.
I found some codes like this one below but unfortunately I was unable to add the TSQL needed to fit my needs, can somebody help...
I found some codes like this one below but unfortunately I was unable to add the TSQL needed to fit my needs, can somebody help...
SQL:
IF Object_id('tempdb..#queriesForContraints') IS NOT NULL
BEGIN
DROP TABLE #queriesForContraints
END
DECLARE @ignoreTablesCommaSeparated VARCHAR(1000)
SELECT 'ALTER TABLE ['
+ ForeignKeys.foreigntableschema + '].['
+ ForeignKeys.foreigntablename
+ '] DROP CONSTRAINT ['
+ ForeignKeys.foreignkeyname + ']; '
+ 'ALTER TABLE ['
+ ForeignKeys.foreigntableschema + '].['
+ ForeignKeys.foreigntablename
+ '] WITH CHECK ADD CONSTRAINT ['
+ ForeignKeys.foreignkeyname
+ '] FOREIGN KEY(['
+ ForeignKeys.foreigntablecolumn
+ ']) REFERENCES ['
+ Schema_name(sys.objects.schema_id) + '].['
+ sys.objects.[name] + '](['
+ sys.columns.[name]
+ ']) ON DELETE CASCADE; ' AS query
INTO #queriesForContraints
FROM sys.objects
INNER JOIN sys.columns
ON ( sys.columns.[object_id] = sys.objects.[object_id] )
INNER JOIN (SELECT sys.foreign_keys.[name] AS
ForeignKeyName,
Schema_name(sys.objects.schema_id) AS
ForeignTableSchema,
sys.objects.[name] AS
ForeignTableName,
sys.columns.[name] AS
ForeignTableColumn,
sys.foreign_keys.referenced_object_id AS
referenced_object_id,
sys.foreign_key_columns.referenced_column_id AS
referenced_column_id
FROM sys.foreign_keys
INNER JOIN sys.foreign_key_columns
ON (
sys.foreign_key_columns.constraint_object_id =
sys.foreign_keys.[object_id] )
INNER JOIN sys.objects
ON ( sys.objects.[object_id] =
sys.foreign_keys.parent_object_id )
INNER JOIN sys.columns
ON ( sys.columns.[object_id] =
sys.objects.[object_id] )
AND ( sys.columns.column_id =
sys.foreign_key_columns.parent_column_id ))
ForeignKeys
ON ( ForeignKeys.referenced_object_id = sys.objects.[object_id] )
AND ( ForeignKeys.referenced_column_id = sys.columns.column_id )
WHERE ( sys.objects.[type] = 'U' )
AND ( sys.objects.[name] NOT IN (
'sysdiagrams' --add more comma separated table names here if required
) )
DECLARE @queryToRun NVARCHAR(MAX)
SELECT @queryToRun = STUFF(
(SELECT query + ''
FROM #queriesForContraints
FOR XML PATH (''))
, 1, 0, '')
EXEC sp_executesql @statement = @queryToRun
IF Object_id('tempdb..#queriesForContraints') IS NOT NULL
BEGIN
DROP TABLE #queriesForContraints
END