T-SQL to find all tables without primary key in SQL Server
As per the best practice, it is always advisable to have a Primary key on all tables in SQL Server Database. Now many might not know this and have created tables without any primary key. As a Good DBA your job is to find out all the table which are not having the primary key and then based on recommendation you can create a Primary key. Below piece of code will assist you in getting all the tables which are not having any primary key defined.
select [name] as [Table_Name] from sys.objects where type = 'U' and [name] not in (SELECT A.TABLE_NAME as [name] FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)