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

« SQL SERVER – Fix : Error 2596 The repair statement was not processed. The database cannot be in read-only mode

SQL SERVER – 2005 – SSMS – Enable Autogrowth Database Property »

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

August 7, 2007 by pinaldave

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

Posted in Database, Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology | 19 Comments

19 Responses

on August 14, 2007 at 6:00 am | Reply Plamen RB

Just a thought: if you use INFORMATION_SCHEMA.TABLES instead of sys.tables your script will be working in sql server 2000 as well.
on August 14, 2007 at 6:19 am | Reply pinaldave

Hi Plamen,

That is correct. However, sys.tables is standard SQL Server 2005 syntax.

Regards,
Pinal Dave (http://www.SQLAuthority.com)
on August 28, 2007 at 12:00 am | Reply Sunil

Hi Pinal,

What will be the equivalent syntax for SQL Server 2000?
on August 28, 2007 at 12:01 am | Reply pinaldave

You can replace sys.tables with systables.

I have stop using SQL Server 2000 so please verify with your local DBA.

Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on October 29, 2007 at 12:42 am | Reply Alex

very helpful, thanks.
on November 14, 2007 at 2:36 am | Reply Richard Mason

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.
on January 26, 2008 at 9:32 am | Reply MousumiMitra

I want to see the table of Humanresources.Employee.i.e.Select*from humanresources.Employee
on March 26, 2008 at 7:52 am | Reply tracy

this is just what i needed. thanks
on June 4, 2008 at 7:53 pm | Reply Mike Davis

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.
on June 10, 2008 at 11:58 am | Reply Avinash

Hi
Thanks a lot this is what i was searching for 2 days
Regards
Avinash
on June 12, 2008 at 10:36 pm | Reply KM Web Design

Thanks Pinal

Right on! this is the answer to my question, you solved it
on June 18, 2008 at 4:11 pm | Reply shailesh

i want to retrive records of table.
n i want to pass this table name as an argument,
so how to do this….
on July 28, 2008 at 6:23 pm | Reply scs

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.
on October 7, 2008 at 1:57 pm | Reply Manohar

This is just what i needed. thanks..
It helped me a lot….
on February 24, 2009 at 5:40 pm | Reply SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – 2005 – List Tables in Database Without Primary Key [...]
on December 2, 2009 at 5:15 pm | Reply Kedar

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
on July 13, 2010 at 10:28 am | Reply Anoop

Thanks Pinal,

thanks.. But i got some table names with a prefix ‘conflict_’. Any idea on this?
on November 17, 2011 at 11:45 am | Reply Ramanjaneyulu Gangadhari

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
- on November 17, 2011 at 8:27 pm | Reply Brian Tkatch
  
  @raman
  
  The name COLUMN there is the TABLE name, if you are looking for the SCHEMA, try: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ”;