Get All Tables in a Database
rgreh
Get List of All Tables in a Database
There are many ways to show all tables of a database. Now one may ask why the developers of SQL server came up with many ways to arrive at the same solution. The answer is that although there are many ways to retrieve all tables of the database, each one of them differ from one another way in the way they derive the tables, or in the number of extra information they show along with the table name etc.
Let us some of the ways of getting all tables form the database:
1) The oldest of all ways is
SELECT * FROM sysobjects WHERE xtype='U'
One should note that it is the slowest of all ways to get all tables from the database. This is because sysobjects consist of all database objects like SP, constraints, trigger etc. So, the above query filters among all such objects to get the list of all tables which increases the retrieval time.
2) Faster way of retrieving all tables would be
select * from sys.tables;
This would list out all the tables but again it will list out many extra columns one might not be interested in:
I don’t think anyone would be interested in object id, principle id etc.
3) So, the much faster and better way would be
SELECT * FROM information_schema.tables
This query will list out all tables along with schema, database name and table type. Note that qbove query would list out other types of tables (Virtual tables i.e Views) as well. To list out only base tables, one should add a where condition as
SELECT * FROM information_schema.tables where Table_Type = 'BASE TABLE'
Last way to list out all the tables is by calling the stored procedure sp_tables and passing table as paramater. Exact code is
4) Using Stored procedure
EXEC sp_tables @table_type = "'TABLE'"
It might be that you have two or more tables with the same name and the only differentiation between them is the database schema. For example, you might have the table sarin as dbo.sarin or dbs.sarin where dbo and dbs is different schema respectively.
In these cases, retrieving only the table name is not enough. One needs to get the name of the table prepended by schema name. Only can implement this in many ways. I would list out two ways which I have used in my professional life:
5) Table name prepended by schema name
SELECT FullName = s.name + '.' + t.name
,SchemaName = s.name
,TableName = t.name
FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id
ORDER BY s.name, t.name
We have appended schema to table name to get the full name of the table. However we have make use of join operation which would increase the data retrieval time. A much faster alternative would be
6) Faster way to retrieve table name prepended by schema name
SELECT TABLE_SCHEMA + '.' + TABLE_NAME, *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME
As shown above, from the single table without using join, we retrieve the table name prepended by schema name
Note: Images used on this website
are either a production of Bhaktivedanta Book Trust(https://www.krishna.com), Iskcon
Foundation or were found in google search under "Free to use and share". If any
of the images presented here violates copyright issues or infringes anyone copyright
or are not under "Fair use", then please bring it to our notice. Read
Disclaimer for more.
Share this to your friends. One of your friend is
waiting for your share.
Related Articles
Calling web service asynchronously using jquery ajax
Get All Tables in a Database
Get comma separated result of a database query
Testing performance issues with reflection
Get list of all database objects with table name or column name
When to use optional parameters of creating database statement
Different types of alter table command to change database schema
Delete duplicate records from table in SQL
Post Comment