SQL SERVER – 2005 – List Tables in Database Without Primary Key

This is very simple but effective script. It list all the table without primary keys.
USE DatabaseName;
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
ORDER BY SchemaName, TableName;

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL

28 thoughts on “SQL SERVER – 2005 – List Tables in Database Without Primary Key

  1. Just a thought: if you use INFORMATION_SCHEMA.TABLES instead of sys.tables your script will be working in sql server 2000 as well.


  2. I have an issue with a SQL Server 2005 database that created by importing some tables from a SQL Server 2000 database and creating other tables in SQL 2005.

    The issue is that the imported tables have ansi nulls set off and the other tables have ansi nulls set on. Besides the confusion of what ansi nulls does, this prevents me from using indexed views.

    The sys.tables view shows a uses_ansi_nulls column but ad hoc updates are not permitted on the view.

    Is there a reasonable way to change the ansi nulls setting programatically.


  3. We were in a meeting working implementing Replication and had a hitch concerning tables without primary keys. We needed to get a list quick for our reporting team to review and after a quick Google we found and used your code…thanks for posting it…saved us some time.


  4. I am trying to run a web application on my local computer. The application is in ASP.Net. The manual of the application says, only thing I need to modify in the application is the following line in the web.config file:

    It says the above line will depend on what Server is installed on my computer.
    I have SQL Server 2005 installed. Can you tell me what should be the correct syntax of the above line. Also where should I save the database ‘acdb’ on the computer and how do I link or view this table in the SQL server.


  5. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  6. For sql server 2000:

    use databasename;

    select table_catalog,table_schema,table_name from information_schema.tables where


  7. sir… iam not getting…
    my requirement is getting tables list which were created by me in oracle DB
    i posted this query


    is this right or any other query i have to type..plz ..send me ur reply..


  8. I like our site it help a lot.
    My datbase give me error on ensert of data it say violetion of premary kay
    can not duplicate value on database.


  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

  10. Hi Pinal,

    select * from sys.tables where object_id not in (select object_id from sys.indexes where is_primary_key = 1)

    This above query also works fine and it returns the expected result.

    Please confirm the same.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s