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'

No comments:

Post a Comment