my code stock.com

Sonya Curry

Finding Tables with no Primary Keys
by Sonya Curry

You’ll want to know if there tables without primary keys and why. Here is a way of getting that information from the INFORMATION_SCHEMA.tables view.

Snippet options

Download: Download snippet as finding-tables-with-no-primary-keys.sql.
Copy snippet: For this you need a free my code stock.com account.
Embed code : You will find the embed code for this snippet at the end of the page, if you want to embed it into a website or a blog!

--Which of my tables don't have primary keys?
SELECT --we'll do it via information_Schema
  TheTables.Table_Catalog+'.'+TheTables.Table_Schema+'.'
                        +TheTables.Table_Name AS [tables without primary keys]
FROM
  information_Schema.tables TheTables
  LEFT OUTER JOIN information_Schema.table_constraints TheConstraints
    ON TheTables.table_Schema=TheConstraints.table_schema
       AND TheTables.table_name=TheConstraints.table_name
       AND constraint_type='PRIMARY KEY'
WHERE table_Type='BASE TABLE'
  AND constraint_name IS NULL
  ORDER BY [tables without primary keys];

Create a free my code stock.com account now.

my code stok.com is a free service, which allows you to save and manage code snippes of any kind and programming language. We provide many advantages for your daily work with code-snippets, also for your teamwork. Give it a try!

Find out more and register now

You can customize the height of iFrame-Codes as needed! You can find more infos in our API Reference for iframe Embeds.