SDU Tools: List Incoming Foreign Keys in a SQL Server Database
One of the challenges when you have foreign keys in place, is that there are restrictions on what you can do to tables that are the targets of the foreign keys. What’s often needed though is a list of all references to a particular table or set of tables.
In our free SDU Tools for developers and DBAs, we added a function ListIncomingForeignKeys to do just that, and to provide their details in a form that’s easy to consume programmatically if you need that.
You can see how to execute it in the main image above. The procedure takes these parameters:
@DatabaseName sysname - This is the database to process @ReferencedSchemasToList nvarchar(max) - a comma-delimited list of schemas to include (ie: ‘Sales,Purchasing’) or the word ‘ALL’ @ReferencedTablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: ‘Customers,Orders’) or the word ‘ALL’
One row is returned for each foreign key.
The columns returned are SourceSchemaName, SourceTableName, ForeignKeyName, SourceColumnList, ReferencedSchemaName, ReferencedTableName, ReferencedColumnList, IsNotTrusted, IsDisabled.
You can see it in action here:
To become an SDU Insider and to get our free tools and eBooks, please just visit here:
http://sdutools.sqldownunder.com
Learn about indexing and foreign keys
Want to learn more about indexes and foreign keys? Take our online on-demand course now: https://training.sqldownunder.com/p/sql-server-indexing-for-developers
2018-10-31