SQL SERVER - 2005 - List Tables in Database Without Primary Key « Journey to SQL Authority with Pinal Dave

Feed on: Posts; Comments

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://www.SQLAuthority.com), BOL

Posted in Database, Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 14 Comments

14 Responses to “SQL SERVER - 2005 - List Tables in Database Without Primary Key”

on August 14, 2007 at 6:00 am 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 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 Sunil

Hi Pinal,

What will be the equivalent syntax for SQL Server 2000?
on August 28, 2007 at 12:01 am 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 Alex

very helpful, thanks.
on November 14, 2007 at 2:36 am 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 MousumiMitra

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

this is just what i needed. thanks
on June 4, 2008 at 7:53 pm 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 Avinash

Hi
Thanks a lot this is what i was searching for 2 days
Regards
Avinash
on June 12, 2008 at 10:36 pm 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 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 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 Manohar

This is just what i needed. thanks..
It helped me a lot….