SQL语句来抓取表名，视图和存储过程，按模式排序 [英] SQL statement to grab table names, views, and stored procs, order by schema

问题描述

我有一个SQL语句，它会列出MS SQL Server数据库中所有表，视图和存储过程的名称？想要从列表中生成一个Excel电子表格，其中列有：schema，type（table，view，stored proc）和name。

解决方案

这是您要求的：

 选择
 s.name为[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 
其中
 o.type（'U'，'V'，'P'） - 表，视图和存储过程
 order by 
 s.name 
  

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.

解决方案

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

这篇关于SQL语句来抓取表名，视图和存储过程，按模式排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！