sqlsql-server

Get the names of all the tables and the views in a single SQL query for SQL Server?


I know that INFORMATION_SCHEMA.TABLES gives the table names and sys.views gives the view names. But I'm not able to get both in a single query result. Any help is appreciated.


Solution

  • sys.views(sys.views (Transact-SQL)) doesn't give you the list of column names, it (unsurprisingly) gives you the list VIEW objects in the database:

    Contains a row for each view object, with sys.objects.type = V.

    You either want the (appropriately named) INFORMATION_SCHEMA.COLUMNS or perform a JOIN from sys.tables to sys.columns on object_id.


    That one word you changed completely changes the question. Instead you now want sys.objects:

    SELECT [name]
    FROM sys.objects o
    WHERE [type] IN ('U','V');
    

    'U' means User Table, and 'V' means View.