Jan 2, 2014

Sql query to list all the user defined tables of a database in sql server


Sql query to list or gets all the user defined tables of a database in sql server

SELECT name FROM sys.tables
SELECT name FROM sys.objects WHERE [Type] = 'U'

Here sys.tables and sys.objects are system view. View sys.tables keeps only user defined tables while sys.objects keeps all the objects like table, view, synonyms, stored procedures functions etc.

Sql query to count the total numbers of user defined table:

SELECT COUNT(OBJECT_ID) AS TotalTables FROM sys.tables

In view sys.Objects meaning of Type:

Type
Meaning
U
User defined table
V
View
P
Stored procedure
FN
Scalar function
TF
Table valued function
SN
Synonym
D
Default constraint
PK
Primary key constraint
F
Foreign key constraint
C
Check constraint
UQ
Unique constraint
TR
Trigger
X
Extended stored procedure
RF
Replication filter procedure
TT
Table type
S
System table
IT
Internal table
IF
Inline table valued function
PG
Plan guid
R
Rule
SQ
Service queue
PC
CLR stored procedure
FS
CLR scalar function
FT
CLR table valued function
AF
CLR Aggregate function
TA
CLR DML trigger

Sql query to list all the stored procedures in database.
SELECT name FROM sys.objects WHERE [Type] = 'P'

Sql query to list all the scalar functions in database.
SELECT name FROM sys.objects WHERE [Type] = 'FN'

Sql query to list all the views in database.
SELECT name FROM sys.objects WHERE [Type] = 'V'

Sql query to list all the triggers in database.
SELECT name FROM sys.objects WHERE [Type] = 'TR'

3 comments:

Alfred Avina said...

The main motive of the Big data app development is to spread the knowledge so that they can give more big data engineers to the world.

Prakash said...

You have written an excellent blog. I learned something new from your Content. Keep sharing this kind of informative blog.
Oracle DBA Training in Chennai
Oracle DBA Online Training
Best Oracle DBA Training in Chennai

mtom said...

This article is dedicated to all those who know what is billiard table; to all those who do not know what is pool table; to all those who want to know what is billiards; best free microservice courses