info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



SQL Server List all Tables with no Primary Key
by BF (Principal Consultant; Architecture; Engineering)
2016-09-09








Information_Schema.tables: (DB..Views..System Views)

SELECT [Table_catalog], [Table_schema], [Table_name], [Table_type]
FROM information_schema.tables
WHERE
[Table_type] = 'base table' --filter out [Table_type = View]
AND objectproperty(object_id(Table_name),'IsMsShipped') = 0 --filter out system tables
AND objectproperty(object_id(Table_name),'TableHasPrimaryKey') = 0
ORDER BY [Table_name] ASC


sys.tables: (DB..Views..System Views)

SELECT DB_NAME() as [Table_catalog], SCHEMA_NAME(schema_id) AS [Table_schema], [name] AS [Table_name], [TYPE_DESC] as [Table_type]
FROM sys.tables
WHERE
OBJECTPROPERTY(OBJECT_ID,'IsMsShipped') = 0 --filter out system tables
AND
OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY [Table_schema] ASC, [Table_name] ASC


Notes:

- Sys.tables contains only tables (Views are stored in Sys.views)

- Information_schema.tables contains both tables & views

- Information_Schema is an ANSI standard set of metadata of your database objects and thus compatible between different types of RDBMS

- The sys.* syntax is Microsoft SQL Server's method of storing metadata about database objects. i.e. SQL Server Management
Studio uses sys.* and some objects like Indexes are not stored in Information_Schema. (Indexes are stored in sys.indexes)


SQL Server Management Studio: