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'
1 comment:
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.
Post a Comment