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 (https://blog.sqlauthority.com)
8 Comments. Leave new
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…
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.
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.
Thanks for the tip, never used this stored procedure.
Thanks
For the name Only : select name from sys.tables=37%
ANd
EXEC sys.sp_tables=63%
Select Name from sys.table is faster but.
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,
Hi Pinal,
I’m experiencing an issue with the sys.sp_tables prodcure and wonder if you can shed some light on this; We have an application that calls sys.sp_tables on start up – the application also has a database role defined for permissions for users. When there is a certain heavy overnight process running, normal users belonging to the database role can’t start the app, their connections are blocked by the big process. But sysadmins can run the app!
So I elevated the application role to db_owner and viola, the end users can initiate the app even when the heavy processing is occurring.
MS Docs says that the only permission required is select permissions on the schema which i tried (prior to making db_owner) but that didn’t help.
Why would that database role normally work but not when this certain process is running. And what allows sysadmin or db_owner to execute even when that process is running.
Once the process stops the regular database role works fine (demoted from db_owner – a matrix of select and select,insert,update,delete on tables).
Thanks in advance!