Get Table Names

Transact-SQL
1

I have a feeling this maybe one of those questions that may have been asked a bazillion of times. I do apologize if my question falls into that category but I have googled and information is all over the place.

First, which one should I use when I am trying to get table names?

Second, and most important of all, why did MS SQL Server created so many different ways to get table information? More particular on SYSObjects vs SYS.Objects

Third, are there any other methods to get table names in MS SQL Server?

SELECT * FROM SYSObjects

SELECT * FROM SYS.Objects

SELECT * FROM SYS.SysObjects

SELECT * FROM Information_Schema.TABLES
2

select * FROM sys.tables is another way

1 Like
3

This is easy to answer: always use sys.objects.
Do not use sysobjects, or other sysxxxx, such as syscolumns.
Never use information_schema.tables, or other i_s views.

sys.tables is a subset of sys.objects so it's ok.

1 Like
4

@ScottPletcher Can I ask Why should you not use Information_schema.tables ????

5
  1. Very slow and can cause locking
  2. Does not have all data available in sys.objects, and are not necessarily accurate. From MS docs / BooksOnline:

** Important ** Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of an object is to query the sys.objects catalog view. INFORMATION_SCHEMA views could be incomplete since they are not updated for all new features.

2 Likes
6

Thank You.

BTW... Are these two the same? SYS.Objects & SYS.SysObjects

7

Oh no, not at all.

sysobjects is obsolete and should never be used in new code.

Always use sys.^sys to get metadata:
sys.objects
sys.databases
etc.

1 Like
8

Thank you