Microsoft – SQLServer – Constraints – Foreign Key – List

Microsoft – SQLServer – Constraints – Foreign Key – List

Function Name :- udf_columnList

What does it do :-

  • Based on the ID passed in, it returns a string that embodies the list of columns for that object
  • To determine the type of object the objectPropertyex is invoked
use <db-name>
go

if OBJECT_ID('dbo.udf_columnList') is null
begin
 exec ('create function dbo.udf_columnList() 
         returns nvarchar(4000)
         begin 
             return null 
         end
       '); 

end

go

alter function dbo.udf_columnList
(
      @objectID int
    , @listSubType tinyint
    , @separator varchar(10)
)
returns nvarchar(4000)
begin

    declare @list nvarchar(4000)

    /*
      if object passed in is a foreign key, then process
      as a foreign key
    */
    if ((objectPropertyex(@objectID, N'BaseType') = 'F')
    begin

     select @list =
              coalesce(@list + @separator, '') +
            /*
               If ListSubType is 1, then return parent columns
               If ListSubType is 2, then return reference columns
            */   
            case
              when (@listSubType = 1) then tblColumnParent.name
              when (@listSubType = 2) then tblColumnReference.name
            end     

      from   sys.foreign_key_columns tblForeignKeyColumn

             inner join sys.columns tblColumnParent
                  on tblForeignKeyColumn.parent_object_id = 
                        tblColumnParent.object_id
                  and  tblForeignKeyColumn.parent_column_id =                             tblColumnParent.column_id

             inner join sys.columns tblColumnReference

                  on tblForeignKeyColumn.referenced_object_id =
                         tblColumnReference.object_id
                  and  tblForeignKeyColumn.referenced_column_id =                          tblColumnReference.column_id

    where  tblForeignKeyColumn.constraint_object_id = @objectID

  end

  return (@list);

end

SP Name :- sp_List_Contraint_ForeignKeys

What does it do :-

  • Accesses sys.foreign_keys and gets the IDs of the foreign key object, the foreign key’s parent object, and the dependent object
  • It then invokes the dbo.udf_ColumnList which gets the IDs of the columns involved in the foreign key relationship
use master
go

if OBJECT_ID('dbo.sp_List_Constraint_Foreignkeys') is null
begin

  exec ('create procedure dbo.sp_List_Constraint_Foreignkeys 
             as print ''shell'' 
        ')

end

go

alter procedure dbo.sp_List_Constraint_Foreignkeys
as

 select 

      OBJECT_NAME(tblForeignKey.object_id) as [constraintName]   

    , OBJECT_NAME(tblForeignKey.parent_object_id) as [object] 

    , OBJECT_NAME(tblForeignKey.referenced_object_id) 
         as referenced

     , tblForeignkey.is_disabled as [isDisabled]

     , tblForeignKey.is_not_trusted

      , dbo.fn_columnList
                            (
                                   tblForeignKey.object_id 
                                 , 1 
                                 , ', '
                            )  as [columnList]

      , dbo.fn_columnList
                            (

                                   tblForeignKey.object_id
                                 , 2   
                                 , ', '
                            )  as [columnListRef]

 from  sys.foreign_keys tblForeignKey

 group by

          tblForeignKey.object_id 
        , OBJECT_NAME(tblForeignKey.object_id)           
        , OBJECT_NAME(tblForeignKey.parent_object_id)        
        , OBJECT_NAME(tblForeignKey.referenced_object_id)

        , tblForeignkey.is_disabled 

        , tblForeignKey.is_not_trusted

order by 

              OBJECT_NAME(tblForeignKey.parent_object_id)
            , OBJECT_NAME(tblForeignKey.referenced_object_id) 

go

if OBJECT_ID('dbo.sp_List_Constraint_Foreignkeys') is not null
begin

    exec sys.sp_MS_marksystemobject 
               dbo.sp_List_Constraint_Foreignkeys

end
go

Note:

  • Unfortunately, MS SQL Server does not support global functions and so unfortunately it seems you will have to have the Scaler function (dbo.udf_columnList) in all databases that you will like it referenced

References:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s