query

List all tables in a sql server database using a query Part 65



Sharing buttons:

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