INFORMATION_SCHEMA.TABLES vs SYS.TABLES in SQL Server

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.TABLESSYS.TABLES
1It 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.
2INFORMATION_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.
3As 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.
4The 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
5Along 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.
6It 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.
7As 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.
8Example:
/* 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
GO

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
INFORMATION_SCHEMA.TABLES Vs SYS.TABLES

Using SYS.TABLES

SELECT * FROM SYS.TABLES
GO
Using sys.tables

Reference


Related Articles & Tools

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.