SQL Server : SELECT from sys.tables and sys.views

Question

The below SQL seems to be working, but I am wondering if there is a better way to write this. I am trying to select all views and tables with a specific name. There should only be one return value if a match is found.

SELECT DISTINCT name 
FROM sys.tables 
WHERE name = 'MyName'  

UNION ALL

SELECT DISTINCT name 
FROM sys.views 
WHERE name = 'MyName'

Answer 1

There's another view which may help you - it is a single view which contains both tables and views. Performance wise, my single-table query performs better, though you'll only notice on a database with a very large number of tables. I modified your query for this purpose.

SELECT 
    DISTINCT NAME 
FROM SYS.OBJECTS
WHERE TYPE IN ('U','V')
AND NAME= 'MYNAME'

U = User Table
V = View

Answer 2

There is INFORMATION_SCHEMA exposed in SQL Server. Kindly note that taking TABLE_NAME here disregards the schema of the table.

More DMVs here

So, do check out these Dynamic Management Views (DMVs) separately first.

SELECT * FROM INFORMATION_SCHEMA.TABLES

SELECT * FROM INFORMATION_SCHEMA.VIEWS

You can use

SELECT 
    distinct TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'MyName'  

UNION ALL

SELECT 
    distinct TABLE_NAME 
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME= 'MyName'