Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL statement to grab table names, views, and stored procs, order by schema

Is there a SQL statement that will list the names of all the tables, views, and stored procs from MS SQL Server database, ordered by schema name?

I would like to generate an Excel spreadsheet from this list with the columns: schema, type (table, view, stored proc), and name.

like image 982
spong Avatar asked Mar 25 '10 14:03

spong


2 Answers

Here's what you asked for:

select 
    s.name as [Schema], 
    o.type_desc as [Type],
    o.name as [Name] 
from
    sys.all_objects o
    inner join sys.schemas s on s.schema_id = o.schema_id 
where
    o.type in ('U', 'V', 'P') -- tables, views, and stored procedures
order by
    s.name
like image 196
Jeffrey L Whitledge Avatar answered Nov 09 '22 21:11

Jeffrey L Whitledge


You can create a query using the system views INFORMATION_SCHEMA.TABLES, INFORMATION_SCHEMA.VIEWS and INFORMATION_SCHEMA.COLUMNS

Edit: Oh and INFORMATION_SCHEMA.ROUTINES for stored procs

like image 26
codingbadger Avatar answered Nov 09 '22 20:11

codingbadger