For years you might have used both INFORMATION_SCHEMA.TABLES and SYS.TABLES to fetch table details in SQL Server. However, you might not have thought much about the differences and the similarities between them. Here, let us see the comparison of INFORMATION_SCHEMA.TABLES Vs SYS.TABLES.
INFORMATION_SCHEMA.TABLES Vs SYS.TABLES
| # | INFORMATION_SCHEMA.TABLES | SYS.TABLES |
|---|---|---|
| 1 | It is an ANSI standard view to display details of the tables in a database. | It is Microsoft’s proprietary system catalog view in SQL Server. |
| 2 | INFORMATION_SCHEMA.TABLES returns read-only information about all the tables and views in the current database. | SYS.TABLES returns a read only information about user tables in the current database. |
| 3 | As this is ANSI standard, this is implemented in several databases other than MS SQL Server. e.g. MySQL, PostgreSQL, MariaDB,… etc. | As sys.tables is Microsoft’s proprietary view, it is available in MS SQL Server alone. |
| 4 | The INFORMATION_SCHEMA views provide an internal, system view of the SQL Server metadata. | The SYS.TABLES catalog view is inherited from the sys.objects catalog view |
| 5 | Along with user tables INFORMATION_SCHEMA.TABLES will also list the views. So, to get the user tables alone, you have to add a TABLE_TYPE filter condition. | SYS.TABLES lists only the tables in the database. |
| 6 | It returns just 4 pieces of information about a table (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE) | Returns more than 40 columns with information about the tables. |
| 7 | As this gives the basic details about the tables, you can use it to get a quick list or anything to do with the minimal details of the table. | As this gives in depth details of the table, it is useful for DBA. |
| 8 | Example: /* If you want to get both tables and views */ SELECT * FROM INFORMATION_SCHEMA.TABLES GO /* If you want to get only tables */ SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ GO | Example:SELECT * FROM SYS.TABLES |
Using INFORMATION_SCHEMA.TABLES
/* If you want to get both tables and views */
SELECT * FROM INFORMATION_SCHEMA.TABLES
GO
/* If you want to get only tables */
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO
Using SYS.TABLES
SELECT * FROM SYS.TABLES
GO
Reference
- Read more about sys.tables at Microsoft Docs.
- Read more about INFORMATION_SCHEMA.TABLES at Microsoft Docs.