Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_fkeys

  No additional text.


Syntax

create procedure sys.sp_fkeys
(
    @pktable_name        sysname = null,    -- Wildcard pattern matching IS NOT supported.
    @pktable_owner       sysname = null,    -- Wildcard pattern matching IS NOT supported.
    @pktable_qualifier   sysname = null,    -- Wildcard pattern matching IS NOT supported.
    @fktable_name        sysname = null,    -- Wildcard pattern matching IS NOT supported.
    @fktable_owner       sysname = null,    -- Wildcard pattern matching IS NOT supported.
    @fktable_qualifier   sysname = null     -- Wildcard pattern matching IS NOT supported.
)
as
    set nocount on

    declare @pktable_id             int
    declare @fktable_id             int

    -- select 'XXX starting parameter analysis'
    if (@pktable_name is null) and (@fktable_name is null)
    begin   -- If neither primary key nor foreign key table names given
        raiserror (15252,-1,-1)
        return
    end

    if @fktable_qualifier is not null
    begin
        if db_name() <> @fktable_qualifier
        begin   -- If qualifier doesn't match current database
            raiserror (15250, -1,-1)
            return
        end
    end

    if @pktable_qualifier is not null
    begin
        if db_name() <> @pktable_qualifier
        begin   -- If qualifier doesn't match current database
            raiserror (15250, -1,-1)
            return
        end
    end

    if @pktable_owner = ''
    begin   -- If empty owner name
        select @pktable_id = object_id(quotename(@pktable_name))
    end
    else
    begin
        select @pktable_id = object_id(isnull(quotename(@pktable_owner), '') + '.' + quotename(@pktable_name))
    end

    if @fktable_owner = ''
    begin   -- If empty owner name
        select @fktable_id = object_id(quotename(@fktable_name))
    end
    else
    begin
        select @fktable_id = object_id(isnull(quotename(@fktable_owner), '') + '.' + quotename(@fktable_name))
    end

    if @fktable_name is not null
    begin
        if @fktable_id is null
            select @fktable_id = 0  -- fk table name is provided, but there is no such object
    end

    if @pktable_name is not null
    begin
        if @pktable_id is null
            select @pktable_id = 0  -- pk table name is provided, but there is no such object

        select
            PKTABLE_QUALIFIER   = convert(sysname,db_name()),
            PKTABLE_OWNER       = convert(sysname,schema_name(o1.schema_id)),
            PKTABLE_NAME        = convert(sysname,o1.name),
            PKCOLUMN_NAME       = convert(sysname,c1.name),
            FKTABLE_QUALIFIER   = convert(sysname,db_name()),
            FKTABLE_OWNER       = convert(sysname,schema_name(o2.schema_id)),
            FKTABLE_NAME        = convert(sysname,o2.name),
            FKCOLUMN_NAME       = convert(sysname,c2.name),
            -- Force the column to be non-nullable (see SQL BU 325751)
            KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
            UPDATE_RULE         = convert(smallint,
                                            case ObjectProperty(f.object_id, 'CnstIsUpdateCascade')
                                            when 1 then 0
                                            else        1
                                            end),
            DELETE_RULE         = convert(smallint,
                                            case ObjectProperty(f.object_id, 'CnstIsDeleteCascade')
                                            when 1 then 0
                                            else        1
                                            end),
            FK_NAME             = convert(sysname,object_name(f.object_id)),
            PK_NAME             = convert(sysname,i.name),
            DEFERRABILITY       = convert(smallint, 7)   -- SQL_NOT_DEFERRABLE
        from
            sys.objects o1,
            sys.objects o2,
            sys.columns c1,
            sys.columns c2,
            sys.foreign_keys f inner join
            sys.foreign_key_columns k on (k.constraint_object_id = f.object_id) inner join
            sys.indexes i on (f.referenced_object_id = i.object_id and f.key_index_id = i.index_id)
        where
            o1.object_id = f.referenced_object_id and
            (o1.object_id = @pktable_id) and
            o2.object_id = f.parent_object_id and
            (@fktable_id is null or o2.object_id = @fktable_id) and
            c1.object_id = f.referenced_object_id and
            c2.object_id = f.parent_object_id and
            c1.column_id = k.referenced_column_id and
            c2.column_id = k.parent_column_id
        order by 5, 6, 7, 9, 8
    end
    else
    begin
        select
            PKTABLE_QUALIFIER   = convert(sysname,db_name()),
            PKTABLE_OWNER       = convert(sysname,schema_name(o1.schema_id)),
            PKTABLE_NAME        = convert(sysname,o1.name),
            PKCOLUMN_NAME       = convert(sysname,c1.name),
            FKTABLE_QUALIFIER   = convert(sysname,db_name()),
            FKTABLE_OWNER       = convert(sysname,schema_name(o2.schema_id)),
            FKTABLE_NAME        = convert(sysname,o2.name),
            FKCOLUMN_NAME       = convert(sysname,c2.name),
            -- Force the column to be non-nullable (see SQL BU 325751)
            KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
            UPDATE_RULE         = convert(smallint,
                                            case ObjectProperty(f.object_id, 'CnstIsUpdateCascade')
                                            when 1 then 0
                                            else        1
                                            end),
            DELETE_RULE         = convert(smallint,
                                            case ObjectProperty(f.object_id, 'CnstIsDeleteCascade')
                                            when 1 then 0
                                            else        1
                                            end),
            FK_NAME             = convert(sysname,object_name(f.object_id)),
            PK_NAME             = convert(sysname,i.name),
            DEFERRABILITY       = convert(smallint, 7)   -- SQL_NOT_DEFERRABLE
        from
            sys.objects o1,
            sys.objects o2,
            sys.columns c1,
            sys.columns c2,
            sys.foreign_keys f inner join
            sys.foreign_key_columns k on (k.constraint_object_id = f.object_id) inner join
            sys.indexes i on (f.referenced_object_id = i.object_id and f.key_index_id = i.index_id)
        where
            o1.object_id = f.referenced_object_id and
            (@pktable_id is null or o1.object_id = @pktable_id) and
            o2.object_id = f.parent_object_id and
            (o2.object_id = @fktable_id) and
            c1.object_id = f.referenced_object_id and
            c2.object_id = f.parent_object_id and
            c1.column_id = k.referenced_column_id and
            c2.column_id = k.parent_column_id
        order by 1, 2, 3, 9, 4
    end

 
Last revision 2008RTM
See also

  sp_ddopen (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash