SQL Server: Drop all Foreign Keys that Reference a Table

This post has moved to Yagni.net.

 

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

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.

 

Advertisement

SQL Server: Show all the PRIMARY KEYS for a TABLE

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.

SQL: WHEN CASE NULL fails.

Imagine you want to find all the null values in a column in a database table (SQL Server).

x
1
2
NULL
4
5

Here is the SQL that performs the task as required:

SELECT x,

CASE x

WHEN NULL THEN ‘yes’

ELSE ‘no’

END AS result

FROM

someTable

The result he expected was:

x	result
1	no
2	no
NULL	yes
4	no
5	no

But that isn’t what he got. His result was like this:

x	result
1	no
2	no
NULL	no
4	no
5	no

Curiously, if you run this:

SELECT x,

CASE x

WHEN 1 THEN ‘yes’

ELSE ‘no’

END AS result

FROM

someTable

You do get this:

x	result
1	yes
2	no
NULL	no
4	no
5	no

 So, why didn’t the original work?

• Because NULL means unknown
• Because NULL does not equal NULL
• Because NULL is just weird, weird, weird

Anyway, here is the SQL that gives the expected answer: 

SELECT x,

CASE

WHEN x IS NULL THEN ‘yes’

ELSE ‘no’

END AS result

FROM

someTable

Change Owner of an Object in SQL Server 2000

Like this: 

sp_changeobjectowner ‘<objectname>’, ‘<newownername>’

SQL Server Case Sensitive Query

Someone at work sent this out to the team. It might be useful some time:

SQL: Top (Latest) Item Per Group

I spent far too long this morning on trying to find a solution to this tricky SQL problem. Eventually, I found the answer by Googling. I’ve copied the solution here in case I need it again.

How to Rename a SQL Server Database

Sometimes it is useful to rename a SQL Server 2000 Database.