sp_tables is the procedure used for getting the tables and views information in sql server current data base

sp_tables [ [ @table_name = ] ‘name’ ]

[ , [ @table_owner = ] ‘owner’ ]

[ , [ @table_qualifier = ] ‘qualifier’ ]

[ , [ @table_type = ] “type” ]

[ , [@fUsePattern = ] ‘fUsePattern’];

@table_qualifier-database name

@table_owner is the schema name

@table_type- it takes only 3 arguments and will give ouput based on that TABLE, SYSTEMTABLE, and VIEW

[@fUsePattern- for pattern matching

Ex:

exec sp_tables @Table_Type = ”’VIEW”’

exec sp_tables @Table_Type = ”’TABLE”’

normally we have the used below queries for retruing the  tables information and views information from the database

first and best option is information schema

for tables

SELECT [TABLE_NAME]

FROM INFORMATION_SCHEMA.tables

For views

SELECT [TABLE_NAME]

FROM INFORMATION_SCHEMA.views

Alternatively, the INFORMATION_SCHEMA.TABLES system view can also be used to return user views.

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = ‘VIEW’

The INFORMATION_SCHEMA.TABLES system view returns both view and user tables.  To return just user views, the TABLE_TYPE needs to be checked for a value of ‘VIEW’.

The third way of getting a list of user views is by querying the different system views, namely, the sys.views, sys.objectsand dbo.sysobjects.

For views:

Select * from sys.views

SELECT [Name] FROM [sys].[objects] WHERE [type] = ‘U’

SELECT [Name] FROM [dbo].[sysobjects] WHERE [xtype] = ‘U’

For tables:

select * from sys.tables

SELECT [Name] FROM [sys].[objects] WHERE [type] = ‘U’

SELECT [Name] FROM [dbo].[sysobjects] WHERE [xtype] = ‘U’

Please check and let us know if you have any concerns on this

thanks for reading