SQL statement to grab table names, views, and stored procs, order by schema
14,458
Solution 1
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
Solution 2
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
Solution 3
This is the SQL statement I ended up using:
SELECT
CASE so.type
WHEN 'U' THEN 'table'
WHEN 'P' THEN 'stored proc'
WHEN 'V' THEN 'view'
END AS [type],
s.name AS [schema],
so.[name] AS [name]
FROM sys.sysobjects so
JOIN sys.schemas s
ON so.uid = s.schema_id
WHERE so.type IN ('U', 'P', 'V')
ORDER BY [type], [schema], [name] asc
Author by
spong
Enjoys solving problems, technology, software development, food, reading, photography, blogging, and traveling. Social me: @sunpech sunpech.com foodishappiness.com SOreadytohelp
Updated on June 08, 2022Comments
-
spong almost 2 years
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.