text: CREATE PROCEDURE [generateForeignKeyScript]
  
  @tableName SYSNAME,
  @foreignKey varchar(50) = null
  
  AS
  
  DECLARE 
        @object_name SYSNAME,
        @object_id INT
  
  SELECT 
        @object_name = '[' + s.name + '].[' + o.name + ']'
      , @object_id = o.[object_id]
  FROM sys.objects o WITH (NOWAIT)
  JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
  WHERE s.name + '.' + o.name = @tableName
      AND o.[type] = 'U'
      AND o.is_ms_shipped = 0
  
  ;WITH index_column AS 
  (
      SELECT 
            ic.[object_id]
          , ic.index_id
          , ic.is_descending_key
          , ic.is_included_column
          , c.name
      FROM sys.index_columns ic WITH (NOWAIT)
      JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
      WHERE ic.[object_id] = @object_id
  ),
  fk_columns AS 
  (
       SELECT 
            k.constraint_object_id
          , cname = c.name
          , rcname = rc.name
      FROM sys.foreign_key_columns k WITH (NOWAIT)
      JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id 
      JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
      WHERE k.parent_object_id = @object_id
  )
  SELECT ISNULL((SELECT (
          SELECT CHAR(13) +
               'ALTER TABLE ' + @object_name + ' WITH' 
              + CASE WHEN fk.is_not_trusted = 1 
                  THEN ' NOCHECK' 
                  ELSE ' CHECK' 
                END + 
                ' ADD CONSTRAINT [' + fk.name  + '] FOREIGN KEY(' 
                + STUFF((
                  SELECT ', [' + k.cname + ']'
                  FROM fk_columns k
                  WHERE k.constraint_object_id = fk.[object_id]
                  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
                 + ')' +
                ' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('
                + STUFF((
                  SELECT ', [' + k.rcname + ']'
                  FROM fk_columns k
                  WHERE k.constraint_object_id = fk.[object_id]
                  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
                 + ')'
              + CASE 
                  WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE' 
                  WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
                  WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT' 
                  ELSE '' 
                END
              + CASE 
                  WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
                  WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
                  WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'  
                  ELSE '' 
                END 
              + CHAR(13) + 'ALTER TABLE ' + @object_name + ' CHECK CONSTRAINT [' + fk.name  + ']' + CHAR(13)
          FROM sys.foreign_keys fk WITH (NOWAIT)
          JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
          WHERE fk.parent_object_id = @object_id and 
                (CASE WHEN @foreignKey IS NULL THEN '1' ELSE fk.name END ) = ISNULL(@foreignKey, '1' )
          FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
  
   GO
   