This is very simple and can be achieved using system table sys.tables. One short query is all you need to list all tables.
USE YourDBName GO SELECT * FROM sys.Tables GO
This will return all the tables in the database which user have created.
Why sys.tables and not something else
sys.tables is a catalog view. SQL Server keeps one in every database, and it only ever shows you that database, so you never need to worry about picking up tables from somewhere else. It also skips system tables, which is usually what you want. If you run this in master you will see very little, and that is correct. Run it in your own database.
Show the schema name as well
In real life the table name on its own is rarely enough. Two schemas can hold a table with the same name, and then a plain list just confuses everybody. Join to sys.schemas and you get the full picture.
SELECT s.name AS SchemaName,
t.name AS TableName,
t.create_date,
t.modify_date
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
ORDER BY s.name, t.name;I use this one far more often than the short version. The two dates are handy when somebody swears they did not change anything.
The INFORMATION_SCHEMA version
There is a second way to do this, and you will see it in plenty of scripts online.
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME;
Which one should you use? If your script has to run on other database engines too, INFORMATION_SCHEMA is the standard one and travels better. If you are staying on SQL Server, sys.tables tells you more and is the one Microsoft recommends. Note the WHERE clause: without it you get views in the list as well, which catches people out.
How many rows in each table
The question that follows this one, every single time, is how big each table is. You do not need to count the rows yourself. SQL Server already keeps that number.
SELECT s.name AS SchemaName,
t.name AS TableName,
SUM(p.rows) AS RowCounts
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
INNER JOIN sys.partitions AS p
ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
GROUP BY s.name, t.name
ORDER BY RowCounts DESC;This reads a stored count rather than scanning the tables, so it comes back instantly even on a big database. The index_id filter picks the heap or the clustered index, which is where the real row count lives. Counting with COUNT(*) on every table would give you the same answers and ruin your afternoon.
A couple of things worth knowing
These views only show what your login is allowed to see. If a colleague runs the same query and gets a shorter list, permissions are the first thing to check, not the query.
And if you want this for every database on the server rather than one, do not write a loop. Run it once per database, or use sp_MSforeachdb if you already trust it. Getting a tidy list from one database at a time is nearly always faster than debugging a clever script that walks all of them.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
448 Comments. Leave new
I’m trying to geat a list of tables in all views in a DB including the views that have their source tables in another database…
SELECT * FROM [INFORMATION_SCHEMA].VIEW_TABLE_USAGE only gives me a list of views that have their input tables sourced from the current database
this is too good……………
Anyone find a solution to this one, trying to restrict sys, and information_schema objects from ODBC and ms sql managment console on sql server 2008 r2 dbase but having no luck.
found –
but they never had a full proof answer either from 3 yrs ago. Help please as I am trying to set up the connection to the dbase where the user will only be able to see a select set of tables, views and with either userid pass word via ms sql mang console or ODBC have just select access to tables for users, keeping things locked down from the user. any ideas?
Brett Stutzman
I require all the databases whether it is attached to Server or not. Is it possible ? Help me
I have one great doubt , i need to search data in a multiple table . the table name’s are like TR42012,TR52012,TR62012 ETC… I give an Input “month” only, that input will check all the table like (TR42012,TR52012,TR62012 ETC…) and give the particular “month” value only….
its possible or not… please give me a solution to send my mail Id ::: “vinosh.john@gmail.com”
gr8 blog …. dis discusion helped me alot …. nd cleared many doubts too…
Is it possible to show the child/children of a table and show the links?
1)how to write query for comparing userid & password in sql 2005
I have got solution from this site thank’s
Hello. I am a complete newbie and your articles are some of the best that I have come across.
Hi Pinal,
I want to know one thing. it is possible that we can set the table name at SQL which can be use at front end side. I mean if I assign any name to ‘select query’ and it will return table to front end with a given name.
1) excute tis (below) procedure …, i will get 3 tables ..ok
2) and …i m using ExcuteDataSet() in frontend like DatabaseLayer.cs (.Net)
3) so tat time ….(from the Dataset) …Table1,Table2,Table3,Table4…..etc( table index 0 ,1 ,2,3 …etc)
4) I want to access Dataset.Table[‘tablename which is given in backend’]?
not by index value
*******************************************
in Store procedure (Sample)
************************************************
CREATE PROC spname(@parm1 int,
@param varchar(10)
) AS
BEGIN
SELECT filed1,filed2,filed3 FROM table1
SELECT field1,filed2,field4 FROM another_different_table_1
SELECT field1,filed2,field4 FROM another_different_table_2
RETURN………………..
END
Hi ..
how to get all the table structure with select statement in a database?
ex: Emp table having name, department and salary columns
i want output like
select name, department, salary from emp
How will i run parallel load in SSIS package, like i have 10 text file and my system configuration is 7cpu and 32gb ram, so i want to utilise all the cpu and 32gb ram, so how can i run all the text file in one short. not like dependence . as of now my job will take one by one instead of that i have load parelley load all the data into one table.
Note: I have one table , source 10 text file,destination one table/
Please help me to understand the logic to implement and utilize all the cpu in the server
Hi experts, i need your help. I need to create a store procedure which will receive an four arrays of parameters. Each one has parameters split with a comma. I need to retrieve data very fast and i cant just use the split function for security reasons. Can give some advice. Thanks
Hi, I am working into oracle sql developer.so i need to get into database all tables table names. how can write query?
select * from tab; thats all :-)
How to get list of table names of a database, whose names start with “student”?
For example: I have many tables in database like (student_attendance,student_address, student_exam, employ_salary, employ_attendance).
Here I only need the list table names, whose names start with “student”??
Thank you..
Hello,
I want to compare two tables on diffrent servers which are having same structure.
Please assist.
Thanks in Advance
Hello,
How to compare two tables on diff server which are having same structure.
PLease assist.
Thanks in advance
Thanks you, this works !!