USP_SCHEMAINFO_GETFOREIGNKEYINFORMATION

            CREATE procedure dbo.USP_SCHEMAINFO_GETFOREIGNKEYINFORMATION
            (
                @PARENTTABLENAME nvarchar(128),
                @TABLESCHEMA nvarchar(128) = 'dbo'
            )
            as
                /*
                MAB 4/23/08
                This sp is getting called from a SqlClr function.  Setting nocount on will result in a "Invalid use of side-effecting or time-dependent operator in 'SET ON/OFF' within a function." error
                set nocount on;
                */
                -- Get the object_id of the starting table

                declare @PARENTTABLEID int
                select 
                    @PARENTTABLEID = object_id 
                from 
                    sys.objects 
                    inner join sys.schemas on sys.schemas.schema_id = sys.objects.schema_id
                where 
                    sys.objects.name = @PARENTTABLENAME and 
                    sys.objects.type = 'U' and
                    sys.schemas.name = @TABLESCHEMA

                -- Find all child tables of the parent table

                select 
                    sys.objects.name as TABLENAME,
                    CHILDCOLUMN.name as FOREIGNKEYCOLUMN,
                    PARENTCOLUMN.name as PARENTIDCOLUMN
                from 
                    sys.objects 
                    inner join sys.foreign_key_columns on sys.foreign_key_columns.parent_object_id = sys.objects.object_id 
                    inner join sys.columns as CHILDCOLUMN on CHILDCOLUMN.column_id = sys.foreign_key_columns.parent_column_id and CHILDCOLUMN.object_id = sys.foreign_key_columns.parent_object_id
                    inner join sys.columns as PARENTCOLUMN on PARENTCOLUMN.column_id = sys.foreign_key_columns.referenced_column_id and PARENTCOLUMN.object_id = sys.foreign_key_columns.referenced_object_id
                where 
                    sys.foreign_key_columns.referenced_object_id = @parenttableid
                order by
                    sys.objects.name