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

Question

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.

Answer 1

start with

select * from sys.sysobjects

EDIT: Now with schema

select * from sys.sysobjects
inner join sys.schemas on sys.sysobjects.uid = sys.schemas.schema_id

Answer 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

Answer 3

There are some built in system views that you can use:

• sys.views
• sys.tables
• sys.procedures
• sys.schema

More information on MSDN about Catalog Views.

Answer 4

TRY:

SELECT
    ROUTINE_SCHEMA,ROUTINE_TYPE ,ROUTINE_NAME
    FROM INFORMATION_SCHEMA.ROUTINES
UNION 
SELECT 
    TABLE_SCHEMA,TABLE_TYPE,TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES
UNION 
SELECT 
    TABLE_SCHEMA,'VIEW' ,TABLE_NAME
    FROM INFORMATION_SCHEMA.VIEWS
    ORDER BY ROUTINE_SCHEMA,ROUTINE_TYPE ,ROUTINE_NAME

Answer 5

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

Answer 6

Try this SQL if you need to dig into columns and their data type as well. You can use these options for sysobjects.xtype (U = User Table, P = Stored Proc, V = View)

SELECT   object_type = sysobjects.xtype,
     table_name = sysobjects.name,
     column_name = syscolumns.name,
     datatype = systypes.name,
     length = syscolumns.length
FROM sysobjects 
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U' AND 
syscolumns.name LIKE '%[column_name_here]%' 
AND sysobjects.name LIKE '%[table or Stored Proc Name]%' 
ORDER BY sysobjects.name,syscolumns.colid

Answer 7

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