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:
|
|
|
|
|
|