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;
GO
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;
GO

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

About these ads

26 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
    objectproperty(object_id(table_name),’TableHasPrimaryKey’)=0

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

    SELECT * FROM SYS.TABLES WHERE NAME=’USERNAME';

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

  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

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