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)
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.
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,