1

I am using SQL Server 2014 and want to get all the tables name in a particular database STUDENT through a SQL query.

How is it possible?

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
M.K.S
  • 51
  • 1
  • 3
  • 10

1 Answers1

5

You want to query sys.objects and look for everything with the type description 'USER_TABLE'. You could use a query like this;

SELECT 
*
FROM STUDENT.sys.objects
WHERE type_desc = 'USER_TABLE'

The FROM clause has the usual format: DatabaseName.SchemaName.TableName.

Or as marc_s mentions, you can use sys.tables instead;

SELECT 
*
FROM STUDENT.sys.tables
Rich Benner
  • 7,873
  • 9
  • 33
  • 39
  • Or you could just use the **more focused** `sys.tables` catalog view and not have to deal with the `type_desc` anymore.... – marc_s Oct 27 '16 at 08:25
  • @Rich benner my database name is STUDENT and i want to get the names of all tables only exist into the STUDENT database, so how can i use above query according to my database – M.K.S Oct 27 '16 at 08:25
  • That doesn't make sense. You want the names of all of the tables within another table? – Rich Benner Oct 27 '16 at 08:27
  • @Rich Benner Sorry my mistake actually STUDENT is my database name – M.K.S Oct 27 '16 at 08:28
  • Yes, so your database 'STUDENT' contains a number of tables. The query above will list all tables that exist within your database. I don't understand the complication here. – Rich Benner Oct 27 '16 at 08:29
  • yes its working fine thank@Rich Benner for your suggestions – M.K.S Oct 27 '16 at 08:32
  • Feel free to mark the answer as accepted if it resolves your problem. – Rich Benner Oct 27 '16 at 09:05
  • SO Sorry @Rich Benner I am new member and reputation less than 15 that's why I could not vote your answer. vote my question so that I would eligible for voting – M.K.S Oct 27 '16 at 09:13
  • @RichBenner Thanks a lot, now I am eligibled to accept any question – M.K.S Oct 27 '16 at 10:02
  • No worries :) there's a tick under the voting arrows on the answer. If you tick this then it shows others that the question has been answered (and gives reputation to the answerer) – Rich Benner Oct 27 '16 at 10:04