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

7 Comments. Leave new

• 

  Ivan

  July 1, 2008 1:50 am

  thanks a lot, you saved me so much time!

  Reply
• 

  Bob Schultz

  August 13, 2008 10:14 pm

  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
• 

  Todd Owens

  December 18, 2008 1:42 am

  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
• 

  Rohit Dadsena

  January 22, 2009 11:38 am

  U really saved me lot of time.

  Reply
• 

  Rohit Dadsena

  January 22, 2009 11:47 am

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

  Reply
• 

  ech0

  October 25, 2011 8:43 pm

  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
  • 

    madhivanan

    November 3, 2011 2:15 pm

    Refer this method for accuracy

    Reply