CODEWITHSUNDEEP
sqlsql-serverexcel
spong
spong

Reputation: 1222

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.

Upvotes: 7

Views: 15326

Answers (7)

spong
spong

Reputation: 1222

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

Upvotes: 1

Kashif Awan
Kashif Awan

Reputation: 438

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

Upvotes: 0

Jeffrey L Whitledge
Jeffrey L Whitledge

Reputation: 59523

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

Upvotes: 9

KM.
KM.

Reputation: 103677

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

Upvotes: 0

Anders Abel
Anders Abel

Reputation: 69280

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.

Upvotes: 0

codingbadger
codingbadger

Reputation: 44032

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

Upvotes: 1

Arthur
Arthur

Reputation: 8129

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

Upvotes: 0

Related Questions