SQL SERVER – 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-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘syssingleobjrefs’. Scan count 0, logical reads 324, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘sysidxstats’. Scan count 81, logical reads 168, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘syspalnames’. Scan count 0, logical reads 162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘sysschobjs’. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(456 row(s) affected) (This is for sys.sp_tables)
Table ‘sysobjrdb’. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘sysschobjs’. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

2) Execution Plan

You can see that the execution plan for sys.table has much higher cost of query batch.

Well, if you only need to know the name of the tables, I suggest that you start using SP_TABLES; at least it takes less typing to do.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

7 thoughts on “SQL SERVER – System Stored Procedure sys.sp_tables

  1. Dear sir,
    I executed all the sql statements. But,i have one doubt…How did u compare these 2 queries.(See the queries in the Execution plan window) Both are not same…

    Like

  2. Hello Sir,

    I am a Beginner. I have One problem. I want to insert and update records using single stored procedure.

    I have One table called Tbl1. It contains three columns such as ExamID, SetUpDate, ExamNumber.

    ExamID is an Identity Column.
    When I insert the SetUpDate, the ExamNumber will be automatically updated like Oct 2010 01(based on SetUpDate). In that Oct 2010 is the Month and Year of the SetUpDate and 01 is the automatically generated number.

    Likewise when i insert another record, the examnumber will be generated as Oct 2010 02 and so on..

    Suppose if we select setupdate as Nov 2010. then the exam number will be updated as Nov 2010 01, 02 and so on…

    I have write one procedure but it is not working.

    Create procedure [dbo].[sp_InsertUpdate]
    (
    @SetupDate datetime,
    @ExamNumber varchar(20)
    )

    as
    begin
    insert into OnlineExamHdr
    (
    SetupDate,
    ExamNumber
    )
    values
    (
    @SetupDate,
    @ExamNumber
    )

    declare @ExamID int
    declare @datept varchar(20)
    declare @date varchar(20)
    declare @num int

    set @date = (select ExamSetupdate from Tbl1 where ExamID = @ExamID)
    set @datept = convert(varchar(4), @date,100) + convert(varchar(4),year(@date))
    set @num = (select substring(Examnumber, 10, len(Examnumber)) as num from Tbl1 where Examnumber = (select max(Examnumber) from Tbl1 where Examnumber like @datept+’%’))

    update Tbl1
    set ExamNumber = (select(@datept + space(2) + convert(nvarchar(50), (right(100 + (@num + 1), 2)))))
    where ExamID = @ExamID

    end

    Please anyone give the solution.

    Like

    • What you want is a MERGE statement. However, if you are truly a beginner I suggest you understand INSERT statements with exclusionary clauses very well first.

      There are EXCEPT clauses which work like Anti-UNION statement and are for specific situations, and JOIN controlled relational statements. Practice these first until you really master the relational logic; otherwise you will get lost in trying to figure out why your MERGE logic is broken despite the fact that it produces no error.

      Like

  3. Hi Mr Pinal

    I’m trying to learn more about system tables of SQL Server. And I get a problem with sysdiagrams. I can’t find which table exists in a particular diagram? its content (diagram’s image) is stored as binary format (column [Definition])

    Please give me your opinions
    Thanks so much,

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s