CODEWITHSUNDEEP
sqlsql-servert-sql
Clinton Prakash
Clinton Prakash

Reputation: 997

Retrieve all tables and views name with schema name from SQL Server

What is the sql query to retrieve all tables and views name with its schema name from SQL server.

Upvotes: 0

Views: 4179

Answers (3)

Ilyes
Ilyes

Reputation: 14928

You can use Sys.Objects and Sys.Schemas as

SELECT O.name ObjectName,
       S.name SchemaName,
       CASE O.type WHEN 'U' THEN 'TABLE'
                   WHEN 'V' THEN 'VIEW'
       END ObjectType

FROM Sys.Objects O INNER JOIN Sys.Schemas S
     ON O.schema_id = S.schema_id
WHERE O.type = 'U'
      OR
      O.type = 'V'
--    AND 
--    S.name = 'dbo' if you want a specific schema

Here is an example

Upvotes: 0

Onkel Toob
Onkel Toob

Reputation: 2212

This should work, though it would have been rather easy to find out:

SELECT 
    *
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE [TABLE_TYPE] IN('BASE TABLE', 'VIEW');

Upvotes: 3

xCloudx8
xCloudx8

Reputation: 721

Should be ok with this:

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables

Upvotes: 0

Related Questions