SQL Server: Drop all Foreign Keys that Reference a Table

March 17, 2011

This post has moved to Yagni.net.

 


SQL Server: Show all the FOREIGN KEYS that reference a TABLE

November 5, 2009

For SQL 2005+


DECLARE
     @tableName nvarchar(MAX)
SET
    @tableName = 'tableName' -- (1)

SELECT DISTINCT
     ccu.table_name,
     ccu.constraint_name
FROM
     INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN
     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
          ON rc.unique_constraint_name = tc.constraint_name
INNER JOIN
     INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
          ON ccu.constraint_name = rc.constraint_name
WHERE
     tc.table_name = @tableName

Notes:

  • Change line (1) to specify the table name.

 


SQL Server: Show all the PRIMARY KEYS for a TABLE

November 5, 2009

For SQL Server 2005 + 


DECLARE

     @tableName nvarchar(MAX)
SET

     @tableName = 'tableName' -- (1)


SELECT

      tc.TABLE_NAME,

      tc.CONSTRAINT_NAME,

      kcu.COLUMN_NAME,

      kcu.ORDINAL_POSITION

FROM

      INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

INNER JOIN

      INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu

      ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME

WHERE

      tc.CONSTRAINT_TYPE = 'PRIMARY KEY'

      AND

      tc.TABLE_NAME = @tableName OR @tableName IS NULL

ORDER BY

      tc.TABLE_NAME,

      kcu.ORDINAL_POSITION

Notes:

  • Change line (1) to specify the table name. Removing this line will list PRIMARY KEYS for all TABLES.