List all tables in a sql server database using a query Part 65 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this part sixty five of secret silver
tutorial in this video we discuss
writing a transact sequel query to list
all the tables in a sequel server
database this is a very common sequel
server interview question within sequel
server management studio we can use
object Explorer to list all the tables
that belong to a specific database for
example if we have to find the tables
that belong to the sample database we
can simply expand the database with an
object Explorer and then expand the
tables folder and then you can find the
list of tables that belong to the sample
database on the other hand if I have to
write a query to achieve the same then
we can make use of three system views we
can either use this objects sister or
tables or information underscore schemas
or tables views the sis objects view is
available in sequel server 2000 2005 and
2008 whereas these two views are
available in sequel server 2005 and 2008
only they are not available in sequel
server 2000 now let's see how to use the
cysts or view to retrieve all the tables
that are present in the sample database
let's flip the sequel server management
studio so select star from sis objects
now when I execute this query I get all
the objects that are present NS in the
sequel server sample database you know a
sequel server can have different types
of objects like tables views functions
to procedures so this view contains all
those objects now if I just want the
tables then I filter on this X type
column so where X type is equal to you
and I execute this query notice that I
get the three tables that are present in
the sample database TBL customer
department department and employee now
this X type you know what does these
codes mean you means user table so we do
I have this list actually you can visit
this MSDN link for all possible X type
column value
and what they represent so that this URL
you can see that you know f n stands for
scalar function so if you want all
scalar functions then you simply you
know filter on that so I set X type as
FN when I execute that I get the
functions that are available in the
sample database if I want stood
procedures on the other hand the X type
for that is P so I simply say X type is
P execute that I get all the stored
procedures similarly if I want just
views then I replace that with V and I
get views so notice that this one view
view employees and I can see that all
right
now if I want to know what are the
different types of objects that are
available in the sample database I can
find that out by issuing a distinct
query so select distinct X type from
this is objects table so this is going
to tell me the different types of
objects that are available in the sample
database so f n stands for scalar
function so I have scalar functions in
this database i T internal table so it
has got some system tables as well P
stands for procedures that is stored
procedures PK is a primary key
constraint so these are the different
types of objects that are available in
the sample database all right now let's
see how to use the cyst or tables view
you simply say select star from cysts or
tables that's going to return all the
tables in our sample database so select
star from says dot tables so we get
those three tables similarly if I want
views I can simply say it says dot views
and if I want procedures stored
procedures I simply say cyst or
procedures so I see all the stored
procedures within the sample database
notice that there are on 23 stored
procedures
now let us see how to use this
information underscore schema the tables
feel so select star from information
underscore schema that tables is going
to give us all the tables as well as
views so if you want just the tables
then filter on the stable type column
similarly if you want just the views I
can use information on scores kima dot
the key that I get only that view which
is available in the sample database view
employees now if I want stored
procedures look at that I don't have
procedures instead it's called the
routines so when I execute routines you
know select star from information schema
dot routines notice that I get 24 so
it's not only stored procedures it's
also going to return to get that routine
type functions as well so if you want
just the stored procedures then probably
filter on this column right here alright
on this slide you can find resources for
a speed ordinate c-sharp and sequence of
interview questions that's it for today
thank you for listening
have a great day