locked
find arguments for constraint RRS feed

  • Question

  • User348142989 posted

    HI

    Using the following code, I can get a list of table names and the names of associated constraints for each table.

    conn = new SqlConnection(conString);
    conn.Open();
    myQuery = "SELECT * FROM information_schema.table_constraints ORDER BY table_name ";
    SqlDataAdapter myAdapt = new SqlDataAdapter(myQuery, conn);
    DataTable testDataTable = new DataTable(); //create table
    myAdapt.Fill(testDataTable);
    dataGridView1.DataSource = testDataTable;
    dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);

    Microsoft page https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/datatable-constraints shows

    how to create a new constraint.  For a foreign key, essentially three arguments are required:

    - 1] constraint name, 2] table name and column and 3] foreign table name and column for a foreign key (as an example).

    I would like to reverse the process and knowing the constraint name, would like to find 1] the associated table name and column

    and 2] foreign table name and column.

    I am using Visual Studio 2019 to build queries and execute them.

    Thanks for any help.

    Sunday, August 25, 2019 6:09 PM

Answers

  • User-719153870 posted

    Hi TiredOldCat,

    I would like to reverse the process and knowing the constraint name, would like to find 1] the associated table name and column

    and 2] foreign table name and column.

    So you already knew the constraint name or not?

    If you knew the constraint name and want to find the parent-child tables and columns bound to this constaint, please refer to below code:

    select object_name(A.parent_obj) as 'Child Table'
           ,col_name(A.parent_obj,B.fkey) as 'Child Column' 
           ,object_name(B.rkeyid) as 'Parent Table'
           ,col_name(B.rkeyid,B.rkey) as 'Parent Column'
    from sysobjects A
    join sysforeignkeys B on A.id=B.constid
    where A.name='fk_MID'--'Constraint Name'

    Or, if you don't know the constraint name and want to know all constraint related information bound to one specific table, please refer to below:

    select
    a.name as 'Constraint Name',
    object_name(b.parent_object_id) as 'Child Table',
    d.name as 'Child Column',
    object_name(b.referenced_object_id) as 'Parent Table',
    c.name as 'Parent Column'
    from sys.foreign_keys A
    inner join sys.foreign_key_columns B on A.object_id=b.constraint_object_id
    inner join sys.columns C on B.parent_object_id=C.object_id and B.parent_column_id=C.column_id
    
    inner join sys.columns D on B.referenced_object_id=d.object_id and B.referenced_column_id=D.column_id
    
    where object_name(B.referenced_object_id)='MTable'; --'Related Table Name'

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 26, 2019 2:59 AM

All replies

  • User-719153870 posted

    Hi TiredOldCat,

    I would like to reverse the process and knowing the constraint name, would like to find 1] the associated table name and column

    and 2] foreign table name and column.

    So you already knew the constraint name or not?

    If you knew the constraint name and want to find the parent-child tables and columns bound to this constaint, please refer to below code:

    select object_name(A.parent_obj) as 'Child Table'
           ,col_name(A.parent_obj,B.fkey) as 'Child Column' 
           ,object_name(B.rkeyid) as 'Parent Table'
           ,col_name(B.rkeyid,B.rkey) as 'Parent Column'
    from sysobjects A
    join sysforeignkeys B on A.id=B.constid
    where A.name='fk_MID'--'Constraint Name'

    Or, if you don't know the constraint name and want to know all constraint related information bound to one specific table, please refer to below:

    select
    a.name as 'Constraint Name',
    object_name(b.parent_object_id) as 'Child Table',
    d.name as 'Child Column',
    object_name(b.referenced_object_id) as 'Parent Table',
    c.name as 'Parent Column'
    from sys.foreign_keys A
    inner join sys.foreign_key_columns B on A.object_id=b.constraint_object_id
    inner join sys.columns C on B.parent_object_id=C.object_id and B.parent_column_id=C.column_id
    
    inner join sys.columns D on B.referenced_object_id=d.object_id and B.referenced_column_id=D.column_id
    
    where object_name(B.referenced_object_id)='MTable'; --'Related Table Name'

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 26, 2019 2:59 AM
  • User348142989 posted

    Hi Yang

    Many thanks for your help.  I had one small problem with the query 

    SELECT A.name AS 'Constraint Name', OBJECT_NAME(B.parent_object_id) AS 'Child Table', D.name AS 'Child Column',

    OBJECT_NAME(B.referenced_object_id) AS 'Parent Table', C.name AS 'Parent Column'
    FROM sys.foreign_keys AS A INNER JOIN
    sys.foreign_key_columns AS B ON A.object_id = B.constraint_object_id INNER JOIN
    sys.columns AS C ON B.parent_object_id = C.object_id AND B.parent_column_id = C.column_id INNER JOIN
    sys.columns AS D ON B.referenced_object_id = D.object_id AND B.referenced_column_id = D.column_id
    WHERE (OBJECT_NAME(B.parent_object_id) = 'MyTable' )

    I needed to place ( ) around the WHERE clause.  Other than this, worked flawlessly.

    Thanks very much for your help.

    Tuesday, August 27, 2019 1:17 PM
  • User77042963 posted

    You don't have to place ( ) around  the objectname in your where clause.

    Tuesday, August 27, 2019 3:19 PM
  • User348142989 posted

    I agree but for some reason using Visual Studio 2019 the query will not return anything unless I place () around the object name.

    Tuesday, August 27, 2019 5:22 PM