Table A has a foreign key constraint (type) to Table B (id). However, type is not null and id is nullable.
I'm trying to build a query using information_schema that will look at foreign key constraints and match up the column types and nullable columns to see if they sync, however I'm having problems with the logic.
select kcu.table_name, kcu.column_name, c.column_type, c.is_nullable,kcu.referenced_table_name, kcu.referenced_column_name,c.column_type, c.is_nullable
from key_column_usage kcu
inner join columns c on c.table_schema=kcu.table_schema and c.column_name=kcu.column_name and c.table_name=kcu.table_name
where kcu.referenced_table_name='Table_B' and kcu.table_name='Table_A';
I know this syntax is incorrect -- this is just all I've been able to put together so far. I'd like to have this be able to be executed for every table in a database and have it ordered by table_name, then column_name. It can exclude columns where column_type and is_nullable fields are identical.
Open the Table Designer for the table containing the foreign key you want to view, right-click in the Table Designer, and choose Relationships from the shortcut menu. In the Foreign Key Relationships dialog box, select the relationship with properties you want to view.
You can use the following: Create a Diagram in your SQL Server Management Studio to view the connections / foreign keys (not a function however) You can run queries using INFORMATION_SCHEMA . This allows you to view all the metadata pulled from the system database.
The syntax for enabling a check constraint in SQL Server (Transact-SQL) is: ALTER TABLE table_name WITH CHECK CHECK CONSTRAINT constraint_name; table_name. The name of the table that you wish to enable the check constraint.
First method is with table Constraints tab (select table and select Constraints tab). Tab lists table constraints - primary, unique and foreign keys and check constraints - all in one grid. Foreign keys are the ones with 'Foreign_Key' value in CONSTRAINT_TYPE column.
There may be legitimate reasons for a NULLABLE column on one side of a foreign constraint, but this will compare the type/nullable properties of the columns involved.
SELECT
kcu.constraint_schema
, kcu.constraint_name
, kcu.referenced_table_name
, kcu.referenced_column_name
, kcu.table_name
, kcu.column_name
, refcol.column_type referenced_column_type
, childcol.column_type
, refcol.is_nullable referenced_is_nullable
, childcol.is_nullable
FROM information_schema.key_column_usage kcu
INNER JOIN information_schema.columns refcol
ON refcol.table_schema = kcu.referenced_table_schema
AND refcol.table_name = kcu.referenced_table_name
AND refcol.column_name = kcu.referenced_column_name
INNER JOIN information_schema.columns childcol
ON childcol.table_schema = kcu.table_schema
AND childcol.table_name = kcu.table_name
AND childcol.column_name = kcu.column_name
WHERE (
refcol.is_nullable <> childcol.is_nullable
OR
refcol.column_type <> childcol.column_type
)
AND kcu.TABLE_SCHEMA = 'rextester' #change this value to suit
ORDER BY
kcu.table_name
, kcu.column_name
;
See a working example (click the run button)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With