Although it is not possible to include dynamic SQL in a function, it is possible to dynamically generate a function to handle all cases for a given schema.
Below is some SQL that can generate a static function definition with logic to check all foreign keys. Once generated, this function can be used as needed until the schema changes. At that point, it would need to be regenerated.
-- SQL to generate a function that will identify all tables having an
-- FK reference to a specific parent table and key value.
-- Assumptions:
-- 1. All PKs are of the same specified type. For multiple types, separate functions may be generated.
-- 2. Primary and foreign keys are single columns.
-- Settings
DECLARE @FunctionName sysname = 'fn_ForeignKeyReferences'
DECLARE @PrimaryKeyDataType sysname = 'int' -- int, uniqueidentifier, ... other type from sys.types
-- Additional generated settings
DECLARE @GeneratedDateTime NVARCHAR(50) = CONVERT(NVARCHAR(50), GETUTCDATE(), 20)
DECLARE @NoTablesFoundMessage NVARCHAR(100) = '-- No tables with specified PK type "' + @PrimaryKeyDataType + '" were found'
DECLARE @CharKeyTypeSuffix NVARCHAR(10) = CASE WHEN @PrimaryKeyDataType LIKE '%char%' THEN '(max)' ELSE '' END
-- Table to hold FK table and column data
DECLARE @FkInfo TABLE (
ForeignKeyName sysname,
ParentSchemaName sysname, ParentTableName sysname, ParentColumnName sysname,
ChildSchemaName sysname, ChildTableName sysname, ChildColumnName sysname
)
-- Gather FK information
INSERT INTO @FkInfo
SELECT fk.name, ps.name, pt.name, pc.name, cs.name, ct.name, cc.name
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
-- parent (primary key) schema/table/column
JOIN sys.tables pt ON pt.object_id = fkc.referenced_object_id
JOIN sys.schemas ps ON ps.schema_id = pt.schema_id
JOIN sys.columns pc
on pc.object_id = fkc.referenced_object_id
and pc.column_id = fkc.referenced_column_id
-- Chile (foreign key) schema/table/column
JOIN sys.tables ct ON ct.object_id = fkc.parent_object_id
JOIN sys.schemas cs ON cs.schema_id = ct.schema_id
JOIN sys.columns cc
on cc.object_id = fkc.parent_object_id
and cc.column_id = fkc.parent_column_id
JOIN sys.types t ON t.user_type_id = pc.user_type_id
WHERE t.name = @PrimaryKeyDataType
ORDER BY ps.name, pt.name, pc.name, cs.name, ct.name, cc.name
-- Diagnostic preview
--SELECT * FROM @FkInfo
-- SQL templates (Drop function)
DECLARE @DropFunctionTemplate NVARCHAR(MAX) = '
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N''[dbo].[<functionName>]'')
AND type IN ( N''FN'', N''IF'', N''TF'', N''FS'', N''FT'' )
)
BEGIN
DROP FUNCTION [dbo].[<functionName>]
END
'
-- SQL templates (Create function)
DECLARE @FunctionTemplate NVARCHAR(MAX) = '
-- Function to identify all tables having an FK reference to a specific parant table and key value
-- Generated: <GeneratedDateTime>
CREATE FUNCTION [<functionName>](
@SchemaName sysname,
@TableName sysname,
@RowId <PrimaryKeyDataType><CharKeyTypeSuffix>
)
RETURNS @ReferencingTables TABLE (SchemaName sysname, TableName sysname)
AS
BEGIN
IF 1 = 0
BEGIN
DECLARE @Noop INT -- Syntactic placeholder
END
<ParentTablesSql>
RETURN
END
'
-- SQL templates (Code to search for FK references to one particlar parent table)
DECLARE @ParentTableTemplate NVARCHAR(MAX) = '
ELSE IF @TableName = ''<ParentTableName>'' AND @SchemaName = ''<ParentSchemaName>''
BEGIN
INSERT @ReferencingTables
SELECT SchemaName = '''', TableName = '''' WHERE 1 = 0 -- Syntactic placeholder
<ChildTablesSql>
END'
-- SQL templates (Code to search for FK references from one particlar child table and FK)
DECLARE @ChildTableTemplate NVARCHAR(MAX) = '
UNION ALL SELECT SchemaName = ''<ChildSchemaName>'', TableName = ''<ChildTableName>'' WHERE EXISTS(SELECT * FROM [<ChildSchemaName>].[<ChildTableName>] T WHERE T.[<ChildColumnName>] = @RowId)'
-- Pull it all together, substituting schema, table, and column names as we go
-- and concatenating intermediate results using the XML PATH('') trick.
DECLARE @GeneratedSql NVARCHAR(MAX) = (
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
@FunctionTemplate
, '<GeneratedDateTime>', @GeneratedDateTime)
, '<functionName>', @FunctionName)
, '<PrimaryKeyDataType>', @PrimaryKeyDataType)
, '<CharKeyTypeSuffix>', @CharKeyTypeSuffix)
, '<ParentTablesSql>', ISNULL(pts.ParentTablesSql, @NoTablesFoundMessage))
FROM (
SELECT ParentTablesSql = REPLACE((
SELECT REPLACE(REPLACE(REPLACE(
@ParentTableTemplate
, '<ParentSchemaName>', ParentSchemaName)
, '<ParentTableName>', ParentTableName)
, '<ChildTablesSql>', cts.ChildTablesSql)
FROM (
SELECT DISTINCT fk.ParentSchemaName, fk.ParentTableName
FROM @FkInfo fk
) p
CROSS APPLY (
SELECT ChildTablesSql = REPLACE((
SELECT REPLACE(REPLACE(REPLACE(
@ChildTableTemplate
, '<ChildSchemaName>', fk.ChildSchemaName)
, '<ChildTableName>', fk.ChildTableName)
, '<ChildColumnName>', fk.ChildColumnName)
FROM @FkInfo fk
WHERE fk.ParentSchemaName = p.ParentSchemaName AND fk.ParentTableName = p.ParentTableName
ORDER BY fk.ChildSchemaName, fk.ChildTableName, fk.ChildColumnName
FOR XML PATH('')
), '
', CHAR(13))
) cts
ORDER BY p.ParentSchemaName, p.ParentTableName
FOR XML PATH('')
), '
', CHAR(13))
) pts
)
-- Generate drop sql also
DECLARE @DropFunctionSql NVARCHAR(MAX) = REPLACE(@DropFunctionTemplate, '<functionName>', @FunctionName)
-- Combine for final output
DECLARE @AllSql NVARCHAR(MAX) =
@DropFunctionSql + 'GO' + CHAR(13) + CHAR(10)
+ @GeneratedSql + 'GO' + CHAR(13) + CHAR(10)
--PRINT @AllSql
SELECT CAST('<root><![CDATA[' + @AllSql + ']]></root>' AS XML) -- If the above is too long to "print".
-- Optional direct execute (must run as two separate executions).
--EXEC sp_executesql @DropFunctionSql
--EXEC sp_executesql @GeneratedSql
If this is not suitable, you will probably have to code something up in a stored procedure that can generate and execute dynamic sql on-the-fly based on the current schema.
