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 (https://blog.sqlauthority.com), BOL
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
28 Comments. Leave new
Just a thought: if you use INFORMATION_SCHEMA.TABLES instead of sys.tables your script will be working in sql server 2000 as well.
Hi Pinal,
What will be the equivalent syntax for SQL Server 2000?
very helpful, thanks.
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.
I want to see the table of Humanresources.Employee.i.e.Select*from humanresources.Employee
this is just what i needed. thanks
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.
Hi
Thanks a lot this is what i was searching for 2 days
Regards
Avinash
Thanks Pinal
Right on! this is the answer to my question, you solved it
i want to retrive records of table.
n i want to pass this table name as an argument,
so how to do this….
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.
This is just what i needed. thanks..
It helped me a lot….
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
Thanks Pinal,
thanks.. But i got some table names with a prefix ‘conflict_’. Any idea on this?
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
@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 = ”;
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.
Thanks Pinal Sir…
how can i find the primary and foreign keys in a table in ms access?
when developing a database you can have more then one “Primary Key” in a table
TRUE OR FALSE
im new to sql…….just studying …..can u tell the basic things to be known first