System Stored Procedure sys.sp_tables

I have seen people running the following script quite often, to know the list of the tables from the database:

SELECT *
FROM sys.tables
GO

The script above provides various information from create date to file stream, and many other important information. If you need all those information, that script is the one for you. However, if you do not need all those information, I suggest that you run the following script:

EXEC sys.sp_tables
GO

The script above will give all the tables in the table with schema name and qualifiers. Additionally, this will return all the system catalog views together with other views. This Stored Procedure returns all the tables first in the result set, followed by views.

Even though Stored Procedure returns more numbers of rows, it still performs better than the sys.table query.

Let us verify it with two different methods for database AdventureWorks:

1) SET STATISTICS IO ON

USE AdventureWorks
GO
SET STATISTICS IO ON
SELECT *
FROM sys.tables
GO
EXEC sys.sp_tables
GO

(81 row(s) affected) (This is for sys.tables)
Table ‘syspalvalues’. Scan count 0, logical reads 162, physical reads 0, read

Read more: Journey to SQL Authority with Pinal Dave

Posted via email from .NET Info