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 (https://blog.sqlauthority.com), Joffery

, ,
Previous Post
SQL SERVER – Create Database Error in Windows Vista
Next Post
SQL SERVER – FIX : Error 7311 – You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server or to a linked SQL Server 7.0 server

Related Posts

7 Comments. Leave new

  • thanks a lot, you saved me so much time!

    Reply
  • Pinal,

    This article was a great help to me.

    I was wondering if you could add schema columns to the result set?

    Thanks again..

    Bob

    Reply
  • 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

    Reply
  • U really saved me lot of time.

    Reply
  • But How can i Truncate my Database(or All Tables From Dataabase)

    Reply
  • 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)”

    Reply

Leave a Reply

Menu