USP_CREDITCARDID_ISINUSE

            create procedure dbo.USP_CREDITCARDID_ISINUSE
            (
                @CREDITCARDID uniqueidentifier,
                @RECORDTOEXCLUDE nvarchar(max),
                @ISINUSE bit output
            )
            with execute as owner
            as
                set nocount on

                declare @CREDITCARDTABLES table
                (
                    SEQUENCE int identity(1, 1),
                    TABLENAME nvarchar(128),
                    COLUMNNAME nvarchar(128)
                )

                -- Pull a list of all columns that reference the credit card table

                insert into @CREDITCARDTABLES (TABLENAME, COLUMNNAME)
                select 
                    object_name(sys.foreign_keys.parent_object_id) as TABLENAME,
                    col_name(sys.foreign_keys.parent_object_id, sys.foreign_key_columns.parent_column_id) as COLUMNNAME
                from sys.foreign_keys
                inner join 
                    sys.foreign_key_columns on sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.object_id
                where
                      sys.foreign_keys.referenced_object_id = object_id('dbo.CREDITCARD')

                declare @PREVIOUSSEQUENCE int
                set @PREVIOUSSEQUENCE = 0

                set @ISINUSE = 0
                while @ISINUSE = 0
                begin
                    declare @TABLENAME nvarchar(128), @COLUMNNAME nvarchar(128)
                    set @TABLENAME = null
                    set @COLUMNNAME = null

                    select top 1
                        @PREVIOUSSEQUENCE = SEQUENCE,
                        @TABLENAME = TABLENAME,
                        @COLUMNNAME = COLUMNNAME
                    from @CREDITCARDTABLES
                    where SEQUENCE > @PREVIOUSSEQUENCE
                    order by SEQUENCE

                    if @TABLENAME is null
                        break

                    -- Determine if any of the tables that link to the credit card table

                    -- contain credit being checked

                    declare @SQLTOEXEC nvarchar(max)
                    set @SQLTOEXEC = '    select top 1 @FOUNDID = BASETABLE.ID from ' + @TABLENAME + ' as BASETABLE
                                        where BASETABLE.' + @COLUMNNAME + ' = @CREDITCARDID '

                    if @RECORDTOEXCLUDE is not null
                        set @SQLTOEXEC = @SQLTOEXEC + ' and BASETABLE.ID <> @RECORDTOEXCLUDE'

                    declare @FOUNDID uniqueidentifier
                    exec sp_executesql @SQLTOEXEC, 
                            N'@CREDITCARDID uniqueidentifier, @RECORDTOEXCLUDE nvarchar(max), @FOUNDID uniqueidentifier output',
                            @CREDITCARDID = @CREDITCARDID, @RECORDTOEXCLUDE = @RECORDTOEXCLUDE, @FOUNDID = @FOUNDID output

                    if @FOUNDID is not null
                        set @ISINUSE = 1
                end