SDU Tools: List Foreign Key Columns in a SQL Server Database
In a previous post, I talked about the ListForeignKeys procedure as part of our free SDU Tools for developers and DBAs. That procedure returned one row per foreign key. Sometimes though, you need to process each column of a foreign key separately. So we’ve provided the ListForeignKeyColumns tool to do that.
The tool also detects any keys that are using system-generated names. (We don’t recommend 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 @SchemasToList nvarchar(max) - a comma-delimited list of schemas to include (ie: ‘Sales,Purchasing’) or the word ‘ALL’ @TablesToList 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 column, rather than for each foreign key.
The columns returned are ForeignKeyName, IsDisabled, IsNotTrusted, IsSystemNamed, SchemaName, TableName, ColumnID, ColumnName, ReferencedSchemaName, ReferencedTableName, ReferencedColumnName.
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-24