CODEWITHSUNDEEP
sqlsql-server
Chris Lombardi
Chris Lombardi

Reputation: 891

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

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'

Upvotes: 8

Views: 51345

Answers (5)

Liaqat Kundi
Liaqat Kundi

Reputation: 71

Don't worry, No need to use DISTINCT keyword here because it's not allow in SQL to Create Multi objects with same name.

Upvotes: 0

Jason A. Long
Jason A. Long

Reputation: 4442

Rather that doing a DISTINCT, I'd rather see everything that meets the filter criteria. Adding a couple more columns will allow you to actually identify the data that's being returned.

SELECT 
    o.object_id,
    ObjectName = CONCAT(s.name, ',', o.name),
    ObjectType = o.type_desc
FROM
    sys.objects o
    JOIN sys.schemas s
        ON o.schema_id = s.schema_id
WHERE 
    o.type IN ('U', 'V')
    AND o.name = 'MyName';

Upvotes: 0

Amit Kumar Singh
Amit Kumar Singh

Reputation: 4475

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'

Upvotes: 3

Ryan B.
Ryan B.

Reputation: 3665

If you had a table and a view with the same name but different schemas, you would get back a result set with two rows -- this does not meet your requirements. Instead, use just 'Union' and remove distinct from the individual queries.

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

UNION 

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

Upvotes: 0

Eli
Eli

Reputation: 2608

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

Upvotes: 4

Related Questions