SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database – Part 2

What I love most about this blog is active readers participation. If readers are becoming contributor is the true success for any blog or online community. Recently many readers have contributed their suggestions and script to this blog.

Joffery has provided nice script which is modification to previous article of SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database.

Following note is from Joffery:

Hi Pinal

Very interesting article and of great help.
I made a little addition to your code. As I wanted also to know what the FKs are doing in the Table (referential integrity on update and on delete) I added two columns to your superb query. And a little bit of ordering :)
Maybe it helps other readers.

USE AdventureWorks
GO
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName,
f.update_referential_action_desc AS UpdateAction,
f.delete_referential_action_desc AS DeleteAction
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
ORDER BY TableName ASC, ColumnName ASC

Reference : Pinal Dave (http://blog.SQLAuthority.com), Joffery

About these ads

9 thoughts on “SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database – Part 2

  1. Bob Shultz:

    Schema Names added below

    USE AdventureWorks
    GO
    SELECT
    f.name AS ForeignKey,
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName,
    f.update_referential_action_desc AS UpdateAction,
    f.delete_referential_action_desc AS DeleteAction
    FROM sys.foreign_keys AS f
    JOIN sys.foreign_key_columns AS fc
    ON f.OBJECT_ID = fc.constraint_object_id
    JOIN sys.objects o
    ON f.parent_object_id = o.object_id
    ORDER BY
    SchemaName ASC,
    TableName ASC,
    ColumnName ASC

    Like

  2. I am new to this but I gave it a try anyway.

    I checked whether it was possible to truncate all tables of a database by using a cursor. However, since in most cases there are constraints between the tables, the cursor won’t be able to work.

    DECLARE @table varchar(150)

    DECLARE db_console CURSOR FOR

    select distinct TABLE_SCHEMA + ‘.’ + TABLE_NAME from INFORMATION_SCHEMA.COLUMNS

    OPEN db_console

    fetch next from db_console into @table

    while @@fetch_status = 0
    begin
    use AdventureWorks
    exec(‘truncate table ‘ + @table)
    –print @db + ‘.’ + ‘.’ + @schema + ‘.’ + @table + ‘truncated’
    fetch next from db_console into @table
    end

    close db_console
    deallocate db_console

    You might as well say that this cursor is useless and in the same is a waste of time due to the fact that there is a stored procedure that can do that for us.

    According to a response from kristof (stackoverflow), I was able to track the following:

    — disable all constraints
    EXEC sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”

    — delete data in all tables
    EXEC sp_MSForEachTable “DELETE FROM ?”

    — enable all constraints
    exec sp_msforeachtable “ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”

    — reseed tables
    EXEC sp_MSforeachtable “DBCC CHECKIDENT ( ‘?’, RESEED, 0)”

    Like

  3. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #001 « SQL Server Journey with SQL Authority

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s